How to solve incorrect syntax in sql

Question

I am using this code

cn.Open()

Using cmd As New SqlClient.SqlCommand("INSERT INTO Students(AdmissionNumber, FullName, DOBirth, Class_Stream, DateAdmitted, Gender, County, KCPEYear, KCPEIndex, Phone1, Phone2, PostalAddress, PostalCode, Town, EmailAddress, AnyOtherInformation, StudentPhoto) VALUES ('" & Tbx12.Text & "','" & Tbx30.Text & "','" & Tbx4.Text & "','" & Tbx31.Text & "','" & Tbx13.Text & "','" & Cbx1.SelectedItem & "','" & Cbx2.SelectedItem & "','" & Cbx4.SelectedItem & "','" & Tbx15.Text & "','" & Tbx6.Text & "','" & Tbx7.Text & "','" & Tbx8.Text & "','" & Tbx9.Text & "','" & Tbx10.Text & "','" & Tbx11.Text & "','" & Rtbx1.Text & "',@StudentPhoto)", cn)
    cmd.Parameters.Add("@AdmissionNumber", SqlDbType.NVarChar).Value = Tbx12.Text
    cmd.Parameters.Add("@FullName", SqlDbType.NVarChar).Value = Tbx30.Text
    cmd.Parameters.Add("@DOBirth", SqlDbType.NVarChar).Value = Tbx4.Text
    cmd.Parameters.Add("@Class_Stream", SqlDbType.NVarChar).Value = Tbx31.Text
    cmd.Parameters.Add("@DateAdmitted", SqlDbType.NVarChar).Value = Tbx13.Text
    cmd.Parameters.Add("@Gender", SqlDbType.NVarChar).Value = Cbx1.Text
    cmd.Parameters.Add("@County", SqlDbType.NVarChar).Value = Cbx2.Text
    cmd.Parameters.Add("@KCPEYear", SqlDbType.NVarChar).Value = Cbx4.Text
    cmd.Parameters.Add("@KCPEIndex", SqlDbType.NVarChar).Value = Tbx15.Text
    cmd.Parameters.Add("@Phone1", SqlDbType.NVarChar).Value = Tbx6.Text
    cmd.Parameters.Add("@Phone2", SqlDbType.NVarChar).Value = Tbx7.Text
    cmd.Parameters.Add("@PostalAddress", SqlDbType.NVarChar).Value = Tbx8.Text
    cmd.Parameters.Add("@PostalCode", SqlDbType.NVarChar).Value = Tbx9.Text
    cmd.Parameters.Add("@Town", SqlDbType.NVarChar).Value = Tbx10.Text
    cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar).Value = Tbx11.Text
    cmd.Parameters.Add("AnyOtherInformation", SqlDbType.NVarChar).Value = Rtbx1.Text
    cmd.Parameters.Add(New SqlClient.SqlParameter("@StudentPhoto", SqlDbType.Image)).Value = IO.File.ReadAllBytes(a.FileName)

    i = cmd.ExecuteNonQuery
End Using

to save data to SQL Server. I am suspecting that the error I got is because at Textbox30, Combobox2 and Textbox10 can contain User Inputs with Apostrophes. Eg. Murang'a, Ndung'u, How will I solve this?

