Syntax error in INSERT INTO statement (vb.net)

Question

I receive an error when I run my project in vb: [syntax error in insert into statement]

Private Sub Bsimpan_Click(sender As Object, e As EventArgs) Handles Bsimpan.Click
    If Tno.Text = "" Or Tnis.Text = "" Or Tnama.Text = "" Or Tkelas.Text = "" Or CBjk.Text = "" Or Tt4lahir.Text = "" Or ttgllahir.Text = "" Then
        MsgBox("Data Belum Lengkap")
        Exit Sub
    Else
        Call koneksi()
        cmd = New OleDbCommand("select * from Anggota where nis='" & Tnis.Text & "'", conn)
        rd = cmd.ExecuteReader
        rd.Read()
        If Not rd.HasRows Then
            Dim sqltambah As String = "insert into Anggota(No,nis,Nama,Kelas,Jenis_kelamin,Tempat_lahir,Tanggal_lahir) values " & _
                "('" & Tno.Text & "', '" & Tnis.Text & "', '" & Tnama.Text & "', '" & Tkelas.Text & "', '" & CBjk.Text & "', '" & Tt4lahir.Text & "', '" & ttgllahir.Text & "')"
            cmd = New OleDbCommand(sqltambah, conn)
            cmd.ExecuteNonQuery()
            Call kosongkan()
            Call tampilkan()
            Tno.Focus()
        Else
            Dim sqledit As String = "update Anggota set " & _
                "NIS='" & Tnis.Text & "', " & _
                "Nama='" & Tnama.Text & "', " & _
                "Kelas='" & Tkelas.Text & "', " & _
                "Jenis_kelamin='" & CBjk.Text & "', " & _
                "Tempat_lahir='" & Tt4lahir.Text & "', " & _
                "Tanggal_lahir='" & ttgllahir.Text & "'"
            cmd = New OleDbCommand(sqledit, conn)
            cmd.ExecuteNonQuery()
            Call kosongkan()
            Call tampilkan()
        End If
    End If
End Sub

Building up the query string via concatenation always opens you up to syntax errors, in addition to SQL injection risks. Use parameterised queries instead. — shree.pat18, Dec 17 '14 at 03:44
Can you provide the value of sqltambah after the concatenation? — The_Black_Smurf, Dec 17 '14 at 04:26

score 1 · Answer 1 · edited May 23 '17 at 11:47

It might be a character in your values that could create an invalid sql statement. However, generating an sql statement with a concatenation of user inputs is a very dangerous and evil thing to do because it open the door to a simple, well known and well documented security issue called SQL Injection.

The only good way to handle parameter is with parameterized SQL query.

See this SO question for more details.

score 0 · Answer 2 · answered Dec 17 '14 at 03:53

0

Could it be a missing space between the table name and the first parenthesis?

Probably the best way to find out would be copy the content of the sqltambah variable and run the insert statement yourself and see if you get a little more info about where the error is.

Take a look at this: http://msdn.microsoft.com/en-us/library/ms174335.aspx

answered Dec 17 '14 at 03:53

sebagomez

9,501
7
51
89

missing space is not a syntax error, no problem with that – meda Dec 17 '14 at 04:49

meda · Accepted Answer · 2014-12-17T04:03:43.003

Use parametrized queries.

For example the query would look like this:

INSERT INTO anggota 
            (no, 
             nis, 
             nama, 
             kelas, 
             jenis_kelamin, 
             tempat_lahir, 
             tanggal_lahir) 
VALUES      (@no, 
             @nis, 
             @nama, 
             @kelas, 
             @jenis_kelamin, 
             @tempat_lahir, 
             @tanggal_lahir)

Then adjust your code:

cmd.Parameters.AddWithValue("@No", Tno.Text)
cmd.Parameters.AddWithValue("@nis", Tnis.Text)
cmd.Parameters.AddWithValue("@Nama", Tnama.Text)
cmd.Parameters.AddWithValue("@Kelas", Tkelas.Text)
cmd.Parameters.AddWithValue("@Jenis_kelamin", CBjk.Text)
cmd.Parameters.AddWithValue("@Tempat_lahir", Tt4lahir.Text)
cmd.Parameters.AddWithValue("@Tanggal_lahir", ttgllahir.Text)


cmd = New OleDbCommand(sqltambah, conn)
cmd.ExecuteNonQuery()

No, nis, Nama, Kelas, Jenis_kelamin, Tempat_lahir, Tanggal_lahir these are 7 columns in insert query — HaveNoDisplayName, Dec 17 '14 at 04:02

Syntax error in INSERT INTO statement (vb.net)

3 Answers3

Linked