0

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

2
Contributors
9
Replies
10
Views
6 Years
Discussion Span
Last Post by Oxiegen
0

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'

0

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 by tukky: n/a

0

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

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

0

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

0

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

0

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.
Be sure to adhere to our posting rules.