incorrect syntax near '21'

Question

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim cmd As New SqlCommand("insert into accountant(acc_id,acc_name,payment_type,bill_no) values ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "'", cn)
    cn.Open()
    cmd.ExecuteNonQuery()
    cn.Close()

    MsgBox("Accountant data inserted SucsessFully")
    TextBox1.Clear()
    TextBox2.Clear()
    TextBox3.Clear()
    TextBox4.Clear()

    accouantant_Load(sender, e)
End Sub

And probably a single quote in your textboxes. The medicine is always the same: USE PARAMETERS. — Steve, Nov 30 '12 at 20:41

score 2 · Accepted Answer · edited May 23 '17 at 12:04

Probably you have a single quote in your textboxes.
The solution is to use parameters to build your sql string.
If you use parameters then you are free from parsing problems on strings, date or other fields, but, the uttermost importance of parameters is the prevention of Sql Injection Attacks

    Dim cmd As New SqlCommand("insert into accountant(acc_id,acc_name,payment_type,bill_no)" + 
                              "values (@id, @name, @ptype, @bnum)", cn)
    cn.Open()
    cmd.Parameters.AddWithValue("@id",TextBox1.Text)
    cmd.Parameters.AddWithValue("@name", TextBox2.Text)
    cmd.Parameters.AddWithValue("@ptype" TextBox3.Text)
    cmd.Parameters.AddWithValue("@bnum", TextBox4.Text)
    cmd.ExecuteNonQuery()
    cn.Close()

Please note, First, I don't know if your acc_id field is an Identity column. In this case don't add a value for it. It will be calculated automatically by the database.
Second, I assume that all fields are of type varchar/nvarchar or like. If this is not the case then you should use a conversion on the parameter values like

    cmd.Parameters.AddWithValue("@bnum", Convert.ToInt32(TextBox4.Text))

incorrect syntax near '21'

1 Answers1