Syntax error in UPDATE statement.

Question

Hi all,

someone help me out pls...

here my code:

Dim myqry As String = Nothing

  myqry = "UPDATE Users SET Name = '" & Namebox.Text & "', Password = '" & PassBox.Text & "', Age = '" & AgeBox.Text & "', JobTitle = '" & JobTBox.Text & "', "
  myqry = myqry & " Address = '" & AddressBox.Text & "', PhoneNo = '" & PhoneBox.Text & "', isAdmin = '" & CheckBox1.CheckState & "' WHERE (((Users.EmployeeID)=" & EmpBox.Text & "));"

  mycmd = New OleDbCommand(myqry, conn)
  mycmd.ExecuteNonQuery()

Answer 1

If you don't tell what goes wrong then nobody can help you.

However probably better is 

 dim myqry = "UPDATE Users SET Name  = ? ,Password = ?, this for all your fields  WHERE (((Users.EmployeeID)= ?;"

dim mycmd as new OledBCommand(myqry, conn)

mycmd.parameters.addwithvalue("?",NameBox.Text)
mycmd.parameters.addwithvalue("?",PassBox.Text)
.
this for all your fields in the correct sequence and as last

.
mycmd.parameters.addwithvalue("?",EmpBox.Text)
mycmd.ExecuteNonQuery

---

Success
Cor

Answer 2

I agree with Cor...  consider taking his advice and use query parameters.

Additionally, I suspect you are having problems because you are wrapping the Age parameter in single quotes when it is probably expectecting an integer value (without single quotes).  Also possibly your isAdmin value is returning a string value of "checked" or "unchecked".  I doubt this is what you want -- rather this is likely a bit field that can only receive a zero or one value.

All this speculation could be avoided if you set a breakpoint after the query text is set and then inspect the myqry variable's value.  Alternately you could add a Debug.Writeline(myqry) statement before you execute the query.  This would show how the actual sql statement will be sent to the DB.  Then you can copy that statement and try it directly on the DB itself (in a new query window for SQL Server or a new Query designer for Access).  This is always good advice and often allows you to troubleshoot issues like this before you come to the forum.

Answer 3

First thing I would do is enclose all of your column names in brackets (e.g. [Password]). Actually Password is a Jet reserved word so that could be causing your syntax error.

As the others have suggested, I would also recommend using Command Parameters.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 4

so sorry Cor, actually im working with ListView that show data from database, this problem appear when i click save button after edit .

with ur suggestion, i come with new change, but still same error appear

(sorry for my bad english )

code:

 

