-1

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.

los
  • 11
  • 2
  • 7
    You should rename your table from `Order` to something else (perhaps to `Orders`?) - because `ORDER` is a keyword in SQL (in `ORDER BY col ASC/DESC`) y’see. Otherwise you need to escape the table-name with square-brackets: `INSERT INTO [Order] ( etc ) VALUES ( etc )` – Dai Apr 24 '23 at 00:12
  • You might try the answers from here: https://stackoverflow.com/questions/5893837/using-parameters-inserting-data-into-access-database – topsail Apr 24 '23 at 00:14
  • 2
    Suggested reading: **[Reserved Words and Symbols in OleDb](https://support.microsoft.com/en-us/office/learn-about-access-reserved-words-and-symbols-ae9d9ada-3255-4b12-91a9-f855bdd9c5a2)** – Ňɏssa Pøngjǣrdenlarp Apr 24 '23 at 00:39
  • 1
    Using _AddWithValue_ will make it difficult to debug - use [Add](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbparametercollection.add?view=dotnet-plat-ext-7.0) instead. Also see [OleDbCommand.Parameters](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbcommand.parameters?view=dotnet-plat-ext-7.0#remarks). – Tu deschizi eu inchid Apr 24 '23 at 03:05

1 Answers1

0

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.

Murad
  • 52
  • 5