Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > There is already an open DataReader associated with this Connection which must be closed first

Reply
Thread Tools

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

 
 
Rob Nicholson
Guest
Posts: n/a
 
      09-23-2005
I'm starting to worry a bit now. We're getting the above error when two
users hit the same database/page on an ASP.NET application using ADO.NET,
talking to a SQL 7 server. The error is perfectly repeatable But this
should help!

The error is occurring inside ExecuteReader which uses a DataReader
internally.

Here are some things that I'm pretty sure it's *NOT*:

It's not because our data readers are not being closed - they are. If they
weren't being closed, then the same error would occur in single user
operation. It doesn't - it's only when two users (threads) are running at
once.

It's not connection pooling as we've turned that off using Pooling=False in
the SqlConnection connection string. I've verified it really is off by
watching connections open & close manually in SQL Enterprise manager.

The two threads are not (AFAIK) sharing the same connection object. The
SqlConnection object is not in a shared or static variable - it's stored as
a private variable within one of our classes (called MSSQL) in a variable
called m_Connection. This m_Connection variable is created each time we open
a connection using m_Connection = New SqlConnection(ConnString), opened,
used (ExecuteScalar) closed and then destroyed using m_Connection = nothing.
The instance of MSSQL is stored in the session cache but I've verified that
the two users/threads are indeed using their own instance of MSSQL and
therefore their own instance of m_Connection. The two instances of
m_Connection will have the same connection string (SQL login used, not
Windows authentication).

There are no exceptions/error conditions occuring elsewhere apart from this
final error.

I don't think it's the connection/data reader closing - I've triple checked
all open/close pairs and the close is always within a Try...Catch with the
connection closed in the Finally section.

The error never occurs in our own use of data readers, only from within
ExecuteScalar. Hmm, I guess I could write my own version of ExecuteScaler...

So, pretty flummoxed at the moment. I willing to try any suggestions!

Cheers, Rob.


 
Reply With Quote
 
 
 
 
Scott M.
Guest
Posts: n/a
 
      09-23-2005
Does it happen in EVERY circumstance? It just sounds like your connected
access is being hit twice simultaneously.


"Rob Nicholson" <(E-Mail Removed)> wrote in message
news:uz%(E-Mail Removed)...
> I'm starting to worry a bit now. We're getting the above error when two
> users hit the same database/page on an ASP.NET application using ADO.NET,
> talking to a SQL 7 server. The error is perfectly repeatable But this
> should help!
>
> The error is occurring inside ExecuteReader which uses a DataReader
> internally.
>
> Here are some things that I'm pretty sure it's *NOT*:
>
> It's not because our data readers are not being closed - they are. If they
> weren't being closed, then the same error would occur in single user
> operation. It doesn't - it's only when two users (threads) are running at
> once.
>
> It's not connection pooling as we've turned that off using Pooling=False
> in
> the SqlConnection connection string. I've verified it really is off by
> watching connections open & close manually in SQL Enterprise manager.
>
> The two threads are not (AFAIK) sharing the same connection object. The
> SqlConnection object is not in a shared or static variable - it's stored
> as
> a private variable within one of our classes (called MSSQL) in a variable
> called m_Connection. This m_Connection variable is created each time we
> open
> a connection using m_Connection = New SqlConnection(ConnString), opened,
> used (ExecuteScalar) closed and then destroyed using m_Connection =
> nothing.
> The instance of MSSQL is stored in the session cache but I've verified
> that
> the two users/threads are indeed using their own instance of MSSQL and
> therefore their own instance of m_Connection. The two instances of
> m_Connection will have the same connection string (SQL login used, not
> Windows authentication).
>
> There are no exceptions/error conditions occuring elsewhere apart from
> this
> final error.
>
> I don't think it's the connection/data reader closing - I've triple
> checked
> all open/close pairs and the close is always within a Try...Catch with the
> connection closed in the Finally section.
>
> The error never occurs in our own use of data readers, only from within
> ExecuteScalar. Hmm, I guess I could write my own version of
> ExecuteScaler...
>
> So, pretty flummoxed at the moment. I willing to try any suggestions!
>
> Cheers, Rob.
>
>



 
Reply With Quote
 
 
 
 
S. Justin Gengo
Guest
Posts: n/a
 
      09-23-2005
