“already an open DataReader” exception with nested SqlDataReader in ASP.NET

I wanted to use nested SqlDataReader in the code below but I couldn’t make it.I get “System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first” with the code. Any Suggestions

            c = "select user_Reps.rep_key,Officers.Officer_Name from user_Reps left join Officers on user_Reps.rep_key = Officers.Officer_code where [user_key]="+userCode;
            if (c == null)
                c = sr.ReadToEnd();
            try
            {

                SqlCommand cmd = new SqlCommand(c, cn);
                cn.Open();
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
                SqlDataReader rdr2;
                while (rdr.Read())
                {
                    string c2 = "select Active_Clients.Clients_code,Active_Clients.Clients_Name,Active_Clients.Geo_code  from Active_Clients where Active_Clients.[Officer_code] =" + rdr.GetValue(0) + " order by Clients_Name";
                    SqlCommand cmd2 = new SqlCommand(c2, cn);

                    rdr2 = cmd2.ExecuteReader(CommandBehavior.CloseConnection);


                    Dictionary<string, object> d = new Dictionary<string, object>(rdr.FieldCount+rdr2.FieldCount);
                    while (rdr2.Read())
                    {
                        int i = 0;
                        for (; i < rdr.FieldCount; i++)
                        {
                            d[rdr.GetName(i)] = rdr.GetValue(i);
                        }

                        for (; i < rdr2.FieldCount; i++)
                        {
                            d[rdr2.GetName(i)] = rdr2.GetValue(i);
                        }

                        list.Add(d);
                    }




                    rdr2.Close();

                    //list.Add(d);
                }
                JavaScriptSerializer j = new JavaScriptSerializer();
                Response.Write(j.Serialize(list.ToArray()));

            }

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The error message is a bit misleading. Unless you have MultipleActiveResultSets=True in your connection string, you can have only one active result set per connection. This is still the case, if every reader hat it’s own separate SqlCommand object.

Method 2

You cannot have two open DataReaders on the same connection at the same time.

A common solutions to this problem would be to load the contents of the first DataReader into a DataTable or List<> before opening the next DataReader. You could also open a new connection for the nested query.

Edit Or, as @Mithrandir mentioned, you can use the MultipleActiveResultSets=true on the connection string.

Method 3

Assuming it’s on a sql server version 2005 or higher, enabling Multiple active resultsets (MARS) should do the trick I think. http://msdn.microsoft.com/en-us/library/h32h3abf%28v=vs.80%29.aspx.
If MARS is not an option, a different connection should be opened for the second command.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0