You are actually not implementing it right. Look up a tutorial of Prepared Statements — juergen d, May 16 '16 at 12:15
Check out [this other SO question (and its answers!)](http://stackoverflow.com/questions/7351135/prepared-statements-in-vb-net) for a first glimpse. You're using *parameters* - which is good - but you don't use them in your `INSERT` statement! (which is horribly bad) — marc_s, May 16 '16 at 12:23
@Replace the string concatenation in the values clause with parameter names: `VALUES(@AdmissionNumber, @FullName,...)`. The actual values used for the `INSERT` will be the parameter values. — Dan Guzman, May 16 '16 at 12:25
There is nothing wrong with parameters in an insert statement. — Kramb, May 16 '16 at 12:31
You really should set the length of your varchar parameters. And you seem to be using the image datatype. It has been deprecated since 2005 in favor of varbinary(max). — Sean Lange, May 16 '16 at 13:24

score 1 · Accepted Answer · edited May 16 '16 at 14:31

Basically, your problem is that you concatenate together your INSERT statement (which is a horribly bad practice - opens the doors for SQL injection attacks - still the #1 attacks out on the web!), while in the statements below, you're actually adding parameters (which aren't present in your INSERT query above.....).

So change your code to this:

cn.Open()

' use INSERT statement *WITH* parameters!
Dim insertQry as string = 
    "INSERT INTO Students(AdmissionNumber, FullName, DOBirth, Class_Stream, DateAdmitted, Gender, County, " &
                         "KCPEYear, KCPEIndex, Phone1, Phone2, PostalAddress, PostalCode, Town, EmailAddress, AnyOtherInformation, StudentPhoto) " &
    "VALUES (@AdmissionNumber, @FullName, @DOBirth, @Class_Stream, @DateAdmitted, @Gender, @County, " &
                         "@KCPEYear, @KCPEIndex, @Phone1, @Phone2, @PostalAddress, @PostalCode, @Town, " &
                         "@EmailAddress, @AnyOtherInformation, @StudentPhoto);";

Using cmd As New SqlClient.SqlCommand(insertQry, cn)
    cmd.Parameters.Add("@AdmissionNumber", SqlDbType.NVarChar).Value = Tbx12.Text
    cmd.Parameters.Add("@FullName", SqlDbType.NVarChar).Value = Tbx30.Text
    cmd.Parameters.Add("@DOBirth", SqlDbType.NVarChar).Value = Tbx4.Text
    cmd.Parameters.Add("@Class_Stream", SqlDbType.NVarChar).Value = Tbx31.Text
    cmd.Parameters.Add("@DateAdmitted", SqlDbType.NVarChar).Value = Tbx13.Text
    cmd.Parameters.Add("@Gender", SqlDbType.NVarChar).Value = Cbx1.Text
    cmd.Parameters.Add("@County", SqlDbType.NVarChar).Value = Cbx2.Text
    cmd.Parameters.Add("@KCPEYear", SqlDbType.NVarChar).Value = Cbx4.Text
    cmd.Parameters.Add("@KCPEIndex", SqlDbType.NVarChar).Value = Tbx15.Text
    cmd.Parameters.Add("@Phone1", SqlDbType.NVarChar).Value = Tbx6.Text
    cmd.Parameters.Add("@Phone2", SqlDbType.NVarChar).Value = Tbx7.Text
    cmd.Parameters.Add("@PostalAddress", SqlDbType.NVarChar).Value = Tbx8.Text
    cmd.Parameters.Add("@PostalCode", SqlDbType.NVarChar).Value = Tbx9.Text
    cmd.Parameters.Add("@Town", SqlDbType.NVarChar).Value = Tbx10.Text
    cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar).Value = Tbx11.Text
    cmd.Parameters.Add("@AnyOtherInformation", SqlDbType.NVarChar).Value = Rtbx1.Text
    cmd.Parameters.Add("@StudentPhoto", SqlDbType.Image).Value = IO.File.ReadAllBytes(a.FileName)

    i = cmd.ExecuteNonQuery
End Using

and then you should be fine - safely protected from SQL injection attacks, handling names with apostrophes quite nicely, and it'll be faster, too! (if you execute the same insert more than once)

Note this row: `cmd.Parameters.Add("AnyOtherInformation", SqlDbType.NVarChar).Value = Rtbx1.Text` is missing a `@` in the parameter name definition. — Zohar Peled, May 16 '16 at 13:21
np. didn't see your answer when I posted mine, only after posting it. since it's basically the same I'm deleting mine and upvoting yours. — Zohar Peled, May 16 '16 at 13:36
Zohar av noted the missing @. Av also noted your gentleman action to Marc_S. @ Marc_s, Thumbs up. — Henry Gathigira, May 16 '16 at 13:39
Also a slight syntax error at @StudentPhoto);"; Should it be @StudentPhoto)" — Henry Gathigira, May 16 '16 at 13:41
@HenryGathigira: actually, it's *good practice* to end your SQL statements with a `;` - so that semicolon is there *on purpose*, and it *should be there*, too! — marc_s, May 16 '16 at 13:42

How to solve incorrect syntax in sql

1 Answers1