System data oledb oledbexception ошибка синтаксиса в инструкции update

• Remove From My Forums

• Question

• I have one code for updating the password, however the code is giving the error that «Syntax error in UPDATE statement.», before this I was getting error «Syntax
  error in Insert into statement» which was solved by «Noam B«, the link to that page is

  here. Now i am getting these error. I tried to solve it but no success, can you guys help me once again. 

  The code that I have written is:

  using System;
  using System.Collections.Generic;
  using System.ComponentModel;
  using System.Data;
  using System.Drawing;
  using System.Text;
  using System.Windows.Forms;
  using System.Data.OleDb;
  
  namespace Cyber_Application
  {
      public partial class ChangePassword : Form
      {
          string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Cyber Application\param.accdb; Jet OLEDB:Database Password=123456";
  
          public ChangePassword()
          {
              InitializeComponent();
          }
  
          private void changePassword()
          {
              OleDbConnection cn = new OleDbConnection(connectionString);
              cn.Open();
              string scmd1 = "UPDATE EnterSoftware set Password = '" + txtNewPassword.Text + "' where UserName = 'admin'";
              OleDbCommand cmd1 = new OleDbCommand(scmd1, cn);
              OleDbDataReader sdr1 = cmd1.ExecuteReader();
              cn.Close();
              MessageBox.Show("Password has been changed successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
              this.Close();
          }
  
          private void btnOk_Click(object sender, EventArgs e)
          {
              OleDbConnection cn = new OleDbConnection(connectionString);
              cn.Open();
              string scmd = "select Password from EnterSoftware";
              OleDbCommand cmd = new OleDbCommand(scmd, cn);
              OleDbDataReader sdr = cmd.ExecuteReader();
  
              while (sdr.Read())
              {
                  if (txtCurrentPassword.Text == sdr["Password"].ToString())
                  {
                      if (txtNewPassword.Text == "" || txtConfirmPassword.Text == "")
                      {
                          MessageBox.Show("Password cannot be blank", "Change Password Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                      }
  
                      if (txtNewPassword.Text == txtConfirmPassword.Text && txtNewPassword.Text != "" || txtConfirmPassword.Text != "")
                      {
                          changePassword();
                      }
                      else
                      {
                          MessageBox.Show("Password do not match", "Change Password Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                          txtCurrentPassword.Text = "";
                          txtNewPassword.Text = "";
                          txtConfirmPassword.Text = "";
                          txtCurrentPassword.Focus();
                      }
                  }
  
                  else
                  {
                      MessageBox.Show("Invalid Current Password", "Change Password Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                      txtCurrentPassword.Text = "";
                      txtNewPassword.Text = "";
                      txtConfirmPassword.Text = "";
                      txtCurrentPassword.Focus();
                  }
              }
              cn.Close();
          }
      }
  }
  
  

  The error code is as below:

  System.Data.OleDb.OleDbException was unhandled
    Message=Syntax error in UPDATE statement.
    Source=Microsoft Office Access Database Engine
    ErrorCode=-2147217900
    StackTrace:
         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.ExecuteReader(CommandBehavior behavior)
         at System.Data.OleDb.OleDbCommand.ExecuteReader()
         at Cyber_Application.ChangePassword.changePassword() in E:ClientsS V Cyber CafeCyber ApplicationCyber ApplicationChangePassword.cs:line 27
         at Cyber_Application.ChangePassword.btnOk_Click(Object sender, EventArgs e) in E:Cyber ApplicationChangePassword.cs:line 52
         at System.Windows.Forms.Control.OnClick(EventArgs e)
         at System.Windows.Forms.Button.OnClick(EventArgs e)
         at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
         at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
         at System.Windows.Forms.Control.WndProc(Message& m)
         at System.Windows.Forms.ButtonBase.WndProc(Message& m)
         at System.Windows.Forms.Button.WndProc(Message& m)
         at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
         at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
         at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
         at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
         at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
         at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
         at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
         at System.Windows.Forms.Application.RunDialog(Form form)
         at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
         at System.Windows.Forms.Form.ShowDialog()
         at Cyber_Application.MainForm.changePasswordToolStripMenuItem_Click(Object sender, EventArgs e) in E:ClientsS V Cyber CafeCyber ApplicationCyber ApplicationMainForm.cs:line 79
         at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
         at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
         at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
         at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
         at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
         at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
         at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
         at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)
         at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
         at System.Windows.Forms.Control.WndProc(Message& m)
         at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
         at System.Windows.Forms.ToolStrip.WndProc(Message& m)
         at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m)
         at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
         at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
         at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
         at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
         at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
         at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
         at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
         at System.Windows.Forms.Application.Run(Form mainForm)
         at Cyber_Application.Program.Main() in E:ClientsS V Cyber CafeCyber ApplicationCyber ApplicationProgram.cs:line 17
         at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
         at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
         at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
         at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
         at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
         at System.Threading.ThreadHelper.ThreadStart()
    InnerException: 
  
  

  • Edited by

    Monday, September 26, 2011 9:59 AM

Я работал в приложении формы C # с базой данных MS-Access mdb. У меня есть база данных, в которой есть таблица Customers с двумя столбцами CustomerId и Balance. Оба столбца имеют тип данных integer.

Я получал ошибку

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 (Object & executeResult)
в System.Data.OleDb.OleDbCommand.ExecuteCommand (поведение CommandBehavior, Object и executeResult)
в System.Data.OleDb.OleDbCommand.ExecuteReaderInternal (поведение CommandBehavior, метод String)
в System.Data.OleDb.OleDbCommand.ExecuteNonQuery ()
в xml_and_db_test.Form1.button1_Click (отправитель объекта, EventArgs e) в G: my Documents Visual Studio 2008 Projects xml_and_db_test xml_and_db_test Form1.cs: строка 45

Коды, которые я пробовал до сих пор:

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

Некоторые коды находятся в комментариях, а некоторые — при каждом методе, я получаю одну и ту же ошибку.

Как сказал gzaxx .. Попробуйте изменить это

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

С участием

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

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

Вы просто измените это на

OleDbCommand update = new OleDbCommand («UPDATE Customers SET [Balance] = ‘» + balance + «‘, WHERE [id] =» + id + «», con);

И ваш код будет работать правильно

Избавьтесь от запятой после предложения set — у вас обновляется только одна переменная. установить xxx = xxx, где должно быть установлено xxx = xxx, где.

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

В вашем запросе после баланса стоит запятая. Также вы переводите свой баланс на int32, но вставляете его как строку из-за одинарных кавычек между ними.

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

Этот запрос должен работать.