I was working in C# form application with an MS-Access mdb
database. I have a database in which I have a table Customers
with two columns CustomerId
And Balance
. Both columns are of integer
datatype.
Error I was getting is
System.Data.OleDb.OleDbException: Syntax error in UPDATE statement.
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 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
Codes I have tried till now are
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 are In comments and some are with every method i'm getting the same error.
As gzaxx said .. Try change this
string queryText = "UPDATE Customers SET Balance = ?, where CustomerId = ?;";
with
string queryText = "UPDATE Customers SET Balance = ? where CustomerId = ?;";
There is a comma after balance in your query. Also you are casting your balance to int32
yet you are inserting it as string because of single quote between it.
"UPDATE Customers SET Balance = " + balance + " WHERE id = " + id
this query should work.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With