Are any of the objects involved being stored in Application variables?

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"Rob Nicholson" <(E-Mail Removed)> wrote in message
news:uz%(E-Mail Removed)...
> I'm starting to worry a bit now. We're getting the above error when two
> users hit the same database/page on an ASP.NET application using ADO.NET,
> talking to a SQL 7 server. The error is perfectly repeatable But this
> should help!
>
> The error is occurring inside ExecuteReader which uses a DataReader
> internally.
>
> Here are some things that I'm pretty sure it's *NOT*:
>
> It's not because our data readers are not being closed - they are. If they
> weren't being closed, then the same error would occur in single user
> operation. It doesn't - it's only when two users (threads) are running at
> once.
>
> It's not connection pooling as we've turned that off using Pooling=False
> in
> the SqlConnection connection string. I've verified it really is off by
> watching connections open & close manually in SQL Enterprise manager.
>
> The two threads are not (AFAIK) sharing the same connection object. The
> SqlConnection object is not in a shared or static variable - it's stored
> as
> a private variable within one of our classes (called MSSQL) in a variable
> called m_Connection. This m_Connection variable is created each time we
> open
> a connection using m_Connection = New SqlConnection(ConnString), opened,
> used (ExecuteScalar) closed and then destroyed using m_Connection =
> nothing.
> The instance of MSSQL is stored in the session cache but I've verified
> that
> the two users/threads are indeed using their own instance of MSSQL and
> therefore their own instance of m_Connection. The two instances of
> m_Connection will have the same connection string (SQL login used, not
> Windows authentication).
>
> There are no exceptions/error conditions occuring elsewhere apart from
> this
> final error.
>
> I don't think it's the connection/data reader closing - I've triple
> checked
> all open/close pairs and the close is always within a Try...Catch with the
> connection closed in the Finally section.
>
> The error never occurs in our own use of data readers, only from within
> ExecuteScalar. Hmm, I guess I could write my own version of
> ExecuteScaler...
>
> So, pretty flummoxed at the moment. I willing to try any suggestions!
>
> Cheers, Rob.
>
>



 
Reply With Quote
 
Bruce Barker
Guest
Posts: n/a
 
      09-23-2005
sql connections (until yukon), only support one request at a time. ther
error mens a second request was made on the connection without the previous
request reading all the result sets.

ExecuteScaler only reads the first row of the first result set, it does not
clear the results, you still need to close the underlying connection.

also be sure your command objects are not shared - you will get the same
results.

if you are getting this with pooling on, then you are definitely sharing the
data between thread in your code.

note: be sure you are not using fields in a vb module, as these are shared
across threads even if private. public/private just controls varible
accessibilty not sharing.

Public Module Test
private myData as myObj = new myObj ' shared across threads
End Module

-- bruce (sqlwork.com)


