OleDbException syntax error in INSERT INTO statement

Question

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.

andrews · Accepted Answer · 2017-10-13 14:11:43Z

1

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.

edited Oct 13, 2017 at 14:11

answered Oct 13, 2017 at 14:05

andrews

2,1832 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NotGI Over a year ago

It seems like the right answer, applying the fix now

andrews Over a year ago

@Keddy1201, glad it helped. Would appreciate answer accept/vote-up, thanks.

NotGI Over a year ago

done, this was the correct answer and was driving me crazy for hours. hopefully this answer will also help others .

Collectives™ on Stack Overflow

OleDbException syntax error in INSERT INTO statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related