VB.Net 插入多条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17340063/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VB.Net insert multiple records
提问by Vpp Man
I have several rows in DataGridView control. And i want to insert each row into database. I tried like this. But it gives error that parameter is already added. How to add parameter name once and then add values each time and execute it each time?
我在 DataGridView 控件中有几行。我想将每一行插入到数据库中。我是这样试的。但是它给出了参数已经添加的错误。如何添加参数名称一次，然后每次添加值并每次执行？
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
回答by Steve
Add the parameters outside the loop and inside the loop update only their values
在循环外添加参数，在循环内只更新它们的值
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 nice shortcut, but has its drawbacks. For example, it is unclear what datatype is required for the column Price. Using the Parameter constructor you could specify the exact datatype for the parameter and avoid a possible conversion mistake
使用 AddWithValue 是一个不错的快捷方式，但也有其缺点。例如，不清楚列需要什么数据类型Price。使用 Parameter 构造函数，您可以为参数指定确切的数据类型并避免可能的转换错误
Dim p = new SqlCeParameter("@price", SqlDbType.Decimal)
command.Parameters.Add(p)
......