"Rob Nicholson" <(E-Mail Removed)> wrote in message
news:uz%(E-Mail Removed)...
> I'm starting to worry a bit now. We're getting the above error when two
> users hit the same database/page on an ASP.NET application using ADO.NET,
> talking to a SQL 7 server. The error is perfectly repeatable But this
> should help!
>
> The error is occurring inside ExecuteReader which uses a DataReader
> internally.
>
> Here are some things that I'm pretty sure it's *NOT*:
>
> It's not because our data readers are not being closed - they are. If they
> weren't being closed, then the same error would occur in single user
> operation. It doesn't - it's only when two users (threads) are running at
> once.
>
> It's not connection pooling as we've turned that off using Pooling=False
> in
> the SqlConnection connection string. I've verified it really is off by
> watching connections open & close manually in SQL Enterprise manager.
>
> The two threads are not (AFAIK) sharing the same connection object. The
> SqlConnection object is not in a shared or static variable - it's stored
> as
> a private variable within one of our classes (called MSSQL) in a variable
> called m_Connection. This m_Connection variable is created each time we
> open
> a connection using m_Connection = New SqlConnection(ConnString), opened,
> used (ExecuteScalar) closed and then destroyed using m_Connection =
> nothing.
> The instance of MSSQL is stored in the session cache but I've verified
> that
> the two users/threads are indeed using their own instance of MSSQL and
> therefore their own instance of m_Connection. The two instances of
> m_Connection will have the same connection string (SQL login used, not
> Windows authentication).
>
> There are no exceptions/error conditions occuring elsewhere apart from
> this
> final error.
>
> I don't think it's the connection/data reader closing - I've triple
> checked
> all open/close pairs and the close is always within a Try...Catch with the
> connection closed in the Finally section.
>
> The error never occurs in our own use of data readers, only from within
> ExecuteScalar. Hmm, I guess I could write my own version of
> ExecuteScaler...
>
> So, pretty flummoxed at the moment. I willing to try any suggestions!
>
> Cheers, Rob.
>
>



 
Reply With Quote
 
William \(Bill\) Vaughn
Guest
Posts: n/a
 
      09-23-2005
Bruce is on to something here. If you don't close the DataReader (even with
an ExecuteScalar), the pending rowset will not be flushed until you do (or
close the connection). This can take some time if the query returns a large
rowset or multiple resultsets. If you try to reuse the DataReader before
this is done, you'll get this exception.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


"Bruce Barker" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> sql connections (until yukon), only support one request at a time. ther
> error mens a second request was made on the connection without the
> previous request reading all the result sets.
>
> ExecuteScaler only reads the first row of the first result set, it does
> not clear the results, you still need to close the underlying connection.
>
> also be sure your command objects are not shared - you will get the same
> results.
>
> if you are getting this with pooling on, then you are definitely sharing
> the data between thread in your code.
>
> note: be sure you are not using fields in a vb module, as these are shared
> across threads even if private. public/private just controls varible
> accessibilty not sharing.
>
> Public Module Test
> private myData as myObj = new myObj ' shared across threads
> End Module
>
> -- bruce (sqlwork.com)
>
>
> "Rob Nicholson" <(E-Mail Removed)> wrote in message
> news:uz%(E-Mail Removed)...
>> I'm starting to worry a bit now. We're getting the above error when two
>> users hit the same database/page on an ASP.NET application using ADO.NET,
>> talking to a SQL 7 server. The error is perfectly repeatable But this
>> should help!
>>
>> The error is occurring inside ExecuteReader which uses a DataReader
>> internally.
>>
>> Here are some things that I'm pretty sure it's *NOT*:
>>
>> It's not because our data readers are not being closed - they are. If
>> they
>> weren't being closed, then the same error would occur in single user
>> operation. It doesn't - it's only when two users (threads) are running at
>> once.
>>
>> It's not connection pooling as we've turned that off using Pooling=False
>> in
>> the SqlConnection connection string. I've verified it really is off by
>> watching connections open & close manually in SQL Enterprise manager.
>>
>> The two threads are not (AFAIK) sharing the same connection object. The
>> SqlConnection object is not in a shared or static variable - it's stored
>> as
>> a private variable within one of our classes (called MSSQL) in a variable
>> called m_Connection. This m_Connection variable is created each time we
>> open
>> a connection using m_Connection = New SqlConnection(ConnString), opened,
>> used (ExecuteScalar) closed and then destroyed using m_Connection =
>> nothing.
>> The instance of MSSQL is stored in the session cache but I've verified
>> that
>> the two users/threads are indeed using their own instance of MSSQL and
>> therefore their own instance of m_Connection. The two instances of
>> m_Connection will have the same connection string (SQL login used, not
>> Windows authentication).
>>
>> There are no exceptions/error conditions occuring elsewhere apart from
>> this
>> final error.
>>
>> I don't think it's the connection/data reader closing - I've triple
>> checked
>> all open/close pairs and the close is always within a Try...Catch with
>> the
>> connection closed in the Finally section.
>>
>> The error never occurs in our own use of data readers, only from within
>> ExecuteScalar. Hmm, I guess I could write my own version of
>> ExecuteScaler...
>>
>> So, pretty flummoxed at the moment. I willing to try any suggestions!
>>
>> Cheers, Rob.
>>
>>

