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

[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

 

[Guest]

Eric,

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

Kerry Moorman

 

[Miha Markic [MVP C#]]

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