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

    Frage

  • I have a rather large ASP-NET-2.0 project that I've been developing for some time.

    I'm using System.Data.Common classes for all of my data access, and SQL-2005 as my provider.

    Up to and including Beta-2, everything was working just fine and dandy.

    Then, with the release of RC1, I suddenly started to get the following error::

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

    This totally threw me, but I dutifuly started to trace all of my connections & readers to make sure I was closing & disposing of them properly, and all appears to be correct.

    After a bunch of tedious and very time-consuming debugging, I've come to the conclusion that from RC1 onwards, the framework is not correctly disposing of commands and/or readers when requested to, and this is resulting in a connection "remembering" a previously used command... and refusing to allow it's re-use, because you are not supposed to use multiple instances of the same command on a connection.

    If I roll the installation back to Beta-2 and use the same source code... everything runs fine again.

    Note: This is an ASP issue, as my SQL-Server is on a separate machine.

    I reported this to MS during RC1, but nothing was done about it before RTM, which either means it was ignored, or it's considered to be "as designed" and I'm just not using it properly.

    This is a HUGE problem for me, as it will force me to re-write the entire app to use multiple connections instead of the original MARS design... not something I'm looking forward too, and a really inefficient use of my server.

    Before I go off and start work on this, can anyone suggest something I may have missed in the change from Beta-2 to RC1/RTM (they both have the same issue) or suggest a reason why this would suddenly start to happen?

    Donnerstag, 3. November 2005 03:10

Antworten

  • This is due to a change in the default setting for MARs.  It used to be on by default and we changed it to off by default post RC1.  So just change your connection string to add it back (add MultipleActiveResultSets=True to connection string).

    Donnerstag, 3. November 2005 05:43
  • MultipleActiveResultSets requires that the provider support it.

    SQL2000 doesn't support MultipleActiveResultSets.

    This was implemented on SQL2005.

    Hope that helps.

    Mittwoch, 12. April 2006 21:15

