Ошибка синтаксиса в инструкции update access

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.

I have been working on this issue for ages! I have an MSAccess 2007 db and I am using Visual Studio 2010 and Visual Basic to write an application that reads and writes from the database. The read works just fine but when I try and write an updated password back into the database, it fails. Initially I had just a plain ‘Syntax Error’ which wasn’t that helpful, but with a bit of research I noted that access databases seem to have an issue with columns called password. I renamed that and tried again, now I get this error when the

da.Update(ds,»All_Users»)

command is executed.

The full error message from VS is :
Syntax error (missing operator) in query expression '((ID = ?) AND ((? = 1 AND Forename IS NULL) OR (Forename = ?)) AND ((? = 1 AND Surname IS NULL) OR (Surname = ?)) AND ((? = 1 AND User_Level IS NULL) OR (User_Level = ?)) AND ((? = 1 AND Last Logon IS NULL) OR (Last Logon = ?)) AND ((? = 1 AND Allow IS NU'.

My code is as follows:

Private Sub btnSave_Click(snder as System.Object, e As System.EventArgs) Handles btnSave.Click
    Dim Con As New OleDb.OleDbConnection
    Dim ConString As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim Sql As String = "SELECT * FROM tblUsers"
    '
    ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        Application.StartupPath & "\Data\Users.accdb;Jet OLEDB:Database Password=---------;"
    Con.ConnectionString = ConString
    Con.Open()
    da = New OleDb.OleDbDataAdapter(Sql, Con)
    da.Fill(ds, "All_Users")
    'Now loop through the records until you find the one for this user
    For i = 0 To ds.Tables("All_Users").Rows.Count - 1
        If ds.Tables("All_Users").Rows(i).Item(0).ToString = CurrentUser.ID Then
            ds.Tables("All_Users").Rows(i).Item(6) = txtConfirmPassword.Text
        End If
    Next
    CurrentUser.Password = txtConfirmPassword.Text
    '
    Dim cb As New OleDb.OleDbCommandBuilder(da)
    da.Update(ds, "All_Users")
    '
    Con.Close()
    MessageBox.Show("Your password has been sucessfully updated.", "Success", _
                    MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
    Me.Close()

Перейти к контенту

1
2

CurrentProject.Connection.Execute "UPDATE ÂÇàêàçå SET [Ïðîäóêò] = " & ProduktVr & ", [Êîëè÷åñòâî] = " & KolichestvoVr & ", [ÖåíàÇàØò]=" & CenaZaShtVr & ", " _
   & "[Ï6]=" & P6Vr & ", [Çàêàç]=" & ZakazVr & " WHERE Çàêàç =" & NomVSpiskeZak & ""

1

[Количество] = " & Nz(KolichestvoVr, "NULL")

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

Set StrVzakazeVr = CurrentDb.OpenRecordset("SELECT Продукт, Количество, ЦенаЗаШт, ТипЦены, " _
          & "Дополнительно, П1, П2, П3, П4, П5, " _
          & "П6, Заказ " _
          & "FROM ВЗаказеВременная WHERE Заказ =" & NomVSpiskeZak)
          
    ProduktVr = StrVzakazeVr![Продукт]
    KolichestvoVr = StrVzakazeVr![Количество]
    CenaZaShtVr = StrVzakazeVr![Ценазашт]
    TipCenyVr = StrVzakazeVr![Типцены]
    DopolnitelnoVr = StrVzakazeVr![Дополнительно]
    P1Vr = StrVzakazeVr![П1]
    P2Vr = StrVzakazeVr![П2]
    P3Vr = StrVzakazeVr![П3]
    P4Vr = StrVzakazeVr![П4]
    P5Vr = StrVzakazeVr![П5]
    P6Vr = StrVzakazeVr![П6]
    ZakazVr = StrVzakazeVr![Заказ]
    
    CurrentProject.Connection.Execute "UPDATE ВЗаказе SET [Продукт] = " & ProduktVr & ", [Количество] = " & KolichestvoVr & ", " _
    & "[ЦенаЗаШт]=" & CenaZaShtVr & ", [Дополнительно]='" & DopolnitelnoVr & "', " _
    & "[П1]=" & P1Vr & ", WHERE Заказ = " & NomVSpiskeZak & ""

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

Sub www140710()
Dim s1, NomVSpiskeZak
Dim StrVzakazeVr As DAO.Recordset
NomVSpiskeZak = 1
 
s1 = ""
s1 = s1 & "SELECT Продукт, Количество, ЦенаЗаШт, ТипЦены, "
s1 = s1 & "Дополнительно, П1, П2, П3, П4, П5, П6, Заказ"
s1 = s1 & " FROM ВЗаказеВременная WHERE Заказ =" & NomVSpiskeZak
Debug.Print s1
Set StrVzakazeVr = CurrentDb.OpenRecordset(s1)
          
    
''    TipCenyVr = StrVzakazeVr![Типцены]
 
    s1 = ""
    s1 = s1 & "UPDATE ВЗаказе SET"
    s1 = s1 & " [Продукт] = " & StrVzakazeVr![Продукт]
    s1 = s1 & ", [Количество] = " & StrVzakazeVr![Количество]
    s1 = s1 & ", [ЦенаЗаШт]=" & StrVzakazeVr![Ценазашт]
    s1 = s1 & ", [Дополнительно]='" & Nz(StrVzakazeVr![Дополнительно], "0") & "'"
    s1 = s1 & ", [П1]=" & Nz(StrVzakazeVr![П1], 0)
    s1 = s1 & ", WHERE Заказ = " & NomVSpiskeZak
    Debug.Print s1
    CurrentProject.Connection.Execute s1
 
End Sub

1
2
3
4
5
6
7
8
9
10
11
12
13

Set StrVr = CurrentDb.OpenRecordset("SELECT Äàòà, Ïîñòàâùèê, Ñòàòóñ, Íà÷àëîÏðîèçâîäñòâà, " _
          & "Îêîí÷àíèåÏðîèçâîäñòâà, Íà÷àëîÄîñòàâêè, Îêîí÷àíèåÄîñòàâêè, Ïðîåêò, Ïîäïðîåêò, Íà÷àëîÏðîèçâîäñòâàÏëàí, " _
          & "Îêîí÷àíèåÏðîèçâîäñòâàÏëàí, Íà÷àëîÄîñòàâêèÏëàí, Îêîí÷àíèåÄîñòàâêèÏëàí, ÍîìåðÑ÷åòà, ÄàòàÑ÷åòåà, ÑòàòóñÏÔ, " _
          & "ÑëóæåáêàÄàòà, Ñðîê, Êà÷åñòâî, Öåíà " _
          & "FROM ÂðåìåííîåÑîõðàíåíèåÇàêàçà WHERE Çàêàç =" & NomVSpiskeZak)
 
    NachProizVr = Format(StrVr![Íà÷àëîïðîèçâîäñòâà], "dd.mm.yyyy")
    OkonProizVr = Format(StrVr![Îêîí÷àíèåÏðîèçâîäñòâà], "dd.mm.yyyy")
    NachDostVr = Format(StrVr![Íà÷àëîÄîñòàâêè], "dd.mm.yyyy")
    
CurrentProject.Connection.Execute "UPDATE Çàêàç SET [Íà÷àëîÏðîèçâîäñòâà]='" & NachProizVr & "', [Îêîí÷àíèåÏðîèçâîäñòâà]='" & OkonProizVr & "', " _
& "[Íà÷àëîÄîñòàâêè]='" & NachDostVr & "' " _
      & "WHERE Íîìåð =" & NomVSpiskeZak & ""

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

Dim s1
Dim strVr As DAO.Recordset
s1 = ""
s1 = s1 & "SELECT Дата, Поставщик, Статус, НачалоПроизводства, "
s1 = s1 & " ОкончаниеПроизводства, НачалоДоставки, ОкончаниеДоставки, Проект,"
s1 = s1 & " Подпроект, НачалоПроизводстваПлан, "
s1 = s1 & " ОкончаниеПроизводстваПлан, НачалоДоставкиПлан, ОкончаниеДоставкиПлан,"
s1 = s1 & " НомерСчета, ДатаСчетеа, СтатусПФ, "
s1 = s1 & " СлужебкаДата, Срок, Качество, Цена "
s1 = s1 & " FROM ВременноеСохранениеЗаказа WHERE Заказ =" & NomVSpiskeZak
 
Set strVr = CurrentDb.OpenRecordset(s1)
 NachProizVr = Format(strVr![Началопроизводства], "mm/dd/yyyy")
 OkonProizVr = Format(strVr![ОкончаниеПроизводства], "mm/dd/yyyy")
 NachDostVr = Format(strVr![НачалоДоставки], "mm/dd/yyyy")
   
s1 = ""
s1 = s1 & " UPDATE Заказ SET "
s1 = s1 & " [НачалоПроизводства]=#" & NachProizVr & "#,"
s1 = s1 & " [ОкончаниеПроизводства]=#" & OkonProizVr & "#, "
s1 = s1 & " [НачалоДоставки]=#" & NachDostVr & "# "
s1 = s1 & " WHERE Номер =" & NomVSpiskeZak & ""
 CurrentProject.Connection.Execute s1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

Set strVr = CurrentDb.OpenRecordset(s1)
 
 
 
 s2 = ""
 
 If IsNull(strVr![Началопроизводства]) = False Then
 s2 = s2 & " [НачалоПроизводства]=#" & Format(strVr![Началопроизводства], "mm/dd/yyyy") & "#,"
End If
 
If IsNull(strVr![ОкончаниеПроизводства]) = False Then
s2 = s2 & " [ОкончаниеПроизводства]=#" & Format(strVr![ОкончаниеПроизводства], "mm/dd/yyyy") & "#,"
End If
 
If IsNull(strVr![НачалоДоставки]) = False Then
s2 = s2 & " [НачалоДоставки]=#" & Format(strVr![НачалоДоставки], "mm/dd/yyyy") & "#"
End If
 j2 = Len(s2)
 If j2 > 10 Then
 If Mid(s2, j2, 1) = "," Then
 s2 = Mid(s2, 1, j2 - 1)
 End If
 s1 = ""
s1 = s1 & " UPDATE Заказ SET "
 
s1 = s1 & s2
'Номер
s1 = s1 & " WHERE номер =" & NomVSpiskeZak
Debug.Print s1
DoCmd.RunSQL s1
End If
' CurrentProject.Connection.Execute s1

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

 private void listBox9_SelectedValueChanged(object sender, EventArgs e)
 {
        AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData));
        connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|\Trip Sheet Management System\WABCO.mdb");
        sql = "SELECT ID,[TRIP COST] FROM TMSDETAILS";
        dataAdapter = new OleDbDataAdapter(sql, connection);
        dataTable = new DataTable();
        bindingSource = new BindingSource();
        connection.Open();
        dataAdapter.Fill(dataTable);
        bindingSource.DataSource = dataTable;
        dataGridView1.DataSource = bindingSource;
        connection.Close();
  }


 private void button8_Click(object sender, EventArgs e)
    {

        commandBuilder = new OleDbCommandBuilder(dataAdapter);

        try
        {
            dataAdapter.Update(dataTable);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

 private void listBox9_SelectedValueChanged(object sender, EventArgs e)
{
    AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData));
    connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|\Trip Sheet Management System\WABCO.mdb");
    sql = "SELECT ID,[TRIP COST] FROM TMSDETAILS";
    dataAdapter = new OleDbDataAdapter(sql, connection);
    dataTable = new DataTable();
    bindingSource = new BindingSource();
    connection.Open();
    dataAdapter.Fill(dataTable);
    bindingSource.DataSource = dataTable;
    dataGridView1.DataSource = bindingSource;
    connection.Close();
}


