views:

124

answers:

1

I am getting a syntax error when I send a parameterized query to Access from my C# program via ADO.NET.

I, of course, know what SQL string I have included in my code, with the parameter names embedded inside.

Does anyone know how I can look at the SQL string that is finally sent to the DBMS during after I call cmd.ExecuteNonQuery?

Thanks.

EDIT:

There is no way to see that string in the interactive debugger or in an Access log or something? In order for anyone to reproduce my precise problem, they would have to have my database, which isn't going to happen. However, since interest has been expressed in the details of what I'm trying to do I'm publishing the following code fragment:

  OdbcCommand cmd = new OdbcCommand();
  cmd.CommandText = 
     @"insert into Posts (Page, Line, TimeStamp, Status) values
           (@pagename, @lineno, @now, 'SAVED')";
  cmd.Connection = _cn;
  cmd.Transaction = transaction;
  cmd.Parameters.Add(new OdbcParameter("@pagename",OdbcType.VarChar));
  cmd.Parameters.Add(new OdbcParameter("@lineno",OdbcType.VarChar));
  cmd.Parameters.Add(new OdbcParameter("@now",OdbcType.DateTime));
  cmd.Parameters["@pagename"].Value = pageId;
  cmd.Parameters["@lineno"].Value = lineId;
  cmd.Parameters["@now"].Value = now;
  cmd.ExecuteNonQuery();

I hope it helps.

Thanks again.

EDIT:

It occurs to me that "TimeStamp" may be a reserved word in AccessSQL and this is likely the cause of the syntax error. However, even assuming this is the cause, the general question of how to see the SQL query in its final form remains open.

+1  A: 

Access only uses positional parameters.

All you need to do is change your SQL to this:

insert into Posts (Page, Line, [TimeStamp], Status) values (?, ?, ?, 'SAVED')

making sure to put any additional parameters into the Parameters collection in the proper order.

Edit: Updated to solve the reserved-word issue.

Edit: It is not possible to trace the SQL that gets run on an OLE DB connection, at least not currently. See this Microsoft KB article.

Jon Seigel
This works nicely, but is not a solution to the main question about seeing the SQL that arrive at Access.
mcoolbeth
@mcoolbeth: I updated my answer.
Jon Seigel