hey guys im currently working on a project for college. (USING VB)
wat the form im working on does, allows the user to enter an ID of a customer and this then displays the customers details the user can then change the details however they want and update them which then saves the changes into a database. But i keep getting a syntax error in the following update statement.

EditCustomerTable.Clear()
        EditCustomerConnection.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=G:\Shop.mdb"
        EditCustomerConnection.Open()
        EditCustomerAdapter = New OleDb.OleDbDataAdapter("UPDATE Customers SET First Name = '" & txtEditFirstName.Text & _
            "Last Name = " & txtEditLastName.Text & _
            "Address 1 = " & txtEditAddress1.Text & _
            "Address 2 = " & txtEditAddress2.Text & _
            "Postcode = " & txtEditPostcode.Text & _
            "Phone Number = " & txtEditPhoneNumber.Text & _
"WHERE ID = " & txtCustomerID.Text & "'", EditCustomerConnection)
        CommandBuilder = New OleDb.OleDbCommandBuilder(EditCustomerAdapter)
        EditCustomerAdapter.Fill(EditCustomerTable)
        EditCustomerConnection.Close()

i dont know where im going wrong in that update statement,Can you please help me
thanks

You need to encase All values in it's own single-quote and separate each column->value with a comma. UPDATE table SET col1 = 'value1', col2 = 'value2' WHERE col3 = 'value3'

can u give an example please with my code. because i dont understand what you mean
sorry.

it gave me a syntax error but then when i did what you said it gave me an error saying:

Syntax error (missing operator) in query expression

Edited 6 Years Ago by tukky: n/a

Note the blank space at the end of line 6.

EditCustomerAdapter = New OleDb.OleDbDataAdapter("UPDATE Customers SET [First Name] = '" & txtEditFirstName.Text & "'," _
            "[Last Name] = '" & txtEditLastName.Text & "'," _
            "[Address 1] = '" & txtEditAddress1.Text & "'," _
            "[Address 2] = '" & txtEditAddress2.Text & "'," _
            "Postcode = '" & txtEditPostcode.Text & "'," _
            "[Phone Number] = '" & txtEditPhoneNumber.Text & "' " _
"WHERE ID = '" & txtCustomerID.Text & "'", EditCustomerConnection)

Note the blank space at the end of line 6.

EditCustomerAdapter = New OleDb.OleDbDataAdapter("UPDATE Customers SET [First Name] = '" & txtEditFirstName.Text & "'," _
            "[Last Name] = '" & txtEditLastName.Text & "'," _
            "[Address 1] = '" & txtEditAddress1.Text & "'," _
            "[Address 2] = '" & txtEditAddress2.Text & "'," _
            "Postcode = '" & txtEditPostcode.Text & "'," _
            "[Phone Number] = '" & txtEditPhoneNumber.Text & "' " _
"WHERE ID = '" & txtCustomerID.Text & "'", EditCustomerConnection)

i entered that code exactly how it was and it says "Overload resolution failed because no accessible 'New' accepts this number of arguments

Try again. Make sure that the commas are within the quotes, except the last one in front of EditCustomerConnection.

i copy pasted the code to make sure it was correct and i still get the same error =[

Weird. Let's try this.

Dim SQL As String = "UPDATE Customers SET [First Name] = '" & txtEditFirstName.Text & "'," _
& "[Last Name] = '" & txtEditLastName.Text & "'," _
& "[Address 1] = '" & txtEditAddress1.Text & "'," _
& "[Address 2] = '" & txtEditAddress2.Text & "'," _
& "Postcode = '" & txtEditPostcode.Text & "'," _
& "[Phone Number] = '" & txtEditPhoneNumber.Text & "' " _
& "WHERE ID = '" & txtCustomerID.Text & "'"

EditCustomerAdapter = New OleDb.OleDbDataAdapter(SQL, EditCustomerConnection)
This article has been dead for over six months. Start a new discussion instead.