Redefining/Re-setting parameters in MySQL query

Question

I have the following code for inserting data into a table using a MySQL query in VB.NET

Dim MySqlCmdStr = "INSERT INTO tb_idlink(id1,id2) " &
    "VALUES (@par1,@par2)"
MySqlCmd.CommandText = MySqlCmdStr
Dim checkedItem As Object
For Each checkedItem In CheckedListBox_1.CheckedItems
    Try
        MySqlCmd.Connection = MySqlConn
        With MySqlCmd
            .Parameters.AddWithValue("@par1", currentID)
            .Parameters.AddWithValue("@par2", checkedItem.ToString())
        End With

        MySqlConn.Open()

        MySqlCmd.ExecuteNonQuery()
        MySqlConn.Close()
    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
    End Try
Next

My problem is if I have more than one box checked in CheckedListBox_1 then on the second loop an exception that says something like "parameter @par1 already defined". Is there a way I can re-define it? I'm not entirely familiar with the whole API.

Also, I'm not 100% sure if looping it is the best way to do this, but it's the first thing that popped into my head. Feel free to suggest an alternative way of doing this.

Answer 1

You dont redefine the parameters, you just supply a new value:

Dim SQL = "INSERT INTO tb_idlink (id1,id2) VALUES (@par1,@par2)"

Using dbcon As New MySqlConnection(MySQLConnStr)
    Using cmd As New MySqlCommand(SQL, dbcon)

        ' define the parameter names and types
        cmd.Parameters.Add("@par1", MySqlDbType.Int32)
        cmd.Parameters.Add("@par2", MySqlDbType.Int32)    ' ????

        dbcon.Open()
        For Each checkedItem In CheckedListBox1.CheckedItems
            ' provide the parameter values
            cmd.Parameters("@par1").Value = currentID
            cmd.Parameters("@par2").Value = Convert.ToInt32(checkedItem)
            cmd.ExecuteNonQuery()
        Next
    End Using
End Using

• Your code appears to reuse a global connection, that is ill-advised. The above uses Using blocks to create, use and and dispose of the DbConnection and DbCommand objects in the smallest scope possible
• You should favor Add rather than AddWithValue so you can specify the datatype rather than forcing the the DB Provider guess and reduce the chance of data type mismatch errors.
• These datatypes are a guess; CurrentId is not defined anywhere and given the names, both seem to be integers not strings.