|
Subject:
|
executing 2 datareaders
|
|
Posted By:
|
msrnivas
|
Post Date:
|
1/7/2004 11:08:10 PM
|
i want to excute 2 queries ,second query will take the firrst querys output as parameter.
so i am using two SqlDataReader,sqlcommand.
when i try to execute second reader its saying below error
Error
rsroles.ExecuteReader Run-time exception thrown : System.InvalidOperationException - There is already an open DataReader associated with this Connection which must be closed first.
if i close and open dbconnection its fine but i am looping the first query so while looping here (myReadermain.GetString(0)) its saying error.
Is there any otherway to query inner sql means othere than datareader can i use any othe object?
here i am placing my code
thanks.
Dim myReadermain, myReader As SqlDataReader
Dim mainrs, rsroles As SqlCommand
Dim db As New SqlConnection(connstr)
sql = "select distinct x from y "
mainrs = New SqlCommand(sql, db)
myReadermain = mainrs.ExecuteReader
Try
While myReadermain.Read()
sql = "select name from b where z=" & Trim(myReadermain.GetString(0))
rsroles = New SqlCommand(sql, db)
myReader = rsroles.ExecuteReader
While myReader.Read()
If Trim(username) = Trim(myReader.GetString(0)) Then
rolesstring = rolesstring & Trim(myReadermain.GetString(0)) & "','"
Exit While
End If
End While
rsroles.Dispose()
myReader.Close()
End While
Finally
myReadermain.Close()
mainrs.Dispose()
End Try
|
|
Reply By:
|
gurujeet
|
Reply Date:
|
1/8/2004 12:46:08 AM
|
Hi,
A datareader is a connected recordset. So your connection is not free to be used by other data objects. To acheive what you want you should use a DataSet or another way could be to move everything from the datareader to a collection object and close the datareader to free up the connection. Hope this helps.
Regards,
Gurjit
|
|
Reply By:
|
planoie
|
Reply Date:
|
1/8/2004 7:15:47 AM
|
Maybe there is a way to rework the query you are using so you don't need to use multiple readers. This would speed up the code be eliminating the multiple calls.
Can you provide a little more detail on what the two queries are doing together so we might be able to suggestion query alternatives?
Peter
------------------------------------------------------
Work smarter, not harder.
|
