Search code examples
sqlms-accessoledboledbcommandreserved-words

OleDbException syntax error in INSERT INTO statement

I'm building an SQL INSERT command using the OleDbCommandBuilder

adapter.InsertCommand = builder.GetInsertCommand();

and I get an exception while trying to add data to database on this line:

adapter.Update(ds, tableName);

and the exception:

System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.'

I've set a breakpoint on this line and found out that the syntax of the insert command is correct:

INSERT INTO order (orderid, employeeid, dateestimated, datearrived) VALUES (?, ?, ?, ?)

And the parameters are correct as well: 0x1, 0x2, 13/10/2017, null

Why am I getting a syntax error exception when the syntax is clearly correct and the parameters are right?

The exception's source says Microsoft Access Database Engine.

Solution

  • You have called your table order. This is the reserved MS-Access/SQL keyword. Try renaming your table to orders or some other name. Or try adding [...] making it to be [order] if you have control over the generated SQL statement.

    You can find the full list of MS-Access reserved keywords here: https://support.office.com/en-us/article/Access-2007-reserved-words-and-symbols-E33EB3A9-8BAA-4335-9F57-DA237C63EABE

    The general rule is to try to avoid naming tables/columns using any of these words. However, if you absolutely need you can use [ and ] symbols around column name to escape it.