"already an open DataReader" exception with nested SqlDataReader in ASP.NET
Solution 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.
Solution 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.
Solution 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.
Abdelwahed
Updated on June 16, 2022Comments
-
Abdelwahed almost 2 years
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())); }