Parameters.AddWithValue:参数已定义

Question

我尝试添加一个参数.addwithvalue。在更改代码之前是这样的...

    Private Sub ComboBox7_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox7.SelectedIndexChanged

    Me.Cursor = Cursors.WaitCursor
    MysqlConn.Close()
    MysqlConn.Open()
    COMMAND.CommandText = "select logo from licenses where name = '" & ComboBox7.Text & "'"
    COMMAND.Connection = MysqlConn
    Dim da As New MySqlDataAdapter(COMMAND)
    Dim ds As New DataSet()
    da.Fill(ds, "projectimages")
    Dim c As Integer = ds.Tables(0).Rows.Count
    If c > 0 Then
        If IsDBNull(ds.Tables(0).Rows(c - 1)("logo")) = True Then
            PictureBox6.Image = Nothing
        Else
            Dim bytBLOBData() As Byte = ds.Tables(0).Rows(c - 1)("logo")
            Dim stmBLOBData As New MemoryStream(bytBLOBData)
            PictureBox6.Image = Image.FromStream(stmBLOBData)
        End If
    End If
    Me.Cursor = Cursors.Default
End Sub

现在我尝试这样做来添加paramatrers.addwithValue而不成功：

    Private Sub ComboBox7_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox7.SelectedIndexChanged

    Me.Cursor = Cursors.WaitCursor
    MysqlConn.Close()
    MysqlConn.Open()
    'COMMAND.CommandText = "select logo from licenses where name = '" & ComboBox7.Text & "'"
    COMMAND.CommandText = "select logo from licenses where name = @ComboBox7Select"
    COMMAND.Parameters.AddWithValue("@ComboBox7Select", If(String.IsNullOrEmpty(ComboBox7.Text), DBNull.Value, ComboBox7.Text))
    COMMAND.Connection = MysqlConn
    Dim da As New MySqlDataAdapter(COMMAND)
    Dim ds As New DataSet()
    da.Fill(ds, "projectimages")
    Dim c As Integer = ds.Tables(0).Rows.Count
    If c > 0 Then
        If IsDBNull(ds.Tables(0).Rows(c - 1)("logo")) = True Then
            PictureBox6.Image = Nothing
        Else
            Dim bytBLOBData() As Byte = ds.Tables(0).Rows(c - 1)("logo")
            Dim stmBLOBData As New MemoryStream(bytBLOBData)
            PictureBox6.Image = Image.FromStream(stmBLOBData)
        End If
    End If
    Me.Cursor = Cursors.Default
End Sub

出现错误"参数'@ComboBox7Select'已定义"。

我做什么来改变工作？？

谢谢。

Answer 1

不要将MySqlConnection和MySqlCommand存储为类中的字段，根本不要重用它们。这只是错误的根源，没有任何好处。在需要它们的地方创建、初始化、使用和处置(Using语句(它们，所以在这种方法中。

您不清除参数，这就是第二次使用时出现此错误的原因。

因此，在添加之前进行简单的COMMAND.Parameters.Clear()即可解决问题。但是使用我上面提到的方法：

Private Sub ComboBox7_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox7.SelectedIndexChanged
    Dim ds As New DataSet()
    Dim licenseNameValue As Object = DBNull.Value
    If Not String.IsNullOrEmpty(ComboBox7.Text) Then licenseNameValue = ComboBox7.Text
    Using mysqlConn As New MySqlConnection("ConnectionString...")
        Using da As New MySqlDataAdapter("select logo from licenses where name = @licenseName", mysqlConn)
            da.SelectCommand.CommandText = "select logo from licenses where name = @licenseName"
            da.SelectCommand.Parameters.AddWithValue("@licenseName", licenseNameValue)
            da.Fill(ds, "projectimages") ' you dont need to open/close the connection with DataAdapter.Fill
        End Using
    End Using
    ' ....
End Sub

Answer 2

问题是您使用的是全局变量COMMAND每次组合中所选索引更改时都会使用该变量。每次使用以下命令初始化命令：

COMMAND=New MySqlCommand()

或者您必须清除参数：

COMMAND.Parameters.Clear()
COMMAND.Parameters.AddWithValue("@ComboBox7Select", If(String.IsNullOrEmpty(ComboBox7.Text), DBNull.Value, ComboBox7.Text))

但最好的方法始终是使用 Using 结构创建和释放MySql对象：

Using MysqlConn As New MySqlConnection(connString)
    Using COMMAND As New MySqlCommand()
    'your code 
    End Using
End Using