title: Update data in access mdb database in c# form application " syntax error in update statement"
body: <p>I was working in C# form application with an MS-Access <code>mdb</code> database. I have a database in which I have a table <code>Customers</code> with two columns <code>CustomerId</code> And <code>Balance</code>. Both columns are of <code>integer</code> datatype.</p>
  
  <p>Error I was getting is</p>
  
  <blockquote>
    <p>System.Data.OleDb.OleDbException: <strong>Syntax error in UPDATE statement.</strong></p>
    
    <p>at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)<br>
    at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object&amp; executeResult)<br>
    at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&amp; executeResult)<br>
    at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object&amp; executeResult)<br>
    at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)<br>
    at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()<br>
    at xml_and_db_test.Form1.button1_Click(Object sender, EventArgs e) in G:\my Documents\Visual Studio 2008\Projects\xml_and_db_test\xml_and_db_test\Form1.cs:line 45  </p>
  </blockquote>
  
  <p>Codes I have tried till now are</p>
  
  <pre><code>try
  {
     OleDbConnection con = new 
     OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\database_for_kissan_Pashu_AhaR_Bills.mdb");
  
     int balance = Convert.ToInt32(textBox2.Text);
     int id = Convert.ToInt32(textBox1.Text);
  
     // int recordnumb = int.Parse(recordTextBox.Text);
  
     //  OleDbConnection oleDbConnection = new            OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Checkout-1\\Documents\\contact.accdb");    
     OleDbCommand update = new OleDbCommand("UPDATE Customers  SET Balance = '" + balance + "',  WHERE id = " + id + " ", con);
  
     con.Open();
     update.ExecuteNonQuery();
     con.Close();
  
     // string queryText = "UPDATE Customers SET Balance = ?, where CustomerId = ?;";
     //string queryText = " 'UPDATE Customers SET Balance =' " + balance+ " ' WHERE CustomerId=  ' " +  id +  " ' " ;
  
     //OleDbCommand cmd = new OleDbCommand(queryText, con);
     //cmd.CommandType = CommandType.Text;
     //cmd.Parameters.AddWithValue("@balance", Convert.ToInt32(textBox2.Text));
     //cmd.Parameters.AddWithValue("@ID", Convert.ToInt32(textBox1.Text));
     //cmd.Parameters.Add("Balance", OleDbType.Integer).Value = Convert.ToInt32(textBox2.Text);
     //cmd.Parameters.Add("CustomerId", OleDbType.Integer).Value = Convert.ToInt32(textBox1.Text);
  
     //con.Open(); // open the connection
     ////OleDbDataReader dr = cmd.ExecuteNonQuery();
  
     //int yy = cmd.ExecuteNonQuery();
     //con.Close();
  }
  catch (Exception ex)
  {
      string c = ex.ToString();
      MessageBox.Show(c);
  }
  
  //try
  //{
  //    OleDbConnection con = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = G:\\my Documents\\Visual Studio 2008\\Projects\\xml_and_db_test\\xml_and_db_test\\bin\\Debug\\database_for_kissan_Pashu_AhaR_Bills.mdb");
  //    string queryText = "UPDATE Customers SET Balance = ?, where CustomerId = ?;";
  
  //    OleDbCommand cmd = new OleDbCommand(queryText, con);
  //    cmd.CommandType = CommandType.Text;
  //    //cmd.Parameters.AddWithValue("@balance", Convert.ToInt32(textBox2.Text));
  //    //cmd.Parameters.AddWithValue("@ID", Convert.ToInt32(textBox1.Text));
  
  //    cmd.Parameters.Add("Balance", OleDbType.Integer).Value = Convert.ToInt32(textBox2.Text);
  
  //    cmd.Parameters.Add("CustomerId", OleDbType.Integer).Value = Convert.ToInt32(textBox1.Text);
  //    con.Open(); // open the connection
  //    //OleDbDataReader dr = cmd.ExecuteNonQuery();
  
  //    int yy = cmd.ExecuteNonQuery();
  //    con.Close();
  
  //}
  //catch (Exception ex)
  //{
  //    string c = ex.ToString();
  //    MessageBox.Show(c);
  
  //}
  //string connetionString = null;
  //OleDbConnection connection;
  //OleDbDataAdapter oledbAdapter = new OleDbDataAdapter();
  //string sql = null;
  //connetionString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = G:\\my Documents\\Visual Studio 2008\\Projects\\xml_and_db_test\\xml_and_db_test\\bin\\Debug\\database_for_kissan_Pashu_AhaR_Bills.mdb;";
  //connection = new OleDbConnection(connetionString);
  //sql = "update Customers set Balance = '1807' where CustomerId = '1'";
  //try
  //{
  //    connection.Open();
  //    oledbAdapter.UpdateCommand = connection.CreateCommand();
  //    oledbAdapter.UpdateCommand.CommandText = sql;
  //    oledbAdapter.UpdateCommand.ExecuteNonQuery();
  //    MessageBox.Show("Row(s) Updated !! ");
  //    connection.Close();
  //}
  //catch (Exception ex)
  //{
  //    MessageBox.Show(ex.ToString());
  //}
  </code></pre>
  
  <p>some codes are In comments and some are with every method i'm getting the same error.</p>