VB.Net insert multiple entries

Question

I have several rows in a DataGridView control. And I want to insert every row in the database. I have tried so. But it gives an error that the parameter has already been added. How to add a parameter name once and then add values ​​every time and execute them every time?

Using connection As New SqlCeConnection(My.Settings.databaseConnectionString) Using command As New SqlCeCommand("INSERT INTO table_master(item, price) VALUES(@item, @price)", _ connection) connection.Open() For Each r As DataGridViewRow In dgvMain.Rows If (Not String.IsNullOrWhiteSpace(r.Cells(1).Value)) Then command.Parameters.AddWithValue("@item", r.Cells(1).Value.Trim) command.Parameters.AddWithValue("@price", r.Cells(2).Value) command.ExecuteNonQuery() End If Next End Using End Using 

Answer 1

Add parameters outside the loop and update only their values ​​inside the loop

 Using connection As New SqlCeConnection(My.Settings.databaseConnectionString) Using command As New SqlCeCommand("INSERT INTO table_master(item, price) VALUES(@item, @price)", _ connection) connection.Open() ' Create and add the parameters, just one time here with dummy values or' ' use the full syntax to create each single the parameter' command.Parameters.AddWithValue("@item", "") command.Parameters.AddWithValue("@price", 0) For Each r As DataGridViewRow In dgvMain.Rows If (Not String.IsNullOrWhiteSpace(r.Cells(1).Value)) Then command.Parameters("@item").Value = r.Cells(1).Value.Trim command.Parameters("@price").Value = r.Cells(2).Value command.ExecuteNonQuery() End If Next End Using End Using 

Using AddWithValue is a good shortcut, but it has its drawbacks. For example, it is unclear what data type is required for the Price column. Using the parameter constructor, you can specify the exact data type for the parameter and avoid a possible conversion error.

 Dim p = new SqlCeParameter("@price", SqlDbType.Decimal) command.Parameters.Add(p) ......