Visual Basic 错误"')'附近的语法不正确"。

Question

我使用的是Visual Studio 2019社区版本，我有一个连接到SQL server的表单，我的Visual basic表单的连接测试是成功的。然而，当我尝试将数据放入数据库时，我在第14行上收到错误

System.Data.SqlClient.SqlException："("附近的语法不正确。">

这是我的代码：哪里出错了？

Imports System.Data.SqlClient
Public Class Form1
Dim name, hello, eligible As String
Dim blood As String
Dim agree As String
Dim connection As New SqlConnection("Server = DESKTOP-SNVR5AC; Database = bloodform; Integrated security = true")
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'connection
Dim command As New SqlCommand("insert into form(Full_name,DOB,Email,Town,Blood_type,Age,Previous_donation,Any_diseases,Positive_bloodtest,Cardiac_problems,Bleeding_disorders) values ('" & TextBox1.Text & "','" & DateTimePicker1.Text & "','" & TextBox2.Text & "','" & ComboBox2.Text & "','" & ListBox2.Text & "','" & ComboBox1.Text & "','" & GroupBox1.Text & "','" & GroupBox2.Text & "','" & GroupBox3.Text & "','" & GroupBox4.Text & "','" & GroupBox5.Text & "',)", connection)
connection.Open()
If command.ExecuteNonQuery() = 1 Then
MsgBox("Application submitted")
Else
MsgBox("Application not submitted")
End If
connection.Close()
'checkbox
If CheckBox1.Checked = False Then
agree = "Please Agree the Terms and Conditions"
MsgBox(agree)
ElseIf CheckBox1.Checked = True Then
'welcome message
name = (TextBox1.Text)
hello = "Welcome"
MsgBox(hello & Space(2) & name)
'age eligibility 
If ComboBox1.SelectedIndex = 0 Then
eligible = "You are underage"
MsgBox(eligible)
ElseIf ComboBox1.SelectedIndex = 1 Then
eligible = "You can donate blood."
'MsgBox(eligible)
'blood
If ListBox2.SelectedItem.ToString() = "Apositive" Then
blood = "You are A+ and can donate to A+ and AB+"
MsgBox(eligible & Space(1) & blood)
ElseIf ListBox2.SelectedItem.ToString() = "Bpositive" Then
blood = "You are B+ and can donate to B+ and AB+"
MsgBox(eligible & Space(1) & blood)
ElseIf ListBox2.SelectedItem.ToString() = "Opositive" Then
blood = "You are O+ and can donate to O+, A+,  B+ and AB+"
MsgBox(eligible & Space(1) & blood)
ElseIf ListBox2.SelectedItem.ToString() = "ABpositive" Then
blood = "You are AB+ and can donate to AB+"
MsgBox(eligible & Space(1) & blood)
ElseIf ListBox2.SelectedItem.ToString() = "Anegative" Then
blood = "You are A- and can donate to  A+, A-, AB+ and AB-"
MsgBox(eligible & Space(1) & blood)
ElseIf ListBox2.SelectedItem.ToString() = "Bnegative" Then
blood = "You are B- and can donate to B+, B-, AB+ and AB-"
MsgBox(eligible & Space(1) & blood)
ElseIf ListBox2.SelectedItem.ToString() = "Onegative" Then
blood = "You are O- and can donate to Everyone"
MsgBox(eligible & Space(1) & blood)
ElseIf ListBox2.SelectedItem.ToString() = "ABnegative" Then
blood = "You are AB- and can donate to AB+ and AB-"
MsgBox(eligible & Space(1) & blood)
End If
End If
End If
End Sub
End Class

Answer 1

SQL查询的末尾包含一个不必要的逗号

GroupBox4.Text & "','" & GroupBox5.Text & "',)", connection)

