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.

Build your cmd outside the loop using the add method not the addwithvalue method. Then in the loop use the .Value(Name) = value. ALso don't open and close the connection every loop. DO that around the loop — Trevor_G, Feb 13 '17 at 22:58

Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2017-02-14T18:17:35.820

2

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.

edited Feb 14 '17 at 18:17

answered Feb 13 '17 at 23:15

Ňɏssa Pøngjǣrdenlarp

38,411
12
59
178

The datatypes in the DB are integers so you are correct there, I will be favoring Add over AddWithValue from now on. One question just so I'm clear on this. I fixed the problem I had earlier by having a `cmd.Parameters.Clear()` at the beginning of each loop. Is that not good practice as well? @Plutonix – robotHamster Feb 14 '17 at 18:11
Using clear: It depends on what the code does. It's not a bad idea, but sometimes one or two are fixed, that is do not change per loop, so you would need to re-establish those again every loop.. but it's fairly trivial. – Trevor_G Feb 14 '17 at 18:16
1

@sharifanani why clear and recreate the same parameters over and over when all you really need is to supply a new value? `Clear()` will work but is overkill – Ňɏssa Pøngjǣrdenlarp Feb 14 '17 at 18:18
Thanks plutonix and @Trevor ! – robotHamster Feb 14 '17 at 18:24

Redefining/Re-setting parameters in MySQL query

1 Answers1