[vikxit]

Hello, I'm trying to update a record in MS Access database with the code below. But it says 'Syntax error in UPDATE statement and highlights cmd.ExecuteNonQuery()'. Can anyone help on this?

Here's the code:

 dbconnect.Open()

        Dim cmd As New OleDb.OleDbCommand
        cmd.Connection = dbconnect

        'chek whether add new or update
        If Me.txtCustomerID.Tag & "" = "" Then
            'add new
            'add data to table
            cmd.CommandText = "INSERT INTO tblCustomer(Customer_ID, First_Name, Last_Name, Gender, Telephone_No, Address, Line_1, Line_2, Email_Add, Registered_Date)" & _
                              "VALUES(" & Me.txtCustomerID.Text & ",'" & Me.txtFirstName.Text & "','" & Me.txtLastName.Text & "','" & Me.comboGender.Text & "','" & Me.txtTelNo.Text & "','" & Me.txtAddress.Text & "','" & _
                              Me.txtLine1.Text & "','" & Me.txtLine2.Text & "','" & Me.txtEmailAdd.Text & "',#" & Me.dtpDate.Text & "#)"
            cmd.ExecuteNonQuery()

        Else
            'update data in table
            cmd.CommandText = "UPDATE tblCustomer" & _
            "SET Customer_ID=" & Me.txtCustomerID.Text & _
            ", First_Name'" & Me.txtFirstName.Text & "'" & _
            ", Last_Name'" & Me.txtLastName.Text & "'" & _
            ", Gender'" & Me.comboGender.Text & "'" & _
            ", Telephone_No'" & Me.txtTelNo.Text & "'" & _
            ", Address" & Me.txtAddress.Text & "'" & _
            ", Line_1'" & Me.txtLine1.Text & "'" & _
            ", Line_2'" & Me.txtLine2.Text & "'" & _
            ", Email_Add'" & Me.txtEmailAdd.Text & "'" & _
            ", Registered_Date'" & Me.dtpDate.Text & "'" & _
            "WHERE Customer_ID=" & Me.txtCustomerID.Tag
            cmd.ExecuteNonQuery()
        End If
        dbconnect.Close()

[CharlieMay]

You're missing a bunch of = signs.
", First_Name = '" & Me.txtFirstName.Text & "'" & _

Repeat this all the way down the line

[vikxit]

CharlieMay, on 23 April 2012 - 04:25 AM, said:

You're missing a bunch of = signs.
", First_Name = '" & Me.txtFirstName.Text & "'" & _

Repeat this all the way down the line

Hello, I tried the '=' sign but the problem is the same "Syntax error in UPDATE statement"

[CharlieMay]

Let's see the block of code where you made your corrections

[vikxit]

i've been able to correct the code.
Also, the "=" sign is important in the code.

There was also a "data type mismatch in criteria expression", then I removed the ' where are integers or other numeric type.

here's the part of the correct code.

'update data in table
            cmd.CommandText = "UPDATE tblCustomer SET [Customer_ID] = '" & Me.txtCustomerID.Text & "'," _
                & "[First_Name] ='" & Me.txtFirstName.Text & "'," _
                & "[Last_Name] ='" & Me.txtLastName.Text & "'," _
                & "[Gender] ='" & Me.comboGender.Text & "'," _
                & "[Telephone_No] =" & Me.txtTelNo.Text & "," _
                & "[Address] ='" & Me.txtAddress.Text & "'," _
                & "[Line_1] ='" & Me.txtLine1.Text & "'," _
                & "[Line_2] ='" & Me.txtLine2.Text & "'," _
                & "[Email_Add] ='" & Me.txtEmailAdd.Text & "'," _
                & "[Registered_Date] =#" & Me.dtpDate.Text & "#" _
                & "WHERE [Customer_ID] = " & Me.txtCustomerID.Text & ""
            cmd.ExecuteNonQuery()

[CharlieMay]

OK, if you enter the telephone with - ie, 999-555-1212 that needs to be a text field and have the surrounding '

I'm also going to point out that Registered_Date needs to be a date/time type in your database. I assume it is as you know about wrapping it with # but if have it set as a text type in the database, the # will not work. So keep that in mind.

Other than that, nothing else sticks out.