Error "There is already an open DataReader associated with this Command which must be closed first" when using 2 distinct commands
Solution 1:
Just add the following in your connection string:
MultipleActiveResultSets=True;
Solution 2:
- The optimal solution could be to try to transform your solution into a form where you don't need to have two readers open at a time. Ideally it could be a single query. I don't have time to do that now.
-
If your problem is so special that you really need to have more readers open simultaneously, and your requirements allow not older than SQL Server 2005 DB backend, then the magic word is MARS (Multiple Active Result Sets). http://msdn.microsoft.com/en-us/library/ms345109%28v=SQL.90%29.aspx. Bob Vale's linked topic's solution shows how to enable it: specify
MultipleActiveResultSets=truein your connection string. I just tell this as an interesting possibility, but you should rather transform your solution.- in order to avoid the mentioned SQL injection possibility, set the parameters to the SQLCommand itself instead of embedding them into the query string. The query string should only contain the references to the parameters what you pass into the SqlCommand.
Solution 3:
You can get such a problem when you are two different commands on same connection - especially calling the second command in a loop. That is calling the second command for each record returned from the first command. If there are some 10,000 records returned by the first command, this issue will be more likely.
I used to avoid such a scenario by making it as a single command.. The first command returns all the required data and load it into a DataTable.
Note: MARS may be a solution - but it can be risky and many people dislike it.
Reference
- What does "A severe error occurred on the current command. The results, if any, should be discarded." SQL Azure error mean?
- Linq-To-Sql and MARS woes - A severe error occurred on the current command. The results, if any, should be discarded
- Complex GROUP BY on DataTable