UPDATE SQL statement help!!!

[Stefan29]

I need some help with an UPDATE SQL statement. I keep getting a syntax error and can't find the problem.

Would appreciate any help!

dim sSaveSql as string

(CustID, LastName, etc are the field names in the Access Database)

sSaveSql = "UPDATE Customer SET " _
& "CustID = """ & oThisFormA.txtCustID.Text.Trim & """, " _
& "LastName = """ & oThisFormA.txtCustLast.Text.Trim & """, " _
& "FirstName = """ & oThisFormA.txtCustFirst.Text.Trim & """, " _
& "DateOfBirth = """ & oThisFormA.txtCustDOB.Text.Trim & """, " _
& "PhoneHome = """ & oThisFormA.txtCustPhoneHome.Text.Trim & """, " _
& "Address = """ & oThisFormA.txtCustAdd.Text.Trim & """, " _
& "Suburb = """ & oThisFormA.txtCustSub.Text.Trim & """, " _
& "State = """ & oThisFormA.txtCustState.Text.Trim & """, " _
& "Postcode = """ & oThisFormA.txtCustPostcode.Text.Trim & """, " _
& "PhoneMobile = """ & oThisFormA.txtCustMobile.Text.Trim & """, " _
& "WHERE CustID = """ & oThisFormA.txtCustID.Text.Trim & """;"

 

[InertiaM]

I can see the error, but you REALLY need to learn about parameterised queries (see link in my signature). It will make reading your SQL statements so much easier and also eliminate errors such as this one.

 

[Stefan29]

I can see the error, but you REALLY need to learn about parameterised queries (see link in my signature). It will make reading your SQL statements so much easier and also eliminate errors such as this one.

Thank you i appreciate that link, however this is a University Project I am doing and the subject requires to do it the way I have shown (although id like to try the parameterised way). Would you be able to tell me the error in the SQL so I can fix it? Thanks!

 

[InertiaM]

That's the way they are asking you to do it on a University course

Heaven help us

 

[Stefan29]

That's the way they are asking you to do it on a University course

Heaven help us

Yes, my course only touches the basics of programming, we dont go in depth about it, thank god!

 

[kulrom]

I assumed that ONLY CustID column is numeric datatype (int and such).
However when you deal with numeric field you use only double quotes e.g.
" & value & "

Otherwise you add an extra single quote around e.g.
'" & value & "'

        sSaveSql = "UPDATE Customer SET " & _
        "CustID = " & txtCustID.Text.Trim & ", " & _
        "LastName = '" & txtCustLast.Text.Trim & "', " & _
        "FirstName = '" & txtCustFirst.Text.Trim & "', " & _
        "DateOfBirth = '" & txtCustDOB.Text.Trim & "', " & _
        "PhoneHome = '" & txtCustPhoneHome.Text.Trim & "', " & _
        "Address = '" & txtCustAdd.Text.Trim & "', " & _
        "Suburb = '" & txtCustSub.Text.Trim & "', " & _
        "State = '" & txtCustState.Text.Trim & "', " & _
        "Postcode = '" & txtCustPostcode.Text.Trim & "', " & _
        "PhoneMobile = '" & txtCustMobile.Text.Trim & "', " & _
        "WHERE CustID = " & txtCustID.Text.Trim & ""

 

[InertiaM]

Yes, my course only touches the basics of programming, we dont go in depth about it, thank god!

But by teaching you that way, they are allowing SQL injection attacks

Anyway, try the following :-

Messagebox.Show (sSaveSql)

and see if you can spot the additional comma.

 

[Nancy08]

When it comes to learning basic SQL, w3school is a best tutorial for you. It contains basic lessons such as how to use SQL to access and manipulate data in MySQL, SQL Server, MS Access, Oracle, Sybase, DB2, and other database systems.

I think, it is really helpful for all students.