CODEWITHSUNDEEP
sql-servervb.net
Henry Gathigira
Henry Gathigira

Reputation: 305

How to solve incorrect syntax in sql

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?

Upvotes: 0

Views: 66

Answers (1)

marc_s
marc_s

Reputation: 754488

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)

Upvotes: 1

Related Questions