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

Update data in access mdb database in c# form application " syntax error in update statement"

提问by Tasleem Ahmad

I was working in C# form application with an MS-Access mdbdatabase. I have a database in which I have a table Customerswith two columns CustomerIdAnd Balance. Both columns are of integerdatatype.

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

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

System.Data.OleDb.OleDbException：UPDATE 语句中出现语法错误。

在 System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
在 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
在 System.Data.OleDb.OleDbCommand.ExecuteCommandText( SystemObject.&
. 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) 在 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.

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

采纳答案by matzone

As gzaxx said .. Try change this

正如 gzaxx 所说 .. 尝试改变这个

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

with

和

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

回答by tsells

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

去掉 set 子句后面的逗号——你只更新了一个变量。set xxx = xxx , where 应该设置 xxx = xxx where.

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

回答by gzaxx

There is a comma after balance in your query. Also you are casting your balance to int32yet you are inserting it as string because of single quote between it.

您的查询中的 balance 后面有一个逗号。此外，您正在将余额转换为int32但由于它之间的单引号而将其作为字符串插入。

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

this query should work.

这个查询应该可以工作。

回答by Amit Bhati

you just change it to

你只要把它改成

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

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

and your code will work properly

并且您的代码将正常工作

回答by Babu Torati

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