Update Query Within Datareader

Posted on 2012-08-29
Last Modified: 2012-08-29
I am using a datareader to loop through 10 records in a table. As it loops I want to update a column but adding two columns.

Basically, I want to add 'countTotalByRun' and 'remainder' and update 'countTotalByRun' to that sum.
I am trying to do this, but it's failing on the cmd.ExecuteNonQuery saying a datareader is already open and needs to be closed first:

Dim objDR As SqlClient.SqlDataReader
            Dim objCommand As SqlClient.SqlCommand
            Dim ConnectionString As String = "Data Source=,1433\SQLEXPRESS;Initial Catalog=eBanPress30;User Id=InvUser;Password=letmein1;"
            Dim objConnection As SqlClient.SqlConnection
            Dim ssql As String

            objConnection = New SqlClient.SqlConnection(ConnectionString)
            ssql = "SELECT * FROM partnumbers"

            If objConnection.State <> ConnectionState.Open Then
            End If
            objCommand = New SqlClient.SqlCommand(ssql, objConnection)
            objDR = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
            objCommand = Nothing
            'Read all the rows
            If objDR.HasRows Then
                While objDR.Read()
                    Dim cmd As SqlCommand
                    Dim strSQL As String
                    strSQL = "UPDATE partnumbers SET countTotalByRun ='" & ((objDR.Item("remainder")) + (objDR.Item("countTotalByRun"))) & "' WHERE customerPart = '" & (objDR.Item("customerPart")) & "'"
                    cmd = New SqlCommand(strSQL, objConnection)
                End While
            End If
            objDR = Nothing

There has got to be an easier way to do this and I am all ears.  Well, eyes anyway.  Thanks for any help you can give me on this.
Question by:G Scott
    LVL 142

    Accepted Solution

    if you don't need to actually "read" the data, but just "update" it in the backend, you should implement a plain update query right away?!

    UPDATE partnumbers
      SET countTotalByRun = remainder+ countTotalByRun

    Open in new window

    that way, no need to bring back data from the server to the client, and no need to do a "row by row" processing.
    1 update command, all the work done, minimal time.

     ssql = "UPDATE partnumbers
      SET countTotalByRun = remainder+ countTotalByRun "
    objCommand = New SqlClient.SqlCommand(ssql, objConnection)

    Open in new window

    note: if really you need to "read in" the data, and then execute a statement on the same connection, you need to either:
    * open a second connection (to the same db) to run the update statements (but you might run into locking issues as you select and update the same table at the same time)
    * better: read in all the data using a datatable, and build/run the update sql using the datatable as input.
    LVL 1

    Author Comment

    by:G Scott
    angelIII, that makes total sense, however, with the remainder + countTotalbyRun it takes the 1 and the 9 and makes it 91 instead of 10.  Any thoughts?
    LVL 1

    Author Comment

    by:G Scott
    Disregard.  ID10T error on my part. Columns were set as string values.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
    Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now