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
– Trevor_G, Commented Feb 13, 2017 at 22:58

Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2017-02-14 18:17:35Z

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, 2017 at 18:17

answered Feb 13, 2017 at 23:15

Ňɏssa Pøngjǣrdenlarp

39k12 gold badges66 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

robotHamster Over a year ago

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

Trevor_G Over a year ago

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.

Ňɏssa Pøngjǣrdenlarp Over a year ago

@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

robotHamster Over a year ago

Thanks plutonix and @Trevor !

Collectives™ on Stack Overflow

Redefining/Re-setting parameters in MySQL query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related