INSERT INTO语句中的语法错误（vb.net）

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

我在vb中运行项目时收到错误:[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

3 个解决方案

#1

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()

#2

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.

它可能是您的值中的一个字符,可能会创建一个无效的sql语句。但是,生成带有用户输入串联的sql语句是一件非常危险和邪恶的事情,因为它为一个简单的,众所周知且记录良好的安全问题打开了大门,称为SQL注入。

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

处理参数的唯一好方法是使用参数化SQL查询。

See this SO question for more details.

有关详细信息,请参阅此SO问题。

#3

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.

找出的最好方法可能是复制sqltambah变量的内容并自己运行insert语句,看看你是否能获得有关错误位置的更多信息。

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

看看这个:http://msdn.microsoft.com/en-us/library/ms174335.aspx

#1