Syntax error in UPDATE statement

[tukky]

Im working on a project for college. The form im working on allows the user to edit the details of a customer. I have the following code attached to the EDIT button

EditCustomerTable.Clear()
            EditCustomerConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=QuickQuotes.mdb"
            EditCustomerConnection.Open()
            EditCustomerAdapter = New OleDb.OleDbDataAdapter
            Dim SQL As String = "UPDATE Customers SET [First Name] = '" & txtCustomerFirstName.Text & "'," _
                  & "[Last Name] = '" & txtCustomerLastName.Text & "'," _
                  & "[Street] = '" & txtCustomerStreet.Text & "'," _
                  & "[City] = '" & txtCustomerCity.Text & "'," _
                  & "[County] '" & txtCustomerCounty.Text & "'," _
                  & "[Postcode] = '" & txtCustomerPostcode.Text & "'," _
                  & "[E-mail] = '" & txtCustomerEmailAdd.Text & "' " _
                  & "[PhoneNumber] '" & txtCustomerTele.Text & "'," _
                  & "WHERE ID = " & txtCustomerID.Text & ""
            EditCustomerAdapter = New OleDb.OleDbDataAdapter(SQL, EditCustomerConnection)
            CommandBuilder = New OleDb.OleDbCommandBuilder(EditCustomerAdapter)
            EditCustomerAdapter.Fill(EditCustomerTable)
            EditCustomerConnection.Close()

but when i click the button the program crashes and says that there is a Syntax error in the UPDATE statement. I cant seem to find any errors. I was wondering if someone could help please.

[Momerath]

Are there any ' characters in any of the values you are trying to save? The method you are using is opening your system up to a SQL Injection Attack. You should consider using Parameters

[Mike Bishop]

try taking out the last , from the line below
"[PhoneNumber] '" & txtCustomerTele.Text & "'," _

[tukky]

try taking out the last , from the line below
"[PhoneNumber] '" & txtCustomerTele.Text & "'," _

It still doesnt seem to work. I keep getting the same error.

[Mike Bishop]

quickquotes.mdb... you dont work for a company called Leyland do you?
do you have any code that does a update?

[tukky]

No i dont work this is for my college project :)
this is my First year coursework (im retaking it) i have my 2nd year coursework which i got an A for and i have an update statement in there which works fine heres the code for that:

'If any of the textboxes are empty then a message box tells the user to make sure the textboxes are completed.
        If txtEditAddress1.Text = "" Or _
        txtEditAddress2.Text = "" Or _
        txtEditFirstName.Text = "" Or _
        txtEditLastName.Text = "" Or _
        txtEditPhoneNumber.Text = "" Or _
        txtEditPostcode.Text = "" Then
            MsgBox("Please ensure no fields are left empty")
        Else
            'updates the database with the changes made to the customers details. 
            EditCustomerTable.Clear()
            EditCustomerConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Shop.mdb"
            EditCustomerConnection.Open()
            EditCustomerAdapter = New OleDb.OleDbDataAdapter
            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)
            CommandBuilder = New OleDb.OleDbCommandBuilder(EditCustomerAdapter)
            EditCustomerAdapter.Fill(EditCustomerTable)
            EditCustomerConnection.Close()
        End If

Its basically the exact same code. I just copy pasted it across and just added a few more fields to the sql statement but i still keep getting the same error.

[Mike Bishop]

here is your code that is working (last two lines of the update query)
& "[Phone Number] = '" & txtEditPhoneNumber.Text & "' " _ & "WHERE ID = " & txtCustomerID.Text & ""
note that there is no after the & "' " & where ID=

here is your code that isn't working (same two lines)
& "[PhoneNumber] '" & txtCustomerTele.Text & "'," _
& "WHERE ID = " & txtCustomerID.Text & ""

Note that there is a comma i think this is where the error is

[tukky]

[IMG]http://i51.tinypic.com/2h2qofc.png[/IMG]

thats what happened. Note that i deleted the comma but it still doesnt seem to work :(

[Momerath]

Missing an equal sign after [PhoneNumber] in your picture. And County.

[vanzhyme]

& "[County] '" & txtCustomerCounty.Text & "'," _
                  & "[Postcode] = '" & txtCustomerPostcode.Text & "'," _
                  & "[E-mail] = '" & txtCustomerEmailAdd.Text & "' " _
                  & "[PhoneNumber] '" & txtCustomerTele.Text & "'," _

Your code here in County and Phonenumber. There is a missing equal sign. Tyr to
add it. Hope it works.

[tukky]

[IMG]http://i51.tinypic.com/23k21rt.png[/IMG]

ive done what you said but now i keep getting the error shown above.