5 Replies - 2497 Views - Last Post: 23 April 2012 - 06:48 AM Rate Topic: -----

#1 vikxit  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 23-April 12

cmd.ExecuteNonQuery() Error when updating to database

Posted 23 April 2012 - 01:10 AM

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


Attached Image

Is This A Good Question/Topic? 0
  • +

Replies To: cmd.ExecuteNonQuery() Error when updating to database

#2 CharlieMay  Icon User is online

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,918
  • Joined: 25-September 09

Re: cmd.ExecuteNonQuery() Error when updating to database

Posted 23 April 2012 - 04:25 AM

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

Repeat this all the way down the line
Was This Post Helpful? 0
  • +
  • -

#3 vikxit  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 23-April 12

Re: cmd.ExecuteNonQuery() Error when updating to database

Posted 23 April 2012 - 05:27 AM

View PostCharlieMay, 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"
Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay  Icon User is online

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,918
  • Joined: 25-September 09

Re: cmd.ExecuteNonQuery() Error when updating to database

Posted 23 April 2012 - 05:32 AM

Let's see the block of code where you made your corrections
Was This Post Helpful? 0
  • +
  • -

#5 vikxit  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 23-April 12

Re: cmd.ExecuteNonQuery() Error when updating to database

Posted 23 April 2012 - 06:33 AM

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

Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is online

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,918
  • Joined: 25-September 09

Re: cmd.ExecuteNonQuery() Error when updating to database

Posted 23 April 2012 - 06:48 AM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1