• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 693
  • Last Modified:

Update Query Within Datareader

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.
MyTable
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=10.47.210.14,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
                objConnection.Open()
            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)
                    cmd.ExecuteNonQuery()
                End While
            End If
            objDR.Close()
            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.
0
G Scott
Asked:
G Scott
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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)
  objCommand.ExecuteNonQuery

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.
0
 
G ScottAuthor Commented:
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?
0
 
G ScottAuthor Commented:
Disregard.  ID10T error on my part. Columns were set as string values.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now