none
Following error when trying to update access database :"System.Data.OleDb.OleDbException: 'Syntax error (missing operator) in query expression ''Computer, Versie='One' WHERE ProductId = 3'.'" RRS feed

  • Question

  • Hi

    For a school project I'm working on a simple program that can insert, delete and update records into an access database.

    Insert and delete works just fine but when trying to update I get the following error : System.Data.OleDb.OleDbException: 'Syntax error (missing operator) in query expression ''Computer, Versie='One' WHERE ProductId = 3'.

    Screenshot of the code below :

    public static void UpdateProduct (Product product)
            {
                string sql = string.Format("UPDATE tblProducten set Merk ='{0}', " +
                    "Soort= '{1}, Versie='{2}' WHERE ProductId = {3}",
                    product.Merk, product.Soort, product.Versie, product.ProductId);
                OleDbConnection connection = new OleDbConnection(Connectiestring);
                connection.Open();

                OleDbCommand command = new OleDbCommand(sql, connection);
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.UpdateCommand = command;
                adapter.UpdateCommand.ExecuteNonQuery(); //getting the error on this line

                connection.Close();
            }

    Any ideas on how to make the update part work correctly?

    Many thanks in advance

    Jonathan

    Saturday, January 6, 2018 11:09 AM

All replies

  • You can use Quickwatch an go to the object and look inside of it to find the T-SQL that it is going to execute. You can copy the T-SQL out for the object and go to MS SQL Sever Management Studio and past the T-SQL into the New Query and Execute the T-SQL that will tell where the syntax error is at in the T-SQL.

    https://msdn.microsoft.com/en-us/library/0taedcee.aspx

    adapter.UpdateCommand.ExecuteNonQuery();

    Look inside the above object and find the T-SQL it is trying to execute.

    Saturday, January 6, 2018 11:36 AM
  • Or forget what I said, because you are using Access and not SQL Server. So you can't execute the T-SQL using the MSMS tool mentioned. But you can still look at the T-SQL that is going to be executed by using Quickwatch and possibly see the syntax error.
    Saturday, January 6, 2018 11:43 AM
  • Then I get the following error :         ExecuteNonQuery    error CS0103: The name 'ExecuteNonQuery' does not exist in the current context    

    Saturday, January 6, 2018 11:51 AM
  • Hello,

    Never use string concatenation or string.format, always use parameters as shown below along with using statements. Note I used named parameters which MS-Access does not recognize but still works. MS-Access parameters are ordinal positioned while SQL-Server are named based so if you ever go to SQL-Server you are good to go.

    Note how I used a try/catch and check the results of ExcecuteNotQuery which can be used if you want to return a bool indicating success or failure.

    using System;
    using System.Data.OleDb;
    
    namespace WindowsFormsApp1
    {
        class DataOperations
        {
            private static string Connectiestring = "TODO";
            public static void UpdateProduct(Product product)
            {
                string sql = "UPDATE tblProducten set Merk = @Merk, Soort= @Soort, Versie=@Versie " + 
                             "WHERE ProductId = @ProductId";
    
    
                using (OleDbConnection cn = new OleDbConnection() { ConnectionString = Connectiestring })
                {
                    using (OleDbCommand cmd = new OleDbCommand() { Connection = cn, CommandText = sql })
                    {
                        cmd.Parameters.AddWithValue("@Merk", product.Merk);
                        cmd.Parameters.AddWithValue("@Soort", product.Soort);
                        cmd.Parameters.AddWithValue("@Versie", product.Versie);
                        cmd.Parameters.AddWithValue("@ProductId", product.ProductId);
    
                        try
                        {
                            cn.Open();
                            int result = cmd.ExecuteNonQuery();
                            if (result == 1)
                            {
                                // success
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
    
                    }
                }
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Saturday, January 6, 2018 11:55 AM
    Moderator
  • Thanks already for the fix. I tried to adjust my code to your suggestion but now I'm getting the error that I need an open connection and it's closed. I tried to put the "connection.Open();" on several locations but none works... any ideas?

    public static void UpdateProduct(Product product)
            {
                string sql = "UPDATE tblProducten set Merk = @Merk, Soort= @Soort, Versie=@Versie " +
                             "WHERE ProductId = @ProductId";
    
                OleDbConnection connection = new OleDbConnection(Connectiestring);
                connection.Open();
    
                using (OleDbConnection cn = new OleDbConnection() { ConnectionString = Connectiestring })
                {
                    using (OleDbCommand cmd = new OleDbCommand() { Connection = cn, CommandText = sql })
                    {
                        
                        cmd.Parameters.AddWithValue("@Merk", product.Merk);
                        cmd.Parameters.AddWithValue("@Soort", product.Soort);
                        cmd.Parameters.AddWithValue("@Versie", product.Versie);
                        cmd.Parameters.AddWithValue("@ProductId", product.ProductId);
                        cmd.ExecuteNonQuery();
    
                        connection.Close();
                    }
                }
            }

    Saturday, January 6, 2018 12:37 PM
  • You have two connections, the one shown below from your code is not needed.

    Also connection.Close() is not needed.

    It should be


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, January 6, 2018 1:05 PM
    Moderator
  • Hello Jonathanvdp,

    Go to your original code. The main cause for the error is you forgot to add single quote. Try to replace the following update statement.

      string sql = string.Format("UPDATE tblProducten set Merk ='{0}'," +"Soort= '{1}', Versie='{2}' WHERE ProductId = {3}",product.Merk, product.Soort, product.Versie, product.ProductId);

    Best regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 8, 2018 7:02 AM
    Moderator
  • Hello Jonathan,

    Is there any update or any other assistance I could provide? You could mark the helpful reply as answer if the issue has been solved. And if you have any concerns, please do not hesitate to let us know.

    Thank you for your understanding and cooperation.

    Best regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Sunday, January 28, 2018 12:42 PM
    Moderator