Oledb ExecuteNonQuery Syntax Error in INSERT INTO

Question

I'm trying to insert values into an Access database with C#, however I am getting a Syntax error All of the data types in the database and the C# code are correct.

Here is the error message: error message

here is the code:

 string insert =
                "INSERT INTO Order (OrderTime, TableNumber, Cost) " +
               "VALUES (@OrderTime, @TableNumber, @Cost)";

            OleDbConnection connection = new OleDbConnection(GlobalData.ConnectionString);
            OleDbCommand cmnd = new OleDbCommand(insert, connection);

            cmnd.Parameters.AddWithValue("@OrderTime", currentOrder.OrderDateTime.ToString());
            cmnd.Parameters.AddWithValue("@TableNumber", currentOrder.TableNumber.ToString());
            cmnd.Parameters.AddWithValue("@Cost", currentOrder.Cost);


            connection.Open();

            cmnd.ExecuteNonQuery();
            
            connection.Close();

Although OrderTime and TableNumber should be numbers, I made them strings in the database. Cost is a double. I have tried using brackets on the values just in case they were reserved words, but that didn't work either.

Answer 1

Based on the error message, it seems that the syntax error is caused by the fact that "Order" is a reserved keyword in SQL and should not be used as a table name without being properly escaped.

To fix the issue, you can try enclosing the table name "Order" in square brackets [] like this:

 string insert =
"INSERT INTO [Order] (OrderTime, TableNumber, Cost) " +
"VALUES (@OrderTime, @TableNumber, @Cost)";

Alternatively, you can also change the table name to something else that is not a reserved keyword in SQL.

Note that if you have other SQL statements in your code, you should also check if they use any reserved keywords as table or column names and make the necessary changes to avoid syntax errors.