System.Data.OleDb.OleDbException: 'Syntax error in UPDATE statement.'

Question

Hello, I am having an issue saving/updating my database using Access in C#. If anyone is able to help solve my issue it would be greatly appreciated. Thank you!

Here are the details:

System.Data.OleDb.OleDbException
  HResult=0x80040E14
  Message=Syntax error in UPDATE statement.
  Source=System.Data
  StackTrace:
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at PasswordManager.frmPasswords.UpdateDatabase() in C:\Users\AKlasen\source\repos\PasswordManager\PasswordManager\frmPasswords.cs:line 104
   at PasswordManager.frmPasswords.button1_Click(Object sender, EventArgs e) in C:\Users\AKlasen\source\repos\PasswordManager\PasswordManager\frmPasswords.cs:line 80
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.Run(Form mainForm)
   at PasswordManager.Program.Main() in C:\Users\AKlasen\source\repos\PasswordManager\PasswordManager\Program.cs:line 19

So here is my UpdateDatabase method:

        public void UpdateDatabase()
        {
            // Create a connection to the database
            con.Open();

            // Define the SQL query and parameters
            string sqlQuery = "UPDATE tbl_users SET username = @NewUsername, password = @NewPassword WHERE ID = @RowID";
            OleDbCommand cmd = new OleDbCommand(sqlQuery, con);
            cmd.Parameters.AddWithValue("@NewUsername", txtUsername.Text);
            cmd.Parameters.AddWithValue("@NewPassword", txtPassword.Text);
            cmd.Parameters.AddWithValue("@RowID", GlobalVar.rowIndex_);

            // Execute the command and close the connection
            cmd.ExecuteNonQuery();
            con.Close();
        }

Answer 1

The access oledb provider use “?” for parameter tokens. Try

string sqlQuery = "UPDATE tbl_users SET username = ?, password = ? WHERE ID = ?";
  

The parameters are positional, not named.

Answer 2

Here is an example to follow and don't use AddWithValue (Google AddWithValue bad).

Also, its always wise to first write the query in the database which means when it works and fails in code the values being passed are causing an issue.

public bool Update(int primaryKey, string description)
{

    using (var cn = new OleDbConnection { ConnectionString = ConnectionString })
    {
        using (var cmd = new OleDbCommand { Connection = cn })
        {
            cmd.CommandText =
                "UPDATE Pictures SET Description = @Description " +
                "WHERE Identifier = @Identifier";

            var descriptionParameter = new OleDbParameter
            {
                DbType = DbType.String,
                ParameterName = "@Description",
                Value = description
            };

            cmd.Parameters.Add(descriptionParameter);

            var identifierParameter = new OleDbParameter
            {
                DbType = DbType.String,
                ParameterName = "@Identifier",
                Value = primaryKey
            };

            cmd.Parameters.Add(identifierParameter);

            try
            {
                cn.Open();

                int affected = cmd.ExecuteNonQuery();

                if (affected == 1)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                // log error
                return false;
            }

        }
    }

    return false;
}