在c#表单应用程序“更新语句中的语法错误”中更新访问mdb数据库中的数据

Question

我在使用MS-Accessmdb数据库的C#表单应用程序中工作。我有一个数据库，其中有一个表客户，其中包含两列CustomerId和Balance。两列都是整数数据类型。

我得到的错误是

系统数据OleDb。OLEDBEException:UPDATE语句中的语法错误。

在系统。数据。OleDb。OleDbCommand。系统中的执行命令文本错误处理（OleDbHResult hr）
。数据。OleDb。OleDbCommand。执行命令文本ForSingleResult（标记DBPARAMS dbParams，对象

到目前为止我一直在尝试的代码是

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

有些代码在注释中，有些代码在每个方法中都会出现相同的错误。

Answer 1

去掉set子句后面的逗号-您只有一个变量正在更新。设置xxx=xxx，其中应设置xxx=xxx，其中。

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

Answer 2

查询中余额后有一个逗号。此外，您正在将余额转换为int 32，但由于它之间只有一个引号，因此您将其作为字符串插入。

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

此查询应该有效。

Answer 3

正如gzaxx所说。。尝试更改此

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

具有

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