Open DataReader associated with this Command which must be closed first.

  • Thread starter Thread starter eric.goforth
  • Start date Start date
E

eric.goforth

Hello,

I'm getting the a "There is already an open DataReader associated with
this Command which must be closed first" error message in the following
scenario. The message seems misleading, since the command isn't
associated with the datareader, but the connection is. Is there any
way to get this work work without closing my DataReader?

Thanks,
Eric

-------

Dim conn As SqlConnection


Dim cmd As SqlCommand
Dim drTests As SqlDataReader

strTestQuery = BuildTestSQL()

cmd = New SqlCommand(strTestQuery, conn)
drTests = cmd.ExecuteReader

Dim iDataRowCounter As Integer = 0

Do While drTests.Read


Dim strTest2SQL As String

strTest2SQL = BuildTest2SQL()

RunUpdateTransaction(strTest2SQL, conn)


Loop


Private Sub RunUpdateTransaction(ByVal UpdateSQL As String, ByVal conn
As SqlConnection)


Dim cmdTest As New SqlCommand(UpdateSQL, conn)

Try

Dim trnTestTest As SqlTransaction

trnTestTest =
conn.BeginTransaction(IsolationLevel.ReadCommitted, "TestTest")

Dim iUpdated As Integer

iUpdated = cmdTest.ExecuteNonQuery

'TODO:Change rollback tran to Commit
trnTestTest.Rollback()

'trnTestTest.Commit()

Catch ex As Exception
Throw ex
Finally
If Not cmdTest Is Nothing Then cmdTest.Dispose()
End Try

End Sub
 
Eric,

One option is to use a second connection for the update transactions.

Kerry Moorman
 
As Kerry stated, just use another instance of connection for doing updates.
Or, load the data first and then do updates.
 
Back
Top