Using MARS or There is already an open DataReader associated with this Command which must be closed first.

14. December 2011 10:11 by Mrojas in   //  Tags: , , , , ,   //   Comments (0)

 

This is a very strange error that you can find sometimes when working with ADO.NET.

David McKean from MSFT says:

This occurs when you have multiple DataReaders open concurrently on the same connection,
ie you call SqlCommand.ExecuteReader but don't close the SqlDataReader returned by this
method before calling it again (either on the same command or another command on the same connection).

It requires a feature called MultipleActiveResultSets which is not available in all providers.

For example SQL2000 does not support it, it was implemented starting from SQL2005.

Also .NET 2.0 must be used.

For more information about enabling Multiple Active Result Sets see: http://msdn.microsoft.com/en-us/library/h32h3abf(v=vs.80).aspx

A good recommendation to make sure that the the readers are closed is to put them inside a using statement, in that case,
no matter if an exception happened they will be closed and disposed.

If you are using SQL Server 2000, MARS is not available so you can create two different connection objects.

Another good article about this issue is: http://blogs.msdn.com/b/spike/archive/2009/08/20/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-closed-first-explained.aspx

 

But in general to use it is just a change in the connection string:

<connectionStrings>
    <clear />
      <add name="VasquezDB" 
         connectionString="Data Source=rvasquez;Initial Catalog=VasquezDB;
                 Integrated Security=True;MultipleActiveResultSets=Yes" />
 </connectionStrings>

 

Good Luck

 

NOTE: a good link with more details about MARS is:

http://blog.typps.com/2011/04/mars-multiple-active-result-sets.html

Categories