Multiple DataReaders

[Sebastian Santacroce]

I'm using a datareader with VB .net with SQL server
database and trying
to make a connection to one store procedure with a
datareader and then a connection to another withought
closing the first and I get the errror:" there already
exists a connection to the SQl server with this data
reader. Must I close and first connection to create
another
For example I need to loop through one table (with a
datareader) and while looping make a new connection to
get other data based on the first data.

Thanks

 

[bruce barker]

a connection only supports 1 active datareader. if you want two readers
active at the same time, you need two connections. with two connections you
must be careful not deadlock yourself (as the server can not detect it) or
have the second connection join the firsts transaction space.

-- bruce (sqlwork.com)

 

[Sebastian Santacroce]

Heres a sample of code I'm using

Dim dr As SqlDataReader
Dim SqlComm As New SqlCommand
("GetPayrollEmployeePercentItems", SQLConn)
SqlComm.Parameters.Add("@EmployeeID", empID)
SqlComm.Parameters.Add
("@Level", "FixedSpecialEarning")
SqlComm.CommandType = CommandType.StoredProcedure
Try
dr = SqlComm.ExecuteReader
Catch ex As System.Data.SqlClient.SqlException
MsgBox(ex.ToString())
End Try

Do While dr.Read()
'Make new connection here while looping
thorugh first datareader will cause one connection still
open error. I want to make the connection like above here
with a different store procedure using data I get in this
loop
Loop

dr.Close()

 

[Sebastian Santacroce]

If I'm just reading there is no change of deadlock right?

 

[Axel Dahmen]

have the second connection join the firsts transaction space.

I've become curious on this: How can I make one connection join the
transaction of another?

TIA,
Axel Dahmen

 

[William Ryan]

Sebastian:

You can still have a deadlock when using a datareader, particlarly, your
query may be the one chosen as the victim.

 

[William Ryan]

I think a viable solution might be to load a given data structure from the
first query with the value(s) that you'll need for the second query. Use
While dr.Read() and load your structure, then close the second reader. Now,
you can iterate through your data structure and use it to fire your next
query. Another thing you may want to do is fill a datatable with all of the
valesin the table. Then, create a dataview for instance and use a RowFilter
matching the values of your structure, grabbing the records you need.

HTH,

Bill