Private Sub Editbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Editbtn.Click
    If EmpBox.Text = "" Then
      MessageBox.Show("Please Select Record to Update", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Else
      str = "edit"
      Call Set2()

    End If
  End Sub


  Private Sub Savebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
    If str = "add" Then
      ''''''ADD NEW RECORD'''''''
      If EmpBox.Text = "" Or Namebox.Text = "" Or PassBox.Text = "" Then
        MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
      Else
        myqry = "INSERT INTO Users (EmployeeID, Name, [Password], Age, JobTitle, Address, PhoneNo, isAdmin)"
        myqry = myqry + "VALUES('" & EmpBox.Text & "','" & Namebox.Text & "','" & PassBox.Text & "','" & AgeBox.Text & "','" & 

        JobTBox.Text & "','" & AddressBox.Text & "','" & PhoneBox.Text & "','" & CheckBox1.CheckState & "')"
        mycmd = New OleDbCommand
        With mycmd
          .CommandText = myqry
          .Connection = conn
          .ExecuteNonQuery()
        End With
        Call Set1()
      End If

    Else
      ''''''''''UPDATE RECORD'''''''
      If Namebox.Text = "" Or PassBox.Text = "" Then
        MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
      Else

       Dim myqry = "UPDATE Users SET Name = '" & Namebox.Text & "', Password = '" & PassBox.Text & "', Age = '" & AgeBox.Text &  "', JobTitle = '" & JobTBox.Text & "', Address = '" & AddressBox.Text & "', PhoneNo = '" & PhoneBox.Text & "', isAdmin = '" &   CheckBox1.CheckState & "' WHERE (((Users.EmployeeID)=" & EmpBox.Text & "));"

        Dim mycmd As New OleDbCommand(myqry, conn)

        mycmd.Parameters.AddWithValue("Name", Namebox.Text)
        mycmd.Parameters.AddWithValue("Password", PassBox.Text)
        mycmd.Parameters.AddWithValue("Age", AgeBox.Text)
        mycmd.Parameters.AddWithValue("JobTitle", JobTBox.Text)
        mycmd.Parameters.AddWithValue("Address", AddressBox.Text)
        mycmd.Parameters.AddWithValue("PhoneNo", PhoneBox.Text)
        mycmd.Parameters.AddWithValue("isAdmin", CheckBox1.CheckState)
        mycmd.Parameters.AddWithValue("EmployeeID", EmpBox.Text)   

    mycmd.ExecuteNonQuery()


        Call Set1()
      End If
    End If

    Call FillListview()
    Call ClearAlltextBox()

  End Sub

Answer 5

Ok, after change it with Paul suggestion...there is no more syntaz error in update statement.

but the problem is database not update with new data.

Answer 6

Ok, after change it with Paul suggestion...there is no more syntaz error in update statement.

but the problem is database not update with new data.

I don't see any problem with the syntax. Did you verify that the EmployeeID that you are trying to update is in the database table? I don't see your connection string but are you connecting to the correct database?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 7

here my connection string:

    Sub ConntoDB()
        Try
            With conn
                If .State = ConnectionState.Open Then .Close()
                .ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ComputerCentre.accdb")
                .Open()
            End With
        Catch ex As Exception
            MessageBox.Show("Unable to connect", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Application.Exit()
        End Try
    End Sub

 Sub FillListview()
        LV.Items.Clear()
        myqry = "SELECT * from Users ORDER BY EmployeeID ASC"
        mycmd = New OleDbCommand(myqry, conn)
        mydr = mycmd.ExecuteReader

        While mydr.Read
            With LV
                .Items.Add(mydr("EmployeeID"))
                With .Items(.Items.Count - 1).SubItems
                    .Add(mydr("Name"))
                    .Add(mydr("Password"))
                    .Add(mydr("Age"))
                    .Add(mydr("JobTitle"))
                    .Add(mydr("Address"))
                    .Add(mydr("PhoneNo"))

                End With
            End With
        End While
    End Sub

Private Sub AddUSER_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call LVsettings()
        Call ConntoDB()
        Call FillListview()
        Call Set1()
    End Sub

im also used EmployeeID and password for login, and there is no problem for login state....thanks for your time...

 

Answer 8

OK, so what part of the code that you posted isn't working?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 9

all run without any error or break,

but when i click save button after edit some data, database and listview not updated as well.

 

fyi,

 

EmployeeID   = text (primary key)

Answer 10

hello,

I recommend to handle insert, select, update, delete uses parameters do not like this doing is not recommended ..

you have to add other fields, and thus test tells us

 

    Using cn As New OleDbConnection("conection")

 

            Dim select_query As String = "@UPDATE Users Set Name @Name ,Password=@Password,Age=@Age,JobTitle=@JobTitle ,Address =@Address,PhoneNo =@PhoneNo where Users.EmployeeID =@EmployeeID"

            Dim cmd As New OleDbCommand(select_query, cn)

            cmd.Parameters.AddWithValue("@Name", valor1)

            cmd.Parameters.AddWithValue("@Password", valor1)

            cmd.Parameters.AddWithValue("@Age", valor1)

            cmd.Parameters.AddWithValue("@Address", valor1)

            cmd.Parameters.AddWithValue("@PhoneNo", valor1)

            cmd.ExecuteNonQuery()

 

        End Using

---

Enmanuel Grullard@Developer ASP.NET

Answer 11

Enmanuel, this was already suggested. Also, please to not propose your own responses as the answer.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 12

all run without any error or break,

but when i click save button after edit some data, database and listview not updated as well.

 

fyi,

 

EmployeeID   = text (primary key)

Could you post your current Update code?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 13

ok, here Paul

 Private Sub Savebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
    If str = "add" Then
      ''''''ADD NEW RECORD'''''''
      If EmpBox.Text = "" Or Namebox.Text = "" Or PassBox.Text = "" Then
        MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
      Else
        myqry = "INSERT INTO Users (EmployeeID, Name, [Password], Age, JobTitle, Address, PhoneNo, isAdmin)"
        myqry = myqry + "VALUES('" & EmpBox.Text & "','" & Namebox.Text & "','" & PassBox.Text & "','" & AgeBox.Text & "','" & JobTBox.Text & "','" & AddressBox.Text & "','" & PhoneBox.Text & "','" & CheckBox1.CheckState & "')"
        mycmd = New OleDbCommand
        With mycmd
          .CommandText = myqry
          .Connection = conn
          .ExecuteNonQuery()
        End With
        Call Set1()
      End If

    Else
      'UPDATE RECORD      

           If EmpBox.Text = "" Or Namebox.Text = "" Or PassBox.Text = "" Then
        MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
      Else

        Dim myqry = "UPDATE Users SET Name = '" & Namebox.Text & "', [Password] = '" & PassBox.Text & "', Age = '" & AgeBox.Text & "', JobTitle = '" & JobTBox.Text & "', Address = '" & AddressBox.Text & "', PhoneNo = '" & PhoneBox.Text & "', isAdmin = '" & CheckBox1.CheckState & "' WHERE (((Users.EmployeeID)=" & EmpBox.Text & "));"

 mycmd = New OleDbCommand(myqry, conn)

mycmd.Parameters.AddWithValue("Name", Namebox.Text)
        mycmd.Parameters.AddWithValue("Password", PassBox.Text)
        mycmd.Parameters.AddWithValue("Age", AgeBox.Text)
        mycmd.Parameters.AddWithValue("JobTitle", JobTBox.Text)
        mycmd.Parameters.AddWithValue("Address", AddressBox.Text)
        mycmd.Parameters.AddWithValue("PhoneNo", PhoneBox.Text)
        mycmd.Parameters.AddWithValue("isAdmin", CheckBox1.CheckState)
        mycmd.Parameters.AddWithValue("EmployeeID", EmpBox.Text)



      
        mycmd.ExecuteNonQuery()


        Call Set1()
      End If
    End If

    Call FillListview()
    Call ClearAlltextBox()

  End Sub
        

Answer 14

I removed the values from the SQL statement and replaced them with parameter placeholders for the Command Parameters. See if the following works:

    Dim myqry = "UPDATE Users SET Name = ?, [Password] = ?, Age = ?, JobTitle = ?, Address = ?, PhoneNo = ?, isAdmin = ? WHERE Users.EmployeeID = ?"

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 15

yes it works well..thanks a lot paul......

Answer 16

hello,

>Enmanuel, this was already suggested. Also, please to not propose your own responses as the answer.

 

What you is proposing and also as a response. Cor Ligthert it had said before if you look at the proposal of Cor Ligthert

---

Enmanuel Grullard@Developer ASP.NET

Answer 17

hello,

>Enmanuel, this was already suggested. Also, please to not propose your own responses as the answer.

 

What you is proposing and also as a response. Cor Ligthert it had said before if you look at the proposal of Cor Ligthert

---

Enmanuel Grullard@Developer ASP.NET

With all due respect, no I did not. I simply changed the code that he had so it would work.

The OP is welcome to give credit to anyone he feels helped him. It's not our job to credit ourselves. :-)

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 18

chill buddy....

Paul i have  problem here,

i trying to create auto increment for my case id with alphabet. that id will place on textbox when the form load.when the form load that id  place on textbox but i wonder whether my method are true or not..

i also got 1 button that will determine whether customer id already had in database or not. this button seems works.

the problem is when i click save/create button got syntax error from INSERT INTO statement.

mybe i missing some line or?

 

here i got so far,

 Private Sub SearchExisting_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchExisting.Click


    Dim ds As New DataSet
    Dim da As OleDbDataAdapter
    Dim sql As String
  

    sql = "(SELECT * FROM Customer WHERE CustomerID = '" & ICcusBox.Text & "')"

    da = New OleDbDataAdapter(sql, con)
    da.Fill(ds, "Customer")


    con.Close()

    CuStomerNameBox.Text = ds.Tables("Customer").Rows(0).Item(1)


  End Sub

  Private Sub NewCase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Call ConntoDB()
'declare code as string and value as int 
    Dim code As String = "CSC"
    Dim value As Integer = 1

    'Increment value by 1 on each operation
    value += 1

    ' Check the length of the integer value and prefix zeros to that
    If value.ToString().Length = 1 Then
      code = code + "00" + value.ToString()
    ElseIf value.ToString().Length = 2 Then
      code = code + "0" + value.ToString()
    ElseIf value.ToString().Length >= 3 Then
      code = code + value.ToString()
    End If
 Dim param As New OleDbParameter()
    param.ParameterName = "@CaseID"
    param.DbType = DbType.[String]
    param.Value = code
CaseIDbox.Text = (param.Value)


  End Sub
Private Sub Createbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Createbtn.Click

    
    Call ConntoDB()
    

    ''''''ADD NEW CASE'''''''
      If ICcusBox.Text = "" Or SerialBox.Text = "" Then
        MessageBox.Show("All fields Are Required", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
      Else
      '
      qry = "INSERT INTO Case (CaseID,SerialNo,Symptom,Remarks,MachineType,CustomerID)"
      qry = qry + "VALUES('" & CaseIDbox.Text & "','" & SerialBox.Text & "','" & SymptomBox.Text & "','" & RemarksBox.Text & "','" & TypeComboBox.Text & "','" & ICcusBox.Text & "')"
      Dim cmd As New OleDbCommand

    
      With cmd

        .Parameters.Add("@MachineType", OleDbType.Char, TypeComboBox.Text.Length).Value = TypeComboBox.Text.ToUpper

        .CommandText = qry
        .Connection = con
        .ExecuteNonQuery()
      End With
      MsgBox("Successful saved the information", MsgBoxStyle.Information)
    End If





  End Sub

Answer 19

It looks like you need a space between "CustomerID)" and "VALUES".

Also, use Parameters as was suggested before, just like in your UPDATE statement.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 20

Paul,

i try change it to this, but still same situation

 Dim qry = "INSERT INTO Case (CaseID,SerialNo,Symptom,Remarks,MachineType, CustomerID) VALUES('" & CaseIDbox.Text & "','" & SerialBox.Text & "','" & SymptomBox.Text & "','" & RemarksBox.Text & "','" & TypeComboBox.Text & "','" & ICcusBox.Text & "')"

      cmd = New OleDbCommand

    
      With cmd

        .Parameters.AddWithValue("CaseID", CaseIDbox.Text)
        .Parameters.AddWithValue("SerialNo", SerialBox.Text)
        .Parameters.AddWithValue("Symptom", SymptomBox.Text)
        .Parameters.AddWithValue("Remarks", RemarksBox.Text)
        .Parameters.Add("@MachineType", OleDbType.Char, TypeComboBox.Text.Length).Value = TypeComboBox.Text.ToUpper
        .Parameters.AddWithValue("CustomerID", ICcusBox.Text)

        .CommandText = qry
        .Connection = con
        .ExecuteNonQuery()
      End With
      MsgBox("Successful saved the information", MsgBoxStyle.Information)
    End If

Answer 21

Hi,

Adjusting the code aver.

 

 Using con As New OleDbConnection("CONECTION")

            Dim SQL = "@INSERT INTO Case (CaseID,SerialNo,Symptom,Remarks,MachineType, CustomerID) VALUES(@CaseID,@SerialNo,@Symptom,@Remarks,@MachineType,CustomerID)"

            Dim cmd As New OleDbCommand(SQL, con)

 

            Try

                With cmd

 

                    .Parameters.AddWithValue("@CaseID", CaseIDbox.Text)

                    .Parameters.AddWithValue("@SerialNo", SerialBox.Text)

                    .Parameters.AddWithValue("@Symptom", SymptomBox.Text)

                    .Parameters.AddWithValue("@Remarks", RemarksBox.Text)

                    .Parameters.AddWithValue("@MachineType", ypeComboBox.Text.ToUpper)

                    .Parameters.AddWithValue("@CustomerID", ICcusBox.Text)

                    .CommandText = SQL

                    .Connection = con

                    .ExecuteNonQuery()

 

 

                End With

 

                MsgBox("Successful saved the information", MsgBoxStyle.Information)

 

            Catch ex As Exception

 

            End Try

 

        End Using

---

Enmanuel Grullard@Developer ASP.NET

Answer 22

You can use Enmanuel's example (that should be @CustomerID in the SQL statement) or use question marks as parameter placeholders (as in the UPDATE example SQL statement).

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 23

ok no more error, but data wont save in access...should i make relationship between this 2 table?

table Case & Customer

Answer 24

Maybe you could post the code in question so we can double check it.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 25

ok paul, here we go

there is no more syntax error problem...

but data not saved in database, when i click save button

i also already made relationship  between Case & Customer table....

full code for this form;

Imports System.Data.OleDb
Public Class NewCase

  Dim con As New OleDbConnection

  Dim cmd As OleDbCommand
  Dim str As String = Nothing
  Dim SQL As String


  Private Sub NewCase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

  End Sub

  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewCusbtn.Click
    New_customer.Show()
  End Sub

  Private Sub SearchExisting_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchExisting.Click
    Call ConntoDB()

    Dim ds As New DataSet
    Dim da As OleDbDataAdapter
    Dim sql As String


    sql = "(SELECT * FROM Customer WHERE CustomerID = '" & ICcusBox.Text & "')"

    da = New OleDbDataAdapter(sql, con)
    da.Fill(ds, "Customer")


    con.Close()

    CuStomerNameBox.Text = ds.Tables("Customer").Rows(0).Item(1)


  End Sub

  Sub ConntoDB()
    Try
      With con
        If .State = ConnectionState.Open Then .Close()
        .ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ComputerCentre.accdb")
        .Open()
      End With
    Catch ex As Exception
      MessageBox.Show("Unable to connect", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
      Application.Exit()
    End Try
  End Sub

  Private Sub Createbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Createbtn.Click
    Call ConntoDB()
    ''''''ADD NEW CASE'''''''

 Dim str = "INSERT INTO Case (CaseID,SerialNo,Symptom,Remarks,MachineType,CustomerID) VALUES(CaseID=?,SerialNo=?,Symptom=?,Remarks=?,MachineType=?,CustomerID=?)"

    Dim cmd As New OleDbCommand(str, con)
 Try
      With cmd

        .Parameters.AddWithValue("CaseID", CaseIDbox.Text)
        .Parameters.AddWithValue("SerialNo", SerialBox.Text)
        .Parameters.AddWithValue("Symptom", SymptomBox.Text)
        .Parameters.AddWithValue("Remarks", RemarksBox.Text)
        .Parameters.AddWithValue("@MachineType", TypeComboBox.Text.ToUpper)
    
 .Parameters.AddWithValue("CustomerID", ICcusBox.Text)

        .CommandText = SQL
        .Connection = con
        .ExecuteNonQuery()
      End With
      MsgBox("Successfull saved the information", MsgBoxStyle.Information)

    Catch ex As Exception

    End Try
    con.Close()

  End Sub
End Class

Answer 26

All that you need are the question mark placeholders in the query:

 
Dim str = "INSERT INTO Case (CaseID,SerialNo,Symptom,Remarks,MachineType,CustomerID) VALUES(?,?,?,?,?,?)"

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 27

Dear paul,

 

thanks you so much....

and another question for u paul:

i dont know how to make auto increment id...

i got write some code, but seems doesnt work....mybe got other method/way that i still dont know?

here the code:

 

Private Sub NewCase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Call ConntoDB()
    Dim Caseno As String
    Caseno = "SELECT MAX(CaseID) FROM Cases WHERE CaseID LIKE CSC0 """
    cmd = New OleDbCommand(SQL, con)
   
    Caseno = cmd.ExecuteScalar

    If Caseno Is DBNull.Value Then
      Caseno = "00001"
    Else
      Caseno = Caseno + 1
    End If
    Caseno = "CSC0" + Caseno
 CaseIDbox.Text = (Caseno)



  End Sub

Thanks in advance!

 

Answer 28

Sr
  
Why not make another question and this was answered, another question If you make below that and can bring Confucianism was answered when the user want to find an answer to a problem.

I hope you take that observation as to when you ask another question.

---

Enmanuel Grullard@Developer ASP.NET

Answer 29

Enmanuel,

okay i already solved this problem with this following code:

Dim SQL, Caseno As String

        SQL = "SELECT MAX(CaseID) FROM Cases "
        cmd = New OleDbCommand(SQL, con)

        Caseno = cmd.ExecuteScalar()

        If Caseno Is DBNull.Value Then
            Caseno = "0001"
        Else
            Caseno = CInt(Replace(Caseno, "CSC", "")) + 1
        End If
        Caseno = "CSC0" + Caseno
        CaseIDbox.Text = (Caseno)

 

thanks to all,