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();

Google "little bobby tables" – Hans Passant Jan 07 '16 at 06:56 — Hans Passant, Jan 07 '16 at 06:56

Grant Winney · Answer 1 · 2016-01-07T03:00:26.267

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);
}

edited Jan 07 '16 at 03:00

answered Jan 07 '16 at 02:49

Grant Winney

65,241
13
115
165

this fixed the error. But now when i try to add a WHERE condition another error pops up stating that Query input must contain at least one table or query . – iboss Jan 07 '16 at 04:44
oops my bad. i forgot i used the insert function to test my connection. – iboss Jan 07 '16 at 04:55
How would you modify the above coding to work with the update WHERE function? I've been trying to have a crack at it and I'm failing big time. – iboss Jan 07 '16 at 05:01
I updated the code according to your advice and now end up with an error that reads "syntax error in UPDATE statement". the UPDATE statement looks like this : com.CommandText = "UPDATE gym SET ([BMI],[Health],[weight_change_to_healthy_bmi]) VALUES (@bmi,@health,@weight) where id = @id"; – iboss Jan 07 '16 at 13:01
nope nvm fixed that one. now the query runs with no hiccups but one teeny problem... no data is being updated??? – iboss Jan 07 '16 at 14:01
thank you so much for your help. ill do as you suggest. – iboss Jan 07 '16 at 14:11

score 2 · Answer 2 · 2016-01-07T04:24:40.677

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";

edited Jan 07 '16 at 04:24

answered Jan 07 '16 at 02:49

Toon - please include more details into this answer – Mauricio Gracia Gutierrez Jan 07 '16 at 04:20

Mauricio Gracia Gutierrez · Answer 3 · 2016-01-07T15:10:49.927

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

how would you edit the code so the values would be saved to a specific row, ie, WHERE ID="'textBox2.Text"' — iboss, Jan 07 '16 at 05:07
@iboss that it seems that you are looking for something called an 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 — Mauricio Gracia Gutierrez, Jan 07 '16 at 15:09

exception 2147217900 in oledb database

3 Answers3