在循环外和循环内添加参数只会更新其值
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
使用AddWithValue是一个不错的捷径,但有其缺点。例如,不清楚column所需的数据类型是什么Price
。使用Parameter构造函数,您可以为参数指定确切的数据类型,并避免可能的转换错误
Dim p = new sqlCeParameter("@price", sqlDbType.Decimal)
command.Parameters.Add(p)
......