SQL Update Syntax Error

[dnomde44]

I am not really sure where my syntax error is. I have gone over it several times and it appears everything is right and I dont think I am using any reserved words. Any help would be greatly appreciated. Every box is TEXT in access for now even fields that would normally be numbers.

Thanks in advance!!!!

Con.Open()
                Dim cmd2 As New OleDb.OleDbCommand("UPDATE Customer SET MemName=@a0, StreetName=@a1, Gang=@a2, RoleInGang=@a3, SSNum=@a4, AKA=@a5," _
                                                   & "Gender=@a6, DOB=@a7, Height=@a8, Weight=@a9, Eyes=@a10, Race=@a11, SID=@a12, FBI=@a13, Prob=@a14, Marks=@15," _
                                                   & "Permit=@a16, Gun=@a17, CarryWOLic=@18, VehicleOne=@a19, VehicleTwo=@a20, VehicleThree=@a21," _
                                                   & "LiscenseAct=@a22, DLNumber=@a23, AddressOne=@a24, AddressTwo=@a25, AddressThree=@a26, AddressFour=@a27," _
                                                   & "AddressFive=@a28, AssocOne=@a29, AssocTwo=@a30, AssocThree=@a31, AssocFour=@a32, AssocFive=@a33," _
                                                   & "AdditionalInfo=@a34, WHERE (ID=@a35), ", Con)

                cmd2.Parameters.AddWithValue("@a0", NameBox.Text)
                cmd2.Parameters.AddWithValue("@a1", StnameBox.Text)
                cmd2.Parameters.AddWithValue("@a2", GangBox.Text)
                cmd2.Parameters.AddWithValue("@a3", RoleInGangBox.Text)
                cmd2.Parameters.AddWithValue("@a4", SSNumBox.Text)
                cmd2.Parameters.AddWithValue("@a5", AKABox.Text)
                cmd2.Parameters.AddWithValue("@a6", GenderBox.Text)
                cmd2.Parameters.AddWithValue("@a7", DOBBox.Text)
                cmd2.Parameters.AddWithValue("@a8", HeightBox.Text)
                cmd2.Parameters.AddWithValue("@a9", WeightBox.Text)
                cmd2.Parameters.AddWithValue("@a10", EyesBox.Text)
                cmd2.Parameters.AddWithValue("@a11", RaceBox.Text)
                cmd2.Parameters.AddWithValue("@a12", SIDBox.Text)
                cmd2.Parameters.AddWithValue("@a13", FBIBox.Text)
                cmd2.Parameters.AddWithValue("@a14", ProbBox.Text)
                cmd2.Parameters.AddWithValue("@a15", ScarsBox.Text)
                cmd2.Parameters.AddWithValue("@a16", PermitBox.Text)
                cmd2.Parameters.AddWithValue("@a17", GunBox.Text)
                cmd2.Parameters.AddWithValue("@a18", WOPermitBox.Text)
                cmd2.Parameters.AddWithValue("@a19", VehicleOneBox.Text)
                cmd2.Parameters.AddWithValue("@a20", VehicleTwoBox.Text)
                cmd2.Parameters.AddWithValue("@a21", VehicleThreeBox.Text)
                cmd2.Parameters.AddWithValue("@a22", DLBox.Text)
                cmd2.Parameters.AddWithValue("@a23", DLNumBox.Text)
                cmd2.Parameters.AddWithValue("@a24", AddressOneBox.Text)
                cmd2.Parameters.AddWithValue("@a25", AddressTwoBox.Text)
                cmd2.Parameters.AddWithValue("@a26", AddressThreeBox.Text)
                cmd2.Parameters.AddWithValue("@a27", AddressFourBox.Text)
                cmd2.Parameters.AddWithValue("@a28", AddressFiveBox.Text)
                cmd2.Parameters.AddWithValue("@a29", AssocOneBox.Text)
                cmd2.Parameters.AddWithValue("@a30", AssocTwoBox.Text)
                cmd2.Parameters.AddWithValue("@a31", AssocThreeBox.Text)
                cmd2.Parameters.AddWithValue("@a32", AssocFourBox.Text)
                cmd2.Parameters.AddWithValue("@a33", AssocFiveBox.Text)
                cmd2.Parameters.AddWithValue("@a34", AddInfoBox.Text)
                'cmd2.Parameters.AddWithValue("@a35", ID)

                cmd2.ExecuteNonQuery()

                MsgBox("Record Updated Successfully")
                EDITMODE = False ' Add success, end EDITMODE
                ClearTextBoxes() 'Add Success, clean up textboxes
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                Con.Close()
            End Try

[svilla]

There is a comma before and after the WHERE clause. The last SET does not need a comma and ommma at the end will also cause an error. The last line of the SQL sould look like this

& "AdditionalInfo=@a34 WHERE (ID=@a35)", Con)

[ndeniche]

Also, shouldn't you specify what king of db variable you are adding with values? The way you're doing it, you are going to need some single quotes in the text fields.

[kingsonprisonic]

Why you commenting this line.

'cmd2.Parameters.AddWithValue("@a35", ID)

[dnomde44]

Svilla

Thank you that was exactly what was holding my entire code up. After looking over and over I didnt realize that the extra comma would throw it off.

Nichito

What do you mean? I have everything right now as a text field so it should take whatever I send it correct? If I change it to a number field in Access then I would have to start adding single quotes. Is that correct o am I completely off?

Kingsonprisonic

I forgot to uncomment it. I tried without that line becuase I thought maybe trying to update an autonumber was causing it to fail for some reason.

[dnomde44]

There is a comma before and after the WHERE clause. The last SET does not need a comma and ommma at the end will also cause an error. The last line of the SQL sould look like this

& "AdditionalInfo=@a34 WHERE (ID=@a35)", Con)

this solved my problem. Thanks again!!