We are having a problem with mysterious open data readers appearing
where they shouldn't.  Our program, written in VB for ASP.NET, is
quite extensive but here's a simplified description.  A connection is
opened, and a transaction is begun.  A series of things can occur,
including reads (using the DataReader), inserts, updates, and deletes.
 Then the transaction is either committed or rolled back.  There are
no datareaders inside of this transaction that are not closed after
using.
 
The problem is that sometimes (and only under load...not even load
that should really stress the servers, but it never appears on a
desktop when being tested by one person) we get an error that says
"Server Error in '/' Application.  There is already an open DataReader
associated with this Connection which must be closed first."   Since
this doesn't happen under light load (and never while debugging
either) we've been unable to figure it out.  It can occur at any spot
where we're trying to read from the database, but only perhaps 1 time
in 10 under load.
 
In the connection string for the database (which we have in the
web.config) we use Pooling=false.
 
Is there anyone out there that has had this problem (or anything
similar) who can give us a solution?  We have to use the transaction,
so we can't be opening and closing the connection after every read,
but we did move a lot of our reads out to another connection (and we
open and close that one all the time to decrease the errors).  Any
solutions that allow us to continue using the transaction will be
welcomed.

---

Request for Question Clarification by theta-ga on 22 Jan 2005 03:01 PST

Hi jgruber-ga,
  A few more details about the issue being faced by you would enable
us researchers to more effectively search for a solution. Could you
please answer the following questions:
  - What version of .Net are you using? 1.0 or 1.1? The issue you
describe seems to be a known issue for applications written in .Net
1.0.
  - What database are you using? For e.g., SQLServer. MySQL etc.
  - Could you provide the stack trace for this error? It should be
displayed on the error page you get.
 
Thanks,
Theta-ga
:)

---

Clarification of Question by jgruber-ga on 28 Jan 2005 09:48 PST

Please see cronenwett-ga's clarifying comment below.  From the stack
trace it is apparent that we're using .NET v. 1.1

The database is MS SQL server 2000

And the stack trace is available in cronenwett-ga's comment.

check if you have shared your connection variable! if so make it as a
private variable. Are you using threading in your application? if that
is true then you might want to consider looking for thread
synchronization.

Have Fun!
Pratap

Hi, I'm Liz, the original question is from my boss.  

We are running 1.0 and 1.1 side by side, and I've just looked into
forcing it to use 1.1 using the ISAPI filter.  I was curious where you
found this to be a "known issue" as I have been looking for an answer
for a bit.  I believe the info at the bottom of the stack trace shows
it using 1.1, but I can't always cause the error on
demand...especially when I need it.

The database is Microsoft SQL Server Enterprise Edition version 8.00.760 SP3.


Stack Trace:


[InvalidOperationException: There is already an open DataReader
associated with this Connection which must be closed first.]
   System.Data.SqlClient.SqlCommand.ValidateCommand(String method,
Boolean executing) +292
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +91
   DLClocal.FileClass.SubmitChanges(Boolean OverrideWarnings, Boolean
TopLevel) in C:\Documents and
Settings\cronenwett\VSWebCache\WEB1\Library\FileObjects\FileClass.vb:7008
   DLClocal.Comments.cmd_UpdatePage(Object sender, CommandEventArgs e)
in C:\Documents and Settings\cronenwett\VSWebCache\WEB1\FileDetail\Comments.aspx.vb:385
   System.Web.UI.WebControls.ImageButton.OnCommand(CommandEventArgs e) +110
   System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +127
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1277

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573;
ASP.NET Version:1.1.4322.573



The line it dies on is indeed trying to open a data reader.  The error
is intermittent, if I reload a page 20 times I might get the error on
time 2, 7, and 12 today and on 5, 6, 7, and 17 tomorrow.  Errors only
happen under load (though not high load, spread across 4 CPUs it stays
in the teens in % utilization, and pagefile usage is less than 2GB,
with 2GB of memory).  Server is rebooted daily in the early am.  I am
stumped.

I used to get the same error and I had mysql database and ByteFX sql client.

The only problem here is DataReader doesnt close automatically. The
garbage collector doesnt close or terminate objects instantly as it
earlier used to do in ASP/vb script.

Earlier COM objects used to terminate as soon as it goes out of scope.
And even if you dont close connection it used to get closed
automatically.

But in .NET it doesnt work this way, you need to close the DataReader
and Command after you have used it. So you will need to rewrite your
source files and close each and every datareader you have opened after
you have used.

There is also possibility that if when you are using DataReader and if
exception is thrown and if you have forgotten to close it in finally
or catch. Then also it is possible.

- Akash Kava
Our free IMAP/POP3 service at http://www.evenmail.com

DataReaders need to be closed explicitly.  See the code below for a
good way to do this:

System.Data.SqlClient.SqlConnection connection;
// Create connection and set properties
System.Data.SqlClient.SqlDataReader dataReader;
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
command.Connection = connection;
// Set command properties
try
{
  dataReader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
finally
{
  if (dataReader != null)
    dataReader.Close();
}

Here's a good article that may help you:

http://www.dotnet-webhosting.com/ado-dotnet/multiple-datareaders.aspx

G,