mysql - 数据插入sql vb.net

Question

The connection must be valid and open

执行以下代码时会出现此错误。

conn.ConnectionString = "server=localhost; user id=root; password=woodward1892; database=matchinfo"
        conn.Open()
        cmd.Connection = conn
        Dim d As Integer = 1
        For j As Integer = 0 To lstT2players.Items.Count - 1
            cmd = New MySqlCommand("Insert Into batsman2(`ID`, `PlayerName`) Values('" & d & "','" & lstT2players.Items.Item(j) & ")")
            d = d + 1
        Next
        cmd.ExecuteNonQuery()
        conn.Close()

请问你能帮我吗？

Answer 1

当您构建 MySqlCommand 时，您应该使用采用 MySqlConnection 的重载，否则该命令不知道要使用哪个数据库。之后 ExecuteNOnQuery 应该位于 For 循环内，否则您只运行最后一个命令。最后使用参数而不是字符串连接

Using conn = new MySqlConnection("server=localhost; user id=root; password=woodward1892; database=matchinfo")
Using cmd = conn.CreateCommand()
    conn.Open()
    string cmdText = @"Insert Into batsman2(`ID`, `PlayerName`) 
                       Values(@id, @name)"
    cmd.CommandText = cmdText
    cmd.Parameters.Add("@id", MySqlDbType.Int32)
    cmd.Parameters.Add("@name", MySqlDbType.VarWChar)
    Dim d As Integer = 1
    For j As Integer = 0 To lstT2players.Items.Count - 1
       cmd.Parameters["@id"].Value = d 
       cmd.Parameters["@name"].Value = lstT2players.Items.Item(j).ToString()
       cmd.ExecuteNonQuery()
       d = d + 1
    Next
End Using
End Using