why I can't use two datareader in one procedure?

Question

Hi everyone, can you explain me why I can't use two datareader in on procedure?

Here is the sample code:

Private Sub  Do_Execute()

  Dim conx as SqlConnection
  Dim cmd1 as SqlCommand
  Dim cmd2 as SqlCommand

  Dim drd1 as SqlDataReader
  Dim drd2 as SqlDataReader

  conx = new SqlConnection("connection string")
  conx.Open()

     cmd1 = new SqlCommand("SELECT * FROM Category" , conx)
     drd1 = cmd1.ExecuteReader()

     While (drd1.Read())
     {
        Reading Data From drd1
     }

     cmd2 = new SqlCommand("SELECT * FROM Stock" , conx)
     drd2 = cmd2.ExecuteReader()

     While (drd2.Read())
     {
        Reading Data From drd2
     }

End Sub

When I execute that program, It throws to the exception message : " There is already an open DataReader associates with this Command which must be closed first! "

When I closed drd1 before drd2 is initialized. It works.

Why I can't use like above code? Please explain me. Thanks in advance!

Answer 1

I've not tried this before but it should be possible.

Read Using Multiple Active Result Sets and,
Enabling Multiple Active Result Sets on MSDN

Do note this is for SQL2005 and above.

Excerpt from one of the articles:

To access multiple result sets on previous versions of SQL Server using SqlDataReader objects, a separate SqlConnection object must be used with each SqlCommand object.

Answer 2

This article explains what the problem is, and introduces the solution if you are using SQL Server. The solution is called MARS, or Multiple Active Record Sets, and is available in SQL Server 2005 and later versions.

Answer 3

It's because you're actually sharing the same connection.

You either need to:
1) use different connections for each SqlCommand, which was the original way you used to have to do this

or

2) use MARS (Multiple Active Result Sets) as described here