mysql - System.Data.SqlClient.SqlException 字符串后未闭合的引号

Question

每次我运行这段代码来放置一些学生信息，当我点击保存它时，cmd.Executenonquery() 中总是出现消息..请帮忙..

Imports System.Collections.ObjectModel
Imports System.Data.SqlClient
Imports System.Data


Public Class SI
Dim con As New SqlConnection With {.ConnectionString = "Server=Danica-pc; database=SI;user=dandan;pwd=danica;"}
Dim cmd As New SqlCommand
Dim query As String

Dim stuid, i As Integer
Dim studentID As Integer
Dim StudentBindingSource As Object
Dim TableAdapterManager As Object

Private Sub StudentBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
    Me.Validate()
    Me.StudentBindingSource.EndEdit()
    Me.TableAdapterManager.UpdateAll(Me.SIDataSet)
End Sub

Private Sub Label4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Kasarian.Click

End Sub

Private Sub SI_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'TODO: This line of code loads data into the 'SIDataSet.Studentinfo' table. You can move, or remove it, as needed.
    Me.StudentinfoTableAdapter.Fill(Me.SIDataSet.Studentinfo)

End Sub

Private Sub getData()
    i = DataGridView1.CurrentCell.RowIndex()
    studentID = i

End Sub
Private Sub dataReload()
    familynem.Clear()
    middlenem.Clear()
    givennem.Clear()
    usename.Clear()
    accpass.Clear()
    confirmpass.Clear()


    Try
        Dim sql As String = "Select * from Studentinfo"
        Dim myAdapter As New SqlDataAdapter(sql, con)
        con.Open()
        Dim myDataset As New DataSet()
        myAdapter.Fill(myDataset, "SI")
        DataGridView1.DataSource = myDataset
        DataGridView1.DataMember = "SI"
        DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        con.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub
Private Sub famliynem_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles familynem.TextChanged

End Sub

Private Sub stat_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles stat.SelectedIndexChanged

End Sub

Private Sub HomeToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles HomeToolStripMenuItem.Click
    Home.Show()
    Me.Hide()
End Sub

Private Sub EventsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EventsToolStripMenuItem.Click
    EventsForm.Show()
    Me.Hide()
End Sub

Private Sub ProductsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProductsToolStripMenuItem.Click
    Products.Show()
    Me.Hide()
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cancel.Click
    Home.Show()
    Me.Close()
    End
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save.Click
    Dim genderval As String
    Dim birthdate As String
    birthdate = bday.Value.ToString()
    If babae.Checked = True Then
        genderval = "Female"
    Else
        genderval = "Male"
    End If
    query = "insert into studentinfo(Lastname,Firstname,middlename,birthdate,gender,username)""values('" & familynem.Text & "','" & givennem.Text & "','" & middlenem.Text & "','" & birthdate & "','" & genderval & "','" & usename.text & "')"
    con.Open()
    cmd = New SqlCommand(query, con)
    *cmd.ExecuteNonQuery()*
    con.Close()
    dataReload()
    user.Show()
    Me.Hide()


End Sub
End Class

Answer 1

您在这一行中有不需要的 "":

    query = "insert into studentinfo(Lastname,Firstname,middlename,birthdate,gender,username)""values('" & familynem.Text & "','" & givennem.Text & "','" & middlenem.Text & "','" & birthdate & "','" & genderval & "','" & usename.text & "')"

此外，我建议您考虑使用 SQL 参数来传递值:

编辑:您可以通过替换此代码来使用 SQL 参数:

query = "insert into studentinfo(Lastname,Firstname,middlename,birthdate,gender,username)""values('" & familynem.Text & "','" & givennem.Text & "','" & middlenem.Text & "','" & birthdate & "','" & genderval & "','" & usename.text & "')"
con.Open()
cmd = New SqlCommand(query, con)
*cmd.ExecuteNonQuery()*
con.Close()

与:

Using conn As New SqlConnection("YOUR CONNECTION STRING")
    Dim query = "INSERT INTO studentinfo(Lastname,Firstname,middlename,birthdate,gender,username) VALUES(@familynem, @givennem, @middlenem, @birthdate, @genderval, @usename)"
    Using cmd As New SqlCommand(query, conn)
        cmd.Parameters.AddWithValue("@familynem", familynem.Text)
        cmd.Parameters.AddWithValue("@givennem", givennem.Text)
        cmd.Parameters.AddWithValue("@middlenem", middlenem.Text)
        cmd.Parameters.AddWithValue("@birthdate", birthdate)
        cmd.Parameters.AddWithValue("@genderval", genderval.Text)
        cmd.Parameters.AddWithValue("@usename", usename.Text)
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()
    End Using
End Using

Using 构造负责为您调用 .Dispose()，您不应该有连接挂起。 SQL 参数有助于防止 SQL 注入(inject)攻击，如果您有像 O'Reilly 这样的名称，撇号会成为问题，则可以阻止查询中断。