Updating 3 Rows (fixed) Using 1 UPDATE Statement

Question

So far I have this code updating only 1 row but is there a way to specify the three rows? (3 different ID's)

Public Shared Sub UpdatePrice(ByVal NewPrice As Decimal, ByVal id As Integer)

    Dim dtoffenseinfo As New DataTable

    If Not DBConnection.State = ConnectionState.Open Then
        'open connection
        DBConnection.Open()
    Else

    End If

    cmd.Connection = DBConnection

    cmd.CommandText = "UPDATE tblStockPrice " & _
                   " SET Price=" & NewPrice & " WHERE id=" & id & ""


    cmd.ExecuteNonQuery()

    DBConnection.Close()
End Sub

I assume I need 2 more parameters for the other two rows and that would be fine.

Use [`and`](http://msdn.microsoft.com/en-us/library/ms188372.aspx) in your [search condition](http://msdn.microsoft.com/en-us/library/ms173545.aspx) of [where clause](http://msdn.microsoft.com/en-us/library/ms188047.aspx) I'm assuming you are using Ms Sql server as you have not specified your back-end. — Andrew Savinykh, Jun 10 '14 at 02:44

score 0 · Accepted Answer · answered Jun 10 '14 at 03:22

0

You can use one UPDATE statement with an appropriate WHERE clause to affect multiple records but, if you want to affect each record differently, calling ExecuteNonQuery once is not a very good option. Just create one command with appropriate parameters and then you can set the Value of each parameter multiple times to affect multiple records, e.g.

Using connection As New SqlConnection("connection string here"),
      command As New SqlCommand("UPDATE MyTable SET Price = @Price WHERE Id = @Id", connection)
    Dim priceParameter = command.Parameters.Add("@Price", SqlDbType.Money)
    Dim idParameter = command.Parameters.Add("@Id", SqlDbType.Int)

    Dim prices = {12.45, 132.67, 9.8}
    Dim ids = {1, 19, 56}

    connection.Open()

    For i = 0 To 2
        priceParameter.Value = prices(i)
        idParameter.Value = ids(i)

        command.ExecuteNonQuery()
    Next
End Using

answered Jun 10 '14 at 03:22

jmcilhinney

50,448
5
26
46

Thanks for that! I guess there's no escaping using loop. Cheers. – user3522506 Jun 10 '14 at 03:25
@user3522506 some people reported that using DAO instead of OLEDB works. – Andrew Savinykh Jun 10 '14 at 03:35
see here http://stackoverflow.com/questions/15788441/ms-access-batch-update-via-ado-net-and-com-interoperability and here http://social.msdn.microsoft.com/Forums/en-US/1256c0c0-f836-4a5c-8772-c3001839d052/bulk-insert-in-ms-access?forum=adodotnetdataproviders – Andrew Savinykh Jun 10 '14 at 03:35
@zespri, there's no point using DAO in this case. The number of rows being updated in this case is very small so there's no real advantage gained. – jmcilhinney Jun 10 '14 at 04:47
@user3522506, you can add your records to a `DataTable` and set the `RowState` of each `DataRow` to `Modified`, create a data adapter with an appropriate `UpdateCommand` and then save all the changes with a single call to `Update`. You're not really going to save much, if anything, code-wise and you might well have to use a loop to populate the `DataTable` anyway. – jmcilhinney Jun 10 '14 at 04:49

Updating 3 Rows (fixed) Using 1 UPDATE Statement

1 Answers1