Vpp Man
Vpp Man

Reputation: 2546

VB.Net insert multiple records

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

Upvotes: 5

Views: 12135

Answers (1)

Steve
Steve

Reputation: 216293

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

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

Upvotes: 6

Related Questions