private void button8_Click(object sender, EventArgs e)
{

    commandBuilder = new OleDbCommandBuilder(dataAdapter);
    commandBuilder.QuotePrefix = "["; 
    commandBuilder.QuoteSuffix = "]";

    try
    {
        dataAdapter.Update(dataTable);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

 protected void Button1_Click(object sender, EventArgs e)
{
    OleDbConnection con = new OleDbConnection();
    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="  
        Server.MapPath("~/Database/registration.accdb");

    con.Open();

    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = con;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText =
        "UPDATE into userdata(password)values('" TextBox1.Text "') where id=@id";
    cmd.ExecuteNonQuery();
    con.Close();
    Response.Write("alert('Password Reset Successfully done');"); 
}
 

 query = "Update [tableName] SET [ColumnName] = 'Values', [ColumnName1] = 'Values2',...";
 

 OleDbConnection con = new OleDbConnection();
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="  
    Server.MapPath("~/Database/registration.accdb");

con.Open();

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText =
    "UPDATE yourTableName SET [yourColumnName] = @YourFirstValue, [secondColumnName] = @YourSecondValue WHERE [columnKey] = @ID"

cmd.Parameters.AddWithValue("@YourFirstValues", textbox1.Text);
cmd.Parameters.AddWithValue("@YourSecondValue ", textbox2.Text);
cmd.Parameters.AddWithValue("@ID", textbox3.Text);

cmd.ExecuteNonQuery();
con.Close();
Response.Write("alert('Password Reset Successfully done');"); 
 

    "UPDATE  userdata SET password=@password where id=@id";
 
 

 cmd.Parameters.AddWithValue("@id", id);
 cmd.Parameters.AddWithValue("@password", TextBox1.Text);