none
Syntax error in UPDATE statement. (MS Access) RRS feed

  • Question

  • In this code I use an Access database...
    OleDbDataReader rdr = acmd.ExecuteReader();
    ...
    rdr.Close();
    The code works fine up to this point. I successfully read from the Clients and WPClients tables in the database.
    Right after that I set a different command...
    q = "Update Clients Set Found = '200550' Where ID = '100020'";
    acmd.CommandText = q;
    acmd.ExecuteNonQuery();
    This throws "Syntax error in UPDATE statement."
    The connection to the Access database is still open.

    However, when I open the .mdb file in Access and paste the exact same command into the SQL view of a query, it runs fine.

    Why can't I run the update statement from C#?

    Monday, November 2, 2009 7:35 PM

Answers

  • Have you tried to 'bracket' the fields?

    "Update [Clients] Set [Found]='200550' where [ID]='100020';"

    If that doesn't work, you can also parameterize your sql:

    q = "Update [Clients] set [Found]=? where [ID]=?);

    OleDbParameters[] parameters =
    {
      new OleDbParameter("?", "2005500"),
      new OleDbParameter("?", "100020")
    };

    acmd.CommandText = q;
    acmd.Parameters.AddRange(parameters);

    I like to parameterize any strings that I use in my queries.  This ensures that they are handled correctly without having to be parsed.
    public enum Answers { Everything = 0x2A }
    • Marked as answer by Jon Q Jacobs Tuesday, November 3, 2009 6:07 PM
    Tuesday, November 3, 2009 2:29 AM

All replies

  • q = "Update Clients Set Found = '200550' Where ID = '100020'";
    replace the above line with this one:
    q = "UPDATE Clients SET Found = 200550 WHERE ID = 100020";
    let us know if this works!

    Balaji Baskar
    Monday, November 2, 2009 7:54 PM
  • Thank you.

    No, ID is strictly a Text type in Access, string in C#.

    I found out what the problem was. Even though Access accepted it from the inside, if called from my program, Access dislikes Found as a column name. I change the name of the column and referenced it accordingly, and now everything works.
    Monday, November 2, 2009 11:33 PM
  • Have you tried to 'bracket' the fields?

    "Update [Clients] Set [Found]='200550' where [ID]='100020';"

    If that doesn't work, you can also parameterize your sql:

    q = "Update [Clients] set [Found]=? where [ID]=?);

    OleDbParameters[] parameters =
    {
      new OleDbParameter("?", "2005500"),
      new OleDbParameter("?", "100020")
    };

    acmd.CommandText = q;
    acmd.Parameters.AddRange(parameters);

    I like to parameterize any strings that I use in my queries.  This ensures that they are handled correctly without having to be parsed.
    public enum Answers { Everything = 0x2A }
    • Marked as answer by Jon Q Jacobs Tuesday, November 3, 2009 6:07 PM
    Tuesday, November 3, 2009 2:29 AM
  • Bracketing the field names is a good idea. Better than just changing the column names. Thank you.

    When I programmed in Delphi, I generally used parameterized queries.

    Now that I program with .NET, the first database I used where parameterized queries were appropriate, I discovered, after much painful trying, that .NET did not support parameterized queries for the particular database type. Though other database type could use parameterized queries, I tend to write code that will allow me to plug in a different database type as needed, so I have to abandon parameterized queries. Very dissappointing.

    Jon

    Tuesday, November 3, 2009 6:06 PM