exception 2147217900 in oledb database

Question

im trying to update a database table with values in textboxes that have been calculated previously. the calculations work fine and the textboxes have been converted to decimals.:

        try
        {
            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\User\Desktop\esoft\gym\gym\bin\Debug\Clients.accdb;";
            con.Open();
            OleDbCommand com = new OleDbCommand();
            com.Connection = con;
            com.CommandText = "INSERT INTO gym ([BMI],[Health],[weight_change_to_healthy_bmi]) VALUES ('" + textBox5.Text + "','" + textBox6.Text + ",'" + textBox4.Text + "') WHERE ID='"+textBox2.Text+"',con";

            com.ExecuteNonQuery();
            MessageBox.Show("Saved");
            con.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error   " + ex);
        }

the exception im getting is :

  System.Data.OleDb.OleDbException was unhandled
  ErrorCode=-
  HResult=-2147217900
  Message=Syntax error (missing operator) in query expression ''Normal,'-3.750000000000000000000000001') WHERE ID='1111',con'.
  Source=Microsoft Access Database Engine
  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 gym.Form6.button3_Click(Object sender, EventArgs e) in C:\Users\User\Desktop\esoft\gym\gym\Form6.cs:line 95
       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 gym.Program.Main() in C:\Users\User\Desktop\esoft\gym\gym\Program.cs:line 18
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

i changed the code to:

 private void button3_Click(object sender, EventArgs e)
    {
     //   try
     //   {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\User\Desktop\esoft\gym\gym\bin\Debug\Clients.accdb;");
           // con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\User\Desktop\esoft\gym\gym\bin\Debug\Clients.accdb;";
            con.Open();
            OleDbCommand com = new OleDbCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = "UPDATE gym ([BMI],[Health],[weight_change_to_healthy_bmi]) VALUES ('" + textBox5.Text + "','" + textBox6.Text + "','" + textBox4.Text + "') WHERE ID='" + textBox2.Text + "' ";

            com.ExecuteNonQuery();
            MessageBox.Show("Saved");
            con.Close();
     //   }
       // catch (Exception ex)
      //  {
       //     MessageBox.Show("Error   " + ex);
       // }

but no there is a syntax error in the line com.ExecuteNonQuery();

Answer 1

If you compare your error

''Normal,'-3.750000000000000000000000001') WHERE ID='1111',con'

with your source code

'" + textBox6.Text + ",'" + textBox4.Text + "') WHERE ID='"+textBox2.Text+"',con";

you can see that your SQL query was ended because of missing single quotes or double quotes

In thise case textBox4.Text seems to be -3.750000000000000000000000001

So yes you are actually missing an apostrophe :

...','" + textBox6.Text + ",'"...

To avoid mistakes like these, and for other reasons (like security), parameterize your queries.

try
{
    using (var con = new OleDbConnection())
    {
        con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\User\Desktop\esoft\gym\gym\bin\Debug\Clients.accdb;";
        con.Open();

        using (var com = new OleDbCommand())
        {
            com.Connection = con;
            com.CommandText = "INSERT INTO gym ([BMI],[Health],[weight_change_to_healthy_bmi]) VALUES (@bmi,@health,@weight)";
            com.Parameters.AddWithValue("@bmi", textBox5.Text);
            com.Parameters.AddWithValue("@health", textBox6.Text);
            com.Parameters.AddWithValue("@weight", textBox4.Text);

            com.ExecuteNonQuery();
        }
    }
    MessageBox.Show("Saved");
}
catch (Exception ex)
{
    MessageBox.Show("Not saved: " + ex.Message);
}

What that means is that a single quote on any your fields can and in facts produce a SQL INJECTION security issue

In case you are looking for a behaviour called UPSERT (INSERT if is new data or do and UPDATE if found) you could create a STORE PROCEDURE that does that an call it using parameters or you could handle that on the upper layer depends on your approach/architecture guidelines

Answer 2

" ' " Apostrophe is missed .

com.CommandText = "INSERT INTO gym ([BMI],[Health],[weight_change_to_healthy_bmi]) VALUES ('" + textBox5.Text + "','" + textBox6.Text + "','" + textBox4.Text + "') WHERE ID='"+textBox2.Text+"',con";

Answer 3

You're missing an apostrophe here:

...','" + textBox6.Text + ",'"...

To avoid mistakes like these, and for other reasons (like security), parameterize your queries.

try
{
    using (var con = new OleDbConnection())
    {
        con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\User\Desktop\esoft\gym\gym\bin\Debug\Clients.accdb;";
        con.Open();

        using (var com = new OleDbCommand())
        {
            com.Connection = con;
            com.CommandText = "INSERT INTO gym ([BMI],[Health],[weight_change_to_healthy_bmi]) VALUES (@bmi,@health,@weight)";
            com.Parameters.AddWithValue("@bmi", textBox5.Text);
            com.Parameters.AddWithValue("@health", textBox6.Text);
            com.Parameters.AddWithValue("@weight", textBox4.Text);

            com.ExecuteNonQuery();
        }
    }
    MessageBox.Show("Saved");
}
catch (Exception ex)
{
    MessageBox.Show("Not saved: " + ex.Message);
}