如何在VB.NET中通过变量值向MySQL表插入新数据？

作为编程新手，我有一段用于创建新课程的VB.NET代码，需要修改标注的SQL查询部分。我猜测要用INSERT INTO tableName (CourseName) VALUES (_)语句，但不知道怎么把变量newCourseName的值作为VALUES的内容传入，代码片段如下：

Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
    mySqlConnection.Open()
    Dim newCourseName As String = InputBox("Type the name of the course you want to create", "Create new course")
    While newCourseName = "" Or ListBox1.Items.Contains(newCourseName) Or newCourseName.Length > 15
        newCourseName = InputBox("Invalid name! Type the name of the course you want to create (max 15 char)", "Create new course")
    End While

    mySqlCommand.Connection = mySqlConnection.ReturnConnection()
    mySqlCommand.CommandType = CommandType.Text
    mySqlCommand.CommandText = "**SQL QUERY HERE: Query that adds the new course to the table. Name is found in the variable newCourseName**."

    mySqlCommand.ExecuteNonQuery()

    mySqlConnection.Close()
    UpdateList()
End Sub

正确的写法：使用参数化查询 #

绝对不要直接把变量拼接到SQL字符串里——这会引发SQL注入漏洞，还会因为内容里的特殊字符（比如单引号）导致报错。正确的方式是用参数化查询：

1. 先写带占位符的SQL语句（MySQL用?作为参数占位符）
2. 给mySqlCommand添加参数，绑定newCourseName的值

修改后的代码片段如下：

Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
    mySqlConnection.Open()
    Dim newCourseName As String = InputBox("Type the name of the course you want to create", "Create new course")
    While newCourseName = "" Or ListBox1.Items.Contains(newCourseName) Or newCourseName.Length > 15
        newCourseName = InputBox("Invalid name! Type the name of the course you want to create (max 15 char)", "Create new course")
    End While

    mySqlCommand.Connection = mySqlConnection.ReturnConnection()
    mySqlCommand.CommandType = CommandType.Text
    -- 替换SQL语句
    mySqlCommand.CommandText = "INSERT INTO tableName (CourseName) VALUES (?CourseName)"
    -- 添加参数绑定
    mySqlCommand.Parameters.Add("?CourseName", MySqlDbType.VarChar, 15).Value = newCourseName

    mySqlCommand.ExecuteNonQuery()

    mySqlConnection.Close()
    UpdateList()
End Sub

注意事项： #

• 把tableName替换成你实际的数据库表名称
• MySqlDbType.VarChar要和你数据库中CourseName字段的类型匹配，长度15和之前的输入限制对应，避免数据截断

内容的提问来源于stack exchange，提问作者Raziewazie