Alle Antworten

  • This is due to a change in the default setting for MARs.  It used to be on by default and we changed it to off by default post RC1.  So just change your connection string to add it back (add MultipleActiveResultSets=True to connection string).

    Donnerstag, 3. November 2005 05:43
  •  Matt Neerincx wrote:
    This is due to a change in the default setting for MARs.  It used to be on by default and we changed it to off by default post RC1.  So just change your connection string to add it back (add MultipleActiveResultSets=True to connection string).



    Arrrggghhhh...!!!  THANK YOU!!

    At least I can stop chasing this thing down and assuming I must be an idiot (well... perhaps I should not be so quick on that one).

    If you don't mind me asking... Why the last minute change to something that has been in place for so long ??
    Donnerstag, 3. November 2005 09:33
  • Hello,

    I tried to make as I understood but I am confronted with the same error however I put MultipleActiveResultSets=True well at the chains of connection

    Syntax presents myself thus at the time of the call :

    Private ReadOnly SqlCon As System.Data.SqlClient.SqlConnection

    Public Function Insert(ByRef Query As String) As Integer

    Insert = New System.Data.SqlClient.SqlCommand(Query, SqlCon).ExecuteNonQuery

    End Function

    I don't understand, the order is however single since it has been just created...  Can you help me?

    SqlCon is opened with ConnectionString = "MultipleActiveResultSets=True;User Id=sa;Initial Catalog=mydb;Data Source=SOLEIL;" and I use MSDE

    Dienstag, 15. November 2005 10:37
  • I'm not sure I understand the question, but as far as I'm aware, MARS is only available for SQL-2005 connections, at this stage.
    Freitag, 18. November 2005 08:03
  • If I understand, I must to close every time the connection for use a new query ? (Sorry for my very bad english ...)
    Freitag, 18. November 2005 12:18
  • 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).
    Freitag, 18. November 2005 12:45
  • Not quite... this is true for most cases, but if you are running sql-2005 and using ado.net-2.0 then you can have multiple active resultsets (MARS) open on a single connection... however you must set the switch in the connection string.

    I use this all the time with sql-2005 and ado.net-2.0 and, except for MS changing the MARS defaults from on to off (see above) it works just fine.
    Freitag, 18. November 2005 13:12
  • Ok, I understand ! But I need MSQE (I haven't money for buy the final version). Thanks for your help. Bye and good programmation !
    Freitag, 18. November 2005 13:15
  •  I made changes in my connection string as per your instruction. But, still it gives the  same error. I did same thing using Access as database using Oledb connection. It work well. Now i would like to do the same using SQL 2000. Please help me

    Dim con As SqlConnection

    Dim da As SqlDataAdapter

    Dim ds As New DataSet()

    Dim objcmd As SqlCommand

    Dim objDataReader As SqlDataReader

    Dim ConnectionString As String

    Dim STRSQL As String

    Dim rec_count1 As Integer

    ConnectionString="Data Source=Server07;Initial Catalog=mydata;Persist Security Info=True;MultipleActiveResultSets=True;User ID=xyz;password=1234"

    con = New SqlConnection(strcon)

    con.Open()

    STRSQL = "SELECT * From training_date WHERE course = '" & Trim(CourseList.Text) & "' AND Tr_date >='" & Now() & "'"

    objcmd = New SqlCommand(STRSQL, cn)

    objDataReader = objcmd.ExecuteReader()

    Do While objDataReader.Read() = True

    STRSQL = " Select * from training WHERE (TR_date ='" & CDate(objDataReader("tr_date")) & "' and course='" & objDataReader("course") & "')"

    da = New SqlDataAdapter(STRSQL, cn)

    da.Fill(ds, "Training") ' ---Here it gives error for There is already an open DataReader associated with this Command which must be closed first.

    rec_count1 = ds.Tables("Training").Rows.Count

    ''---'--Add tr_date to tr_date list if <5 student are ragistered for each date

    If rec_count1 < 5 Then

    tr_dateList.Items.Add(objDataReader("tr_date"))

    End If

    ds.Clear()

    Loop

    objDataReader.Close()

    Mittwoch, 12. April 2006 14:44
  • MultipleActiveResultSets requires that the provider support it.

    SQL2000 doesn't support MultipleActiveResultSets.

    This was implemented on SQL2005.

    Hope that helps.

    Mittwoch, 12. April 2006 21:15
  • Yes, you need to talk to a SQL 2005 server and use .NET 2.0 to get this feature.  The feature requires both client and server support to work.
    Mittwoch, 12. April 2006 22:53
  • Ok, but what about if the multiple connections have to be inside a clr procedure??

    SQL Server 2005 returns an Exception like that:

    System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

    Exactly the same as before and when I add "MultipleActiveResultSets=True" to the connection string it says:

    System.InvalidOperationException: The only additional connection string keyword that may be used when requesting the context connection is the Type System Version keyword.

    Mittwoch, 12. Juli 2006 19:38
  • Hi There,

    I hope someone can help me. I am running into a few bizarre things that I am hoping someone can help me understand.

    I get this error on one web server, but not the other: There is already an open DataReader associated with this Connection which must be closed first.

    I have a SQL2000 DB sitting on DBSERVER1 and my aspx files sitting on DEVELOP1, which is working fine. It has now come time to put this in to production, so we have setup a new site on PRODUCTION1 (setup with the same settings and files as DEVELOP1) and straight away I get the above error…

    Both servers have .net 2.0 redistributable installed, however, when an error is produced on both environments\servers it refers to ‘Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300’ at the bottom of the page.

    DEVELOP1 has the .net 2.0 sdk installed and the PRODUCTION1 doesn’t, I don’t know if this makes any difference?

    I have been reading up on this error (this page being the most helpful) and I can’t understand how this can be happening.

    Here is a snippet of the code:

     

    Function DisplayCurrentLogin() as boolean

         

                DisplayCurrentLogin = false

         

                'set declarations

                dim objConn                 as OleDBConnection

                dim objCmdMgr         as OledbCommand

                dim objDRMgr          as OleDbDataReader

                dim objCmdAdmin       as OledbCommand

                dim objDRAdmin        as OleDbDataReader

               

                dim strSQLMgr         as string

                dim strSQLAdmin       as string

                dim strConn                 as string

         

                dim strLogonUser as string = request.ServerVariables("LOGON_USER")

                strLogonUser = lcase(right(strLogonUser, (len(strLogonUser) - instr(strLogonUser, "\"))))

               

                strConn = Provider=SQLOLEDB;Data Source=dbserver1;Initial Catalog=dbname;User ID=uid;Password=pwd

               

                'set a new connection object           

                objConn = New OleDBConnection(strConn)

               

                'open db connection

                objConn.Open()

               

                'build query string

                strSQLMgr = "select top 1 pertitle + ' ' + perknownas + ' ' + persurname from staffdetails where domainlogin = '" & strLogonUser & "' order by persurname"

                strSQLAdmin = "select top 1 title + ' ' + knownas + ' ' + surname from authorisedusers where domainlogin = '" & strLogonUser & "' order by surname"

               

                'create a new db command object

                objCmdMgr = New OledbCommand(strSQLMgr, objConn)

                objCmdAdmin = New OledbCommand(strSQLAdmin, objConn)

               

                'perform db object tasks

                objDRMgr = objCmdMgr.ExecuteReader()

                objDRAdmin = objCmdAdmin.ExecuteReader()

               

                objDRMgr.read()

                objDRAdmin.read()

              

                if objDRMgr.hasrows then

                      lblHeaderPreCloseLogin.text = objDRMgr.item(0)

                      DisplayCurrentLogin = true

                     

                end if


                if objDRAdmin.hasrows then

                      lblHeaderPreCloseLogin.text = objDRAdmin.item(0)

                      DisplayCurrentLogin = true

                     

                end if

               

                'close db connection

                objConn.Close()

         

          End function

     

    If you require any more information regarding this, please let me know.

    Hope someone can provide me with some suggestions or ideas.

    Thanks,
    Nathan.

    Montag, 24. Juli 2006 14:25
  • We have the same problem but with 1.1net and sql server 2005. We are prefectly sure, after the series of debugging that our reader and connection is closed properly but we keep getting "There is already an open DataReader associated with this Command which must be closed first."

    Are there any found bugs for .net 1.1 and sql2005? We cannot use MARS as 1.1 provider doesn'deal with it.

    Mittwoch, 13. September 2006 08:48
  • Hey everybody,

    Use the Microsoft.Applicationblocks.Data.SqlHelper. Why are you straining yourself so much writing and creating datareaders and command objects?

    Freitag, 15. September 2006 11:03
  • I always thought that MARS was a Dot.Net-2.0 thing... i.e. Needs ADO-2.0

    Obviously, an appropriate provider must also be present... e.g. SQLServer2005.

    Samstag, 16. September 2006 03:43
  • To be more accurate, Mars is a Sql Server 2005 new feature. Some other servers like oracle also support it.
    Samstag, 16. September 2006 03:51
  • Hi all.

    Actually I experience the same exception when there is a slowdown on the web server. I use ASP.NET 2.0, MS SQL Server 2000. Moreover I do use Microsoft Enterprise Library 2.0 and its Data Application Block. All database operations are performed using either Database.ExecuteReader(DbCommand command, DbTransaction transaction) or Database.ExecuteReader(DbCommand command). So I'm sure that all DataReaders and Connections are closed properly. Transactions are closed and disposed properly as well. I've been trying to find out the root cause, but with no success so far... Any ideas on it? What might be the cause?

    Montag, 9. Oktober 2006 17:01
  • I had this problem before in .NET 1.1 and it has now recurred in 2.0

    I think there is an orphaned DataReader instance in the web server memory.

    This can occur when the data access code block encounters an error before the close method on the DataReader instance is called.

    I put this code in the 'finally' block of "try ... catch ... finally" in data access methods that use the DataReader,

     

    .

    .

    .

    If Not IsNothing(MyBase.DataReader) Then

    If Not MyBase.DataReader.IsClosed Then

    MyBase.DataReader.Close()

    End If

    End If

    .

    .

    .

     

    Unfortunately it did not prevent this condition from re-occurring.

    I think the only options are ...

     

    1. "Bounce" the web server to clear that orphaned object from memory.

    2. Wait for "garbage collection" to dispose of the instance. (don't hold your breath while waiting. .. Smile)

     

    I hope this helps.

     

    Raymond

     

    Montag, 9. April 2007 19:54
  • Hi,

     

     just close your reader after reading it, like:

     

    yourreadername.Close();

     

    it will work fine

     

     

    Bye.

     

    -------------------------------------

    - Saqib Jahangir

    ISF - Islamabad Software Factory

    Islamabad, Pakistan

    saqibsaifi@gmail.com

    Samstag, 28. April 2007 13:12
  • It has been covered a number of times in this thread, but to recap...

     

    MARS allows you to hold open Multiple readers with the same query.

     

    However, it requires the combination of .NET 2.0 and SQL-Server 2005 and the "MultipleActiveResultSets=True" argument in the connection string.

     

    Unless you have that combination then you will get an error if you attempt to execute a second reader with the same query.

    Samstag, 28. April 2007 14:27
  • I got this error on the line :

     

    cmd.ExecuteNonQuery();

     

    cmd being a command object. So it doesnt seem specific to datareaders. That command is clean, just constructed a few lines up, however there was another command on the same connection with a open datareader on it. Shouldn't the error message say  connection  instead of command ?

     

    Anyway putting 

    MultipleActiveResultSets=True

    on the connection string fixed it.

     

    Dienstag, 15. Mai 2007 21:27
  • Hi all,

      

        we are also experience the same problem, but still not find any solution, Actually we have not used any datareader in our code. Can anybody tell me how to solve this problem without changing the database Sql 2000 with .net framework 2.0.

     

    Thanks in advance.

    Mittwoch, 23. Mai 2007 07:53
  • I had the same problem when using 2 different data adapters with same connection. First data adapter was on the form and secon was on an user control class which is on the form. Problem disappeared when I moved the initialization of select command of the adapter else where than New method of user control. Notice that I have net.2 and sql server 2000 without MultipleActiveResultSets=True in connection string.

     

    Public Sub New()

    ' This call is required by the Windows Form Designer.

    InitializeComponent()

     

    ' Add any initialization after the InitializeComponent() call.

    sda.SelectCommand = New SqlCommand                                      ' >> when I moved these 2 lines to just before the sda.Fill(...) and worked OK!

    sda.SelectCommand.Connection = DBO.getDefaultConnection

    End Sub

     

    VS allready opens this connection in design time before I run the code and I guess problem has something to do with that.

    Mittwoch, 13. Juni 2007 19:48
  • Well, I have .Net Framework 2.0 and SqlServer2005 Installed on my server but still this MARS thing is not working. is there any other solution to this?

     

    Donnerstag, 14. Juni 2007 09:08
  • I'm using .NET 2.0, SQL Server 2005, and I have set MultipleActiveResultSets=True in my connection string, but I still encounter this exception when I load two images that use the same DAL select command to get the image path. Any suggestions?

     

    Donnerstag, 2. August 2007 01:29
  • I fixed this issue by ensuring that each DAL call was on a new object. Originally I was using a search helper class that kept a static readonly instance of the DAL, so multiple calls were attempted on the same Command object. Oops.

    Donnerstag, 2. August 2007 16:08
  • I am receiving this same error when trying to compile my code.  I am using SQL Server 2000, so MARS does not apply.  There are a series of numbers/letters that appear after the error.  Example: O6UJ9A0026CD.  Also, this error is repeated close to 100 times.  My questions are, can I use the number in the example I gave to find more information on the error?  Why would the error be repeated that many times with a slightly differnt code afterwards?  Thanks in advance.

     

    Mittwoch, 22. August 2007 14:59
  • Hm, i got the same problem to...
    My application using SQL Server 2000 with VS Net 2003 (.NET framework 2.0).
    How to avoid this error ?

    Thanks friends.
    Samstag, 27. Oktober 2007 05:39
  • you can make two connections. If you are using  data adapter then you have to dispose that. because internally data adapter uses data readers.
    To access multiple result sets on SQL Server 2000 using sqldatareaderobjects, a separate SqlConnection object must be used with each SqlCommand object.
    Freitag, 14. Dezember 2007 11:17
  • you can use CommmandBehaviour.CloseConnection. This will solve your problem.

    e.g.  mydatareader=cmd.ExecuteReader(CommmandBehaviour.CloseConnection);

    By doing so you can use more than one data reader simultaneously.
    Freitag, 14. Dezember 2007 11:26
  •  Spoof wrote:

    However, it requires the combination of .NET 2.0 and SQL-Server 2005 and the "MultipleActiveResultSets=True" argument in the connection string.

     

    Unless you have that combination then you will get an error if you attempt to execute a second reader with the same query.



    I have that combination and I am still getting the error.

    I'm explicitly closing all of my dataReaders and commands and I have set MARS to true (which is supposed to be the default if you don't set it in your connection string anyway).  I'm using .NET 2.0 and SQL-Server 2005.

    <connectionStrings>
        <add name="TestDB" connectionString="Data Source=localhost;Initial Catalog=TEST_DB;Integrated Security=True;MultipleActiveResultSets=true"
          providerName="System.Data.SqlClient" />
      </connectionStrings>
    Mittwoch, 23. April 2008 19:51
  • I also got the same problem. Tried a lot of stuff but the thing which works for me is - using  keyword.
    Just enclose your DBDataReader object in a using .

    Code Snippet

    using(DBDataReader dbReader = oCommand.ExecuteReader())
    {

    }



    regards

    sambhav sacheti
    http://dotnet-junk.blogspot.com
    Freitag, 25. April 2008 08:02
  • With SQL-2005 and .NET-2.0 - enabling MARS allows you to have multiple readers open with the same command... so if you are getting this error, then MARS is not enabled.

     

    The default for MARS is OFF - this was changed in RC1 of 2005 (see the early responses to this thread).

     

    You should perhaps check that your connectionstring is actually being applied, and that there are no other connections string also being applied.

     

    Samstag, 26. April 2008 15:08
  • The using statement simply does an implict dispose on the specified object.

     

    Hence, if using() fixes the problem, it's only becasue it's making sure that you are closing readers, and avoiding the need for MARS to be enabled in the first place - i.e. In your particular case the result is you do not have any MultipleActiveResultSets to deal with... but there are plenty of cases where that won't work as a solution.

    Samstag, 26. April 2008 15:19
  • Well, I understand why this helps some people out.
    There is one thing that confuses me. If I use a seperate connection for each Datareader it should work even without MARS.
    However this code example never works the question is why... I used an SQL 2005 Server.
    There is no difference if I use Pooling=false or MultipleActiveResultSets=True or both. Ok it differs in the exceptions I am getting.

    Code Snippet

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data.SqlClient;
    using System.Transactions;
    using System.Threading;

    namespace TestTransaktion
    {
        class Program
        {
            static void Main(string[] args)
            {
                try
                {
                    using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, TimeSpan.MaxValue))
                    {
                        Console.WriteLine("Check Transaction (RootStart): L:{0} D:{1}", Transaction.Current.TransactionInformation.LocalIdentifier, Transaction.Current.TransactionInformation.DistributedIdentifier);
                        for(int i = 0; i < 1000; ++i)
                        {
                            using (TransactionScope ts2 = new TransactionScope(TransactionScopeOption.RequiresNew))
                            {
                                Console.WriteLine("Check Transaction (InnerStart): L:{0} D:{1}", Transaction.Current.TransactionInformation.LocalIdentifier, Transaction.Current.TransactionInformation.DistributedIdentifier);
                                for (int j = 0; j < 10; ++j)
                                {
                                    Thread tmp = new Thread(worker3);
                                    tmp.IsBackground = true;
                                    tmp.Start(Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete));
                                    //ThreadPool.QueueUserWorkItem(worker3, Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete));
                                }
                                Console.WriteLine("Check Transaction (InnerEnd): L:{0} D:{1}", Transaction.Current.TransactionInformation.LocalIdentifier, Transaction.Current.TransactionInformation.DistributedIdentifier);
                                Console.WriteLine("About to complete the Innter Transaction");
                                ts2.Complete();
                            }
                        }
                        Console.WriteLine("Check Transaction (RootEnd): L:{0} D:{1}", Transaction.Current.TransactionInformation.LocalIdentifier, Transaction.Current.TransactionInformation.DistributedIdentifier);
                        Console.WriteLine("About to complete the main thread");
                        ts.Complete();
                    }
                    Console.WriteLine("Transaction Completed");
                }
                catch (Exception ex)
               {
                    Console.WriteLine("Top Catch");
                    Console.WriteLine(ex.ToString());
                }
                Console.WriteLine("Enter <Enter>");
                Console.ReadLine();
            }

            static void worker3(object ar)
            {
                Console.WriteLine("Thread: {0}", Thread.CurrentThread.ManagedThreadId);
                try
                {
                    DependentTransaction dtx = (DependentTransaction)ar;
                    using (TransactionScope ts = new TransactionScope(dtx))
                    {
                        //using (SqlConnection conn = new SqlConnection("Data Source=WKOEDEV01\\SQL2005;Initial Catalog=WKOBASE_CLONE;Persist Security Info=True;User ID=binreader;Password=readme2002;MultipleActiveResultSets=True;Pooling=False"))
                        //using (SqlConnection conn = new SqlConnection("Data Source=WKOEDEV01\\SQL2005;Initial Catalog=WKOBASE_CLONE;Persist Security Info=True;User ID=binreader;Password=readme2002"))
                        using (SqlConnection conn = new SqlConnection("Data Source=WKOEDEV01\\SQL2005;Initial Catalog=WKOBASE_CLONE;Persist Security Info=True;User ID=binreader;Password=readme2002;Pooling=False"))
                        {
                            conn.Open();
                            SqlCommand co = new SqlCommand("SELECT * FROM TEST", conn);
                            {
                                SqlDataReader r = co.ExecuteReader();
                                while (r.Read())
                                {
                                    //Console.WriteLine("Reader3: {0}, {1}", r.GetInt64(0), r.GetString(1));
                                }
                                r.Close();
                            }
                        }
                        ts.Complete();
                    }
                    dtx.Complete();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Worker3 Catch");
                    Console.WriteLine(ex.ToString());
                }
            }
        }
    }



    Dienstag, 29. April 2008 11:55
  • Many thanks - good information Surprise)

     

    Montag, 2. Juni 2008 10:22
  • Thanks Spoof!

     

    I had the same problem, in my work station I have MSDE and it occuerd me on error,

    so when I read your post, i checked it in the site who work with SQL S2005, and it really working fine..

     

    you saved my time..

     

     

    Donnerstag, 7. August 2008 08:12
  • A mySql Syntax error was causing the error for me while using MySQL open DataReader  and the IN Clause

     Re: There is already an open DataReader associated with this Command which must be closed first.

     

    The Syntax error for me dealt with the in clause.  I was changing an inclause from strings to integers and still had a few extra apostrophes left in it.

     

    Like so

    where PEEL_PK in ( ' " + userKeys + " ' )

     

    Once I saw the ' and removed them the error went away go figure?!

    Mittwoch, 22. Oktober 2008 22:42
  • Hello

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

    I have spend few hours before i have found reason why it happens.

    First You should to know what I have try to do:

    I have simple class in c# which use MySqlDataAdapter to return dataset from MySql DB. When I try to call store procedure ,  I have this error from time to time, but why ?

    There is no solution in google resource, so I have try to fix it by my self, finally I found it.

    When Your store procedure returns 0 rows in table You will have this error.

    I know this is stupid but is true.

    Good luck !

    Paweł Mucha
    • Als Antwort vorgeschlagen S.Alemi Sonntag, 29. März 2009 05:51
    Dienstag, 9. Dezember 2008 16:17
  • The problem (with both this issue and many of the posts in this thread) is a confusion of Commands with Connections.

    My experience with this issue comes from recursive algorithms such as populating a tree view from a self-referential table. I get that error message without MARS on, even though each Command only has one data reader associated with it.

    The error message should say There is already an open DataReader associated with this Command or Connection which must be closed first., because without MARS enabled, you can only have one data reader running through any given connection at any given time (you can have as many commands associated with the connection, but only one can be active.)

    This can be frustrating to troubleshoot, because the DataReader is linked to the Connection only through the Command and the error message as written doesn't point you toward the connection.

    Mittwoch, 8. April 2009 19:26
  • I've been troubleshooting an intermittent record lock and am trying to determine if it's related to this behavior (1 active reader per connection at a time).

    We're using Jet 4.0 as the provider (System.Data.OleDb) with an Access 2000 mdb using ADO.Net 2.0. No chance of MARS here.

    There are many routines that open a reader on a connection, then for each iteration of .Read() an update command is executed on the same connection. In most cases this update is on the same table being read. In one case 2 readers are open on the same connection (one in a nested loop inside the other) with an update command being executed on each pass.

    From everything I've read in .Net documentation and elsewhere (including this thread) this should not be possible!

    If the same OleDbCommand object is reused to create a second reader, the "There is already an open DataReader associated with this Command which must be closed first. " exception occurs. But if a second command is created on the same connection, no exception occurs and both readers retrieve all data without any problems.

    I've already alerted others to the issue and we will be changing this behavior to create separate connections for each reader AND any concurrent update commands, but does anyone know if this could be causing intermittent record locks in later database access code? We have verified all IDisposable objects are wrapped in a using or get a Dispose called in a finally block.

    Thanks!


    Mittwoch, 1. Juli 2009 14:31
  • Thank you very much for this post. Solved my very hard to debug MS ADO problem.
    Montag, 16. November 2009 17:57
  • Thanks for the help.  I had not realized this had changed.
    Freitag, 3. September 2010 16:39
  • thank you Sir, this is help for me
    Sonntag, 12. Dezember 2010 15:09
  • Thank you,

    this statment solved the problem.

    Freitag, 17. Dezember 2010 13:27
  • Old post but google finds it first, so I decided to reply to it.

    For my case, this article helped: 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

    I did not have to change my connection string.

    Dienstag, 28. Dezember 2010 14:22
  • Hi,

     

    this worked for me. THANK YOU THANK YOU THANK YOU.


    ndramos
    Sonntag, 14. August 2011 16:28
  • Thanks to your answer, its works fine to me. 
    Donnerstag, 13. Oktober 2011 09:40
  • VB.Net 2005 and SqlServer2000 : 

    Exception : 

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

    Cause :

    i have ComboBox that populated by record content from sql database table 

    the combobox is filled at (form_load) by SqlDataReader (DR)

    the event (SelectedIndexChanged) searches the table and pull out the content as search result into the controls on the form By SqlDataReader (DR)

    Navigate through the table using (First - Next - Prev. - Last) using SqlDataReader .

    Normally, every time i navigate it stops at the first result (ComboBox) because it will change it triggering the (SelectedIndexChanged) Event .

    Solution :

    http://adonetaccess2003.blogspot.com/2011/12/solution-there-is-already-open.html

    It's a work around but useful .


    http://adonetaccess2003.blogspot.com http://vb6access2003.blogspot.com http://ahmedsamir.awardspace.com
    Freitag, 9. Dezember 2011 15:32
  • Use different Open and close sql connection names for each of the datareader. This will solve the issue.

    As you are using same open and close database connection for multiple datareader which is not supported by sql server in vb.net
    Freitag, 30. November 2012 07:36
  • Use different Open and close sql connection names for each of the datareader. This will solve the issue.

    As you are using same open and close database connection for multiple datareader which is not supported by sql server in vb.net
    Freitag, 30. November 2012 07:36
  • adding the ' MultipleActiveResultSets=True'  to connection string solves my problem 

    Thanks ..

    Samstag, 11. Mai 2013 06:52
  • i changed the added the phrase MultipleActiveResultSets=True to my connection string

    then also am getting same error

    if any one the solution, assist me

    Thanks and Regards,

    dhayanand

    Freitag, 31. Mai 2013 10:41
  • This is due to a change in the default setting for MARs.  It used to be on by default and we changed it to off by default post RC1.  So just change your connection string to add it back (add MultipleActiveResultSets=True to connection string).

    This is the perfect answer, also for me, after switching , temporarily, from sql server compact , to sql server express 2012, localdb.
    Mittwoch, 5. Juni 2013 10:25
  • This is due to a change in the default setting for MARs.  It used to be on by default and we changed it to off by default post RC1.  So just change your connection string to add it back (add MultipleActiveResultSets=True to connection string).

    GODLIKE!
    Montag, 3. Februar 2014 15:45