我建议您使用sql参数来传递值，这样更安全(防止sql注入(、智能且易于控制。

https://stackoverflow.com/a/11139935/3195211

尝试：

cmd.CommandText = "INSERT INTO table2(column3) Values (@parameter ) where Column2=true"
cmd.Parameters.AddWithValue("@parameter", TextBox1.Text)
'etc.

Answer 2

需要记住的一些事项：

试图在整个表单中重复使用同一个连接对象是不好的。这会干扰称为连接池的过程。

使用字符串串联将用户输入放入SQL查询中是不好的。事实上，很难夸大这种做法有多么糟糕

如果引发异常，原始代码将无法关闭连接。

您可能应该在插入记录之前检查是否符合资格。

Dim connectionString As String = "Server = DESKTOP-SNVR5AC; Database = bloodform; Integrated security = true"
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
If Not CheckBox1.Checked Then
MsgBox("Please agree to the Terms and Conditions")
Exit Sub
End If
If ComboBox1.SelectedIndex = 0 Then
MsgBox("You are underage")
Exit Sub
End If
Dim SQL As String =
"INERT INTO FORM (
Full_name, DOB, Email, Town, Blood_type, Age, Previous_donation, Any_diseases, Positive_bloodtest, Cardiac_problems, Bleeding_disorders
) VALUES (
@FullNamae, @DOB, @Email, @Town, @BloodType, @Age, @PreviousDonation, @Diseases, @BloodTest, @Cardiac, @BleedingDisorders
)"
Dim RowsChanged As Integer
Using cn As New SqlConnection(connectionString), _
cmd As New SqlCommand(SQL, cn)
'I have to guess at types and lengths here. You should use actual types definitions from your database.
cmd.Parameters.Add("@FullName", SqlDbType.NVarChar, 50).Value = TextBox1.Text
cmd.Parameters.Add("@DOB", SqlDbType.DateTime).Value = DateTimePicker1.Value
cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 85).Value = TextBox2.Text
cmd.Paramerers.Add("@Town", SqlDbType.NVarChar, 50).Value = ComboBox2.Text
cmd.Parameters.Add("@BloodType", SqlDbType.VarChar, 3).Value = ListBox2.Text
cmd.Parameters.Add("@Age", SqlDbType.Int).Value = Integer.Parse(ComboBox1.Text)
cmd.Parameters.Add("@PreviousDonation", SqlDbType.VarChar, 1).Value = GroupBox1.Text
cmd.Parameters.Add("@Diseases", SqlDbType.VarChar, 1).Value = GroupBox2.Text
cmd.Parameters.Add("@BloodTest", SqlDbType.VarChar, 1).Value = GroupBox3.Text
cmd.Parameters.Add("@Cardiac", SqlDbType.VarChar, 1).Value = GroupBox4.Text
cmd.Parameters.Add("@BleedingDisorders", SqlDbType.VarChar, 1).Value = GroupBox5.Text
cn.Open()
RowsChanged = cmd.ExecuteNonQuery()
End Using
If RowsChanged <> 1 Then
MsgBox("Application not submitted")
Exit sub
End If
MsgBox($"Weclome  {TextBox1.Text}")
Dim donorTable = New Dictionary(Of String, String) From
{
{"A+", "A+ and AB+"}, 
{"B+", "B+ and AB+"}, 
{"O+", "O+, A+,  B+ and AB+"}, 
{"AB+", "AB+"}, 
{"A-", "A+, A-, AB+ and AB-"}, 
{"B-", "B+, B-, AB+ and AB-"}, 
{"O-", "Everyone"}, 
{"AB-", "AB+ and AB-"}, 
}
Dim bloodType As String = CStr(ListBox2.SelectedItem).
Replace("positive", "+").Replace("negative", "-")
Dim bloodDonor As String = donorTable(bloodType)
MsgBox($"You are {bloodType} and can donate to {bloodDonor}")
End Sub

Answer 3

为了防止SQL注入，请改用参数化查询。

请注意，使用Using类可以避免使用Final方法，而Close是通过End Using调用的Dispose在ADO.NET中隐式完成的。

Dim connectionString As String = "Server = DESKTOP-SNVR5AC; Database = bloodform; Integrated security = true"
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Using connection As New SqlConnection(connectionString)
Try
connection.Open()
Dim command = New SqlCommand
command.Connection = connection
command.CommandText = "insert into form(Full_name) values (@parameter)"
command.Parameters.AddWithValue("@parameter", TextBox1.Text)
If command.ExecuteNonQuery() Then
MsgBox("Application submitted")
Else
MsgBox("Application not submitted")
End If
command.Dispose()
Catch ex As Exception
MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
End Try
End Using
...
End Sub