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

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

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.