>
>



 
Reply With Quote
 
Rob Nicholson
Guest
Posts: n/a
 
      09-24-2005
> Does it happen in EVERY circumstance? It just sounds like your connected
> access is being hit twice simultaneously.


Yes, pretty much. When the two users hit the "Go" button, it always fails at
the same line.

Cheers, Rob.


 
Reply With Quote
 
Rob Nicholson
Guest
Posts: n/a
 
      09-24-2005
> Are any of the objects involved being stored in Application variables?

No, session used throughout.

Cheers, Rob.


 
Reply With Quote
 
Rob Nicholson
Guest
Posts: n/a
 
      09-24-2005
> sql connections (until yukon), only support one request at a time. ther
> error mens a second request was made on the connection without the

previous
> request reading all the result sets.


Hi Bruce,

Just to clarify this, this means one "SqlConnection" object can only handle
one request at once. I'm aware of this issue hence the reason I said I'm
pretty sure we're closing the data reader and connections each time (I've
countred the opens and closes).

> ExecuteScaler only reads the first row of the first result set, it does

not
> clear the results, you still need to close the underlying connection.


Yes, we do that.

> also be sure your command objects are not shared - you will get the same
> results.


Yup, not shared - created each time within a non-shared class/object.

> if you are getting this with pooling on, then you are definitely sharing

the
> data between thread in your code.


Nope, occurs with pooling turned off.

> note: be sure you are not using fields in a vb module, as these are shared
> across threads even if private. public/private just controls varible
> accessibilty not sharing.


Not sure what you mean by fields?

> Public Module Test
> private myData as myObj = new myObj ' shared across threads
> End Module


Nope, nothing like this anyway. The only thing we do have is code like this:

Readonly Property SomePointer As SomeObject
Get
Static CachedSomePointer As SomeObject
If CachedSomePointer Is Nothing Then
CachedSomePointer = GetThePointerFromSomwhere()
End If
Return CachedSomePointer
End Get
End Property

Cheers, Rob.


 
Reply With Quote
 
Rob Nicholson
Guest
Posts: n/a
 
      09-24-2005
> Bruce is on to something here. If you don't close the DataReader (even
with
> an ExecuteScalar), the pending rowset will not be flushed until you do (or


We do close the data reader I'm pretty sure. The basic problem is I think
the datareader is being closed. If it wasn't then the error would occur when
a single user tried the operation. Therefore, it looks like *somehow* the
two threads are sharing each other's connections. But I'm 99% sure they are
not sharing the same connection object.

Cheers, Rob.


 
Reply With Quote
 
Rob Nicholson
Guest
Posts: n/a
 
      09-24-2005
> Does it happen in EVERY circumstance? It just sounds like your connected
> access is being hit twice simultaneously.


I realised that I can set-up a test environment that should repeat this by
simply a loop into a page_load that simply executes 10,000 ExecuteScaler
statements:

For i As Integer = 1 To 10000
Dim NumPeople As Integer =
CIMS_App.DataServer.ExecuteScaler(SQL)
Next

The SQL being executed is Select Count(*) From People. The source for
ExecuteScaler, OpenConnnection and CloseConnection are included below. No
parameters are being passed in this example so that bit can be ignored.

Now this is where something strange happened. I ran the above code from
within VS 2003 and it crashes with "SQL Server does not exist or access
denied" error. I think I need to work out why this is happening first! Okay,
so firing off 10,000 open connections, select and close connection isn't
something that would normally happen but it shouldn't crash...

Later... if the loop is changed to this:

CIMS_App.DataServer.OpenConnection()
For i As Integer = 1 To 10000
Dim NumPeople As Integer =
CIMS_App.DataServer.ExecuteScaler(SQL)
Next
CIMS_App.DataServer.CloseConnection()

The error doesn't occur. It's the rapid opening and closing of the
connection that's crashing - SQL 7 box BTW.

Cheers, Rob.

[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.SqlInternalConnection.OpenAn dLogin()
System.Data.SqlClient.SqlInternalConnection..ctor( SqlConnection
connection, SqlConnectionString connectionOptions)
System.Data.SqlClient.SqlConnection.Open()
Granite.MSSQL.OpenConnection() in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:37 9

[GException: Unable to open connection to INFORMED02]
Granite.MSSQL.OpenConnection() in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:38 1
Granite.MSSQL.ExecuteScaler(String SQL, SQL_Parameter[] Parameters) in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:50 1

[GException: Unable to load data from INFORMED02 server.]
Granite.MSSQL.ThrowOpenException(Exception ex, String SQL) in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:45 1
Granite.MSSQL.ExecuteScaler(String SQL, SQL_Parameter[] Parameters) in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:53 3
CIMS.DefaultPage.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\CIMS_Dev\Default.aspx.vb:78
System.EventHandler.Invoke(Object sender, EventArgs e) +0
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()

Here's the code:

Public Overrides Function ExecuteScaler(ByVal SQL As String, ByVal
ParamArray Parameters() As SQL_Parameter) As Object
' Trap the database operation.
Dim AlreadyOpen As Boolean
Try
' Open the connection.
AlreadyOpen = OpenConnection()
' Create SQL command.
Dim SQL_Command As New SqlCommand(SQL, m_Connection)
' Add SQL parameters.
For Each SQL_Parameter As SQL_Parameter In Parameters
Dim p As SqlParameter
Dim Value As Object = SQL_Parameter.Value
If Value.GetType Is GetType(System.Byte()) Then
Dim Data() As Byte = Value
p = New SqlParameter(SQL_Parameter.Name,
SqlDbType.Image, Data.Length, ParameterDirection.Input, False, 0, 0,
Nothing, DataRowVersion.Current, Data)
Else
Throw New Exception("MSSQL.ExecuteScalar can't handle "
& Value.GetType.FullName & " data types!")
End If
SQL_Command.Parameters.Add(p)
Next
' Add transaction if enabled.
If Not m_Trans Is Nothing Then
SQL_Command.Transaction = m_Trans
End If
' Execute the command.
ExecuteScaler = SQL_Command.ExecuteScalar
Catch ex As Exception
ThrowOpenException(ex, SQL)
Finally
' Close the connection.
If Not AlreadyOpen Then
CloseConnection()
End If
End Try
End Function

Public Overrides Function OpenConnection() As Boolean
If m_Connection Is Nothing Then
m_Connection = New SqlConnection(ConnectionString)
End If
If m_Connection.State = ConnectionState.Closed Then
Try
m_Connection.Open()
Catch ex As Exception
Throw New GException("Unable to open connection to " &
m_DataLink.ServerName, GraniteErrors.Error0016, ex)
End Try
Else
OpenConnection = True
End If
End Function

Public Overrides Sub CloseConnection()
If Not m_Connection Is Nothing Then
m_Connection.Close()
m_Connection = Nothing
End If
End Sub


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Python Psycopg2 - Interface Error : Connection already closed knobbyknees Python 1 03-28-2010 09:43 PM
already an open DataReader associated with this Command which must be closed first Bart ASP .Net 13 03-28-2007 10:12 PM
DataReader Already Open rn5a@rediffmail.com ASP .Net 2 11-25-2006 12:46 AM
InvalidOperationException: There is already an open DataReader ass =?Utf-8?B?R3JhbnQ=?= ASP .Net 2 09-13-2006 10:54 AM
There is already an open DataReader associated with this Connection James Brown ASP .Net 1 06-26-2003 05:56 PM



Advertisments