Updating data in the mdb access database in an application of the C # form "Syntax error in the update statement"

Question

I worked in a C # form application with an MS-Access mdb database. I have a database in which I have a Customers table with two columns CustomerId And Balance . Both columns have an integer data type.

The error I was getting is

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

in System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling (OleDbHResult hr)
in System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult (tagDBPARAMS dbParams, Object & executeResult)
in System.Data.OleDb.OleDbCommand.ExecuteCommandText (Object & executeResult)
in System.Data.OleDb.OleDbCommand.ExecuteCommand (behavior of CommandBehavior, Object & executeResult)
in System.Data.OleDb.OleDbCommand.ExecuteReaderInternal (CommandBehavior behavior, String method)
in System.Data.OleDb.OleDbCommand.ExecuteNonQuery ()
in 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

The codes I've tried so far

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

some codes in the comments, and some with each method. I get the same error.

Answer 1

Like gzaxx said .. try changing this

 string queryText = "UPDATE Customers SET Balance = ?, where CustomerId = ?;"; 

with

 string queryText = "UPDATE Customers SET Balance = ? where CustomerId = ?;"; 

Answer 2

The request has a comma after the balance. Also you add your balance to int32 , but you insert it as a string due to the single quote between it.

 "UPDATE Customers SET Balance = " + balance + " WHERE id = " + id 

This request should work.

Answer 3

Get rid of the comma after the set clause - you only have one variable to update. set xxx = xxx, where xxx = xxx where should be set.

 OleDbCommand update = new OleDbCommand("UPDATE Customers SET Balance = '" + balance + "' WHERE id = " + id + " ", con); 

Answer 4

just change it to

OleDbCommand update = new OleDbCommand ("UPDATE Customers SET [Balance] = '" + balance + "', WHERE [id] =" + id + "", con);

and your code will work correctly

Answer 5

  OleDbCommand o_cmd = new OleDbCommand("Update tbl_SalesTax set tbl_SalesTax.SerialNumber='" + txtSerialNo.Text + "' ,tbl_SalesTax.PartyCode='" + txtPartyCode.Text + "' ,tbl_SalesTax.PartyName='" + txtPartyName.Text + "' ,tbl_SalesTax.TinNumber='" + txtTinNo.Text + "' ,tbl_SalesTax.Gr='" + cmbgr.Text + "' ,tbl_SalesTax.Qty='" + txtQty.Text + "' ,tbl_SalesTax.Price='" + txtPrice.Text + "' ,tbl_SalesTax.Basic='" + txtBaisc.Text + "' ,tbl_SalesTax.Value='" + txtValue.Text + "' ,tbl_SalesTax.Total='" + txtTotal.Text + "' ,tbl_SalesTax.Bags='" + txtBags.Text + "' ,tbl_SalesTax.DumpCode='" + txtDumpCode.Text + "' where tbl_SalesTax.BookNumber='" + txtBookNo.Text + "'", my_con);