Update Query Within Datareader

Posted on 2012-08-29
Medium Priority
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
  • 2
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38345016
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.

Author Comment

by:G Scott
ID: 38345115
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?

Author Comment

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month14 days, 3 hours left to enroll

809 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