VB.Net 插入多条记录 [英] VB.Net insert multiple records

问题描述

我在 DataGridView 控件中有几行.我想将每一行插入数据库.我是这样试的.但是它给出了参数已经添加的错误.怎么加一次参数名，然后每次加值，每次都执行?

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?

    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

推荐答案

循环外添加参数，循环内只更新参数值

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

使用 AddWithValue 是一个不错的捷径，但也有其缺点.例如，不清楚Price 列需要什么数据类型.使用 Parameter 构造函数，您可以为参数指定确切的数据类型并避免可能的转换错误

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

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

这篇关于VB.Net 插入多条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！