Multiple DataReaders

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
Fri Dec 05 17:29:19 CST 2003

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" <sSantacroce@ilogic.com> wrote in message
news:0a8601c3bb85$e6dc0810$a001280a@phx.gbl...
> 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
>

Sebastian
Fri Dec 05 17:32:27 CST 2003

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()
>-----Original Message-----
>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
>
>.
>

Sebastian
Fri Dec 05 17:51:05 CST 2003


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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Axel
Fri Dec 05 18:04:30 CST 2003

> 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
Sat Dec 06 13:14:38 CST 2003

Sebastian:

You can still have a deadlock when using a datareader, particlarly, your
query may be the one chosen as the victim.
"Sebastian Santacroce" <ssantacroce@ilogic.com> wrote in message
news:upuYmq4uDHA.2408@tk2msftngp13.phx.gbl...
>
> If I'm just reading there is no change of deadlock right?
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

William
Sat Dec 06 13:17:40 CST 2003

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
"Sebastian Santacroce" <sSantacroce@ilogic.com> wrote in message
news:0a8601c3bb85$e6dc0810$a001280a@phx.gbl...
> 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
>