Problem with nested data readers

I am getting this error

"System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.....blah blah blah........at dispatcherList.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\dispatcherList - Copy.aspx.cs:line 54"

Somehow, I don't understand how to make nested data readers correctly, and I am missing some sort of proper close of the nested data reader. Here's the code. I'm sure it's just a misunderstanding that I have about how the readers actually work. Thanks in advance

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Web.Security;
using System.Runtime.InteropServices;
using System.Security.Principal;

public partial class dispatcherList : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string companyID = Session["companyID"].ToString();
        if (companyID != "" && companyID != null)
        {
            SqlConnection myConnection = new SqlConnection("server=localhost;" +
                                               "Trusted_Connection=yes;" +
                                               "database=myDB; " +
                                               "connection timeout=30");
            try
            {
                myConnection.Open();
                SqlDataReader orderReader = null;
                SqlDataReader driverReader = null;
                SqlDataReader dispatchReader = null;

                SqlCommand getDriverInfo = new SqlCommand("Select ID, FirstName, LastName, Lat, Lng FROM Users Where Dispatcher = " + Session["companyID"].ToString(), myConnection);
                driverReader = getDriverInfo.ExecuteReader();
                while (driverReader.Read())
                {
                    Response.Write("<ul class='driver'>");
                    Response.Write("<li class='firstName'>" + driverReader["FirstName"].ToString() + "</li>");
                    Response.Write("<li class='lastName'>" + driverReader["LastName"].ToString() + "</li>");
                    if (driverReader["Lat"] != "" && driverReader["Lat"] != DBNull.Value && driverReader["Lat"] != "undefined")
                    {
                        Response.Write("<li class='lat'>" + driverReader["Lat"].ToString() + "</li>");
                    }
                    else {
                        Response.Write("<li>0</li>"); 
                    }
                    if (driverReader["Lng"] != "" && driverReader["Lng"] != DBNull.Value && driverReader["Lng"] != "undefined")
                    {
                        Response.Write("<li class='lng'>" + driverReader["Lng"].ToString() + "</li>");
                    }
                    else {
                        Response.Write("<li>0</li>");
                    }

                    Response.Write("<ul class='orders'>");
                    SqlCommand getOrderInfoByDriver = new SqlCommand("Select Address, Next, Problem, CreatedBy FROM Temp Where CreatedBy = " + driverReader["ID"] + " AND Delivered IS NULL", myConnection);
                    orderReader = getOrderInfoByDriver.ExecuteReader();
                    while (orderReader.Read())
                    {
                        if (orderReader["Address"] != "" && orderReader["Address"] != DBNull.Value && orderReader["Address"] != "undefined")
                        {
                            Response.Write("<li class='address'>" + orderReader["Address"].ToString() + "</li>");
                        }
                        else
                        {
                            Response.Write("<li></li>");
                        }
                        if (orderReader["Next"] != "" && orderReader["Next"] != DBNull.Value && orderReader["Next"] != "undefined")
                        {
                            Response.Write("<li class='next'>" + orderReader["Next"].ToString() + "</li>");
                        }
                        else
                        {
                            Response.Write("<li></li>");
                        }
                        if (orderReader["Problem"] != "" && orderReader["Problem"] != DBNull.Value && orderReader["Problem"] != "undefined")
                        {
                            Response.Write("<li class='problem'>" + orderReader["Problem"].ToString() + "</li>");
                        }
                        else
                        {
                            Response.Write("<li></li>");
                        }
                    }
                    Response.Write("</ul>");
                    orderReader.Close();
                    getOrderInfoByDriver.Dispose();
                }
                Response.Write("</ul>");
                driverReader.Close();
                getDriverInfo.Dispose();
                myConnection.Close();
            }
            catch (Exception error)
            {
                Response.Write("<p>" + error + "</p>");
                myConnection.Close();
            }
        }
    }
}

Open in new window

LVL 9
BobHavertyComhAsked:
Who is Participating?
 
Jacques Bourgeois (James Burger)PresidentCommented:
The problem is not with the commands, it is with the connection.

By default, you cannot have more than one command running concurrently on the same connection.

With some databases, the only option you have is to open a new command for each command.

But SQL Server enables you to redefine the connection so that it enables more than one command to run on the same connection. The solution is thus simply to go to MARS, not the planet, but the Multiple Active Result Sets, by adding the following to your connection string: MultipleActiveResultSets=True.
0
 
Fernando SotoRetiredCommented:
Hi BobHavertyComh;

You can not have multiple readers using the same command object, only one can be opened at any time.

What you can do is to place all the Select command into one SqlCommand object something like this, please note the ; after each Select statement.

SqlCommand getAllInfo = new SqlCommand("Select ...; Select ....; Select ...");

Then read the info from the reader and when you have read all the data from the first Select statement you will get end of file just like if it only had one Select statement and will take you out of the loop. Then you can issue the following statement.

getAllInfo.NextResult();

Now you are ready to go through the data from the second Select statement. Continue this until you have read all the data from each of the Select statements.
0
 
BobHavertyComhAuthor Commented:
I actually ran into this about 6 months ago and forgot how I fixed it. As soon as you mentioned MultipleActiveResultSets then it all came back to me, and it worked right away. Thanks a lot!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.