SQLDataReader Bug

  • Thread starter Thread starter Srinivas
  • Start date Start date
S

Srinivas

Hi

I am Using .net Framework1.1 and getting the Error
"There is already an open DataReader associated with this
Connection which must be closed first."

this error is coming randomly i.e. Not always only some
times.

I found the Fix 319345 in SP2 of .net Frame Work 1.0
319345 FIX: Thread Abort During SqlCommand.ExecuteReader
Corrupts SqlConnection Pool

can anyone suggest me how to resolve it.

Thanks
Srinivas
 
Each connection can handle one operation at a time. If the DataReader does
not complete rowset population for _each_ resultset generated by the query
(for any reason), the connection is blocked from any further operations. I
suspect that something is (occasionally) going wrong with your query that
might be causing SQL Server to drop the connection at its end. This corrupts
the pool and orphans the connection. (This is fixed in the next release). If
this is happening, the only thing you can do is try to close the connection
and start the operation over again--and including code to anticipate that a
bad connection might be assigned to you when the connection is opened (so
you try again until you get a good connection).

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Put everything in try/catch/finally...if you're handling the sqlconnection
object close that in the finally section too.

dim oreader as sqldatareader
try
oreader = getdatareader <- your custom method for getting a datareader
ie don't reinvent the wheel on all your pages
while oreader.read
'do whatever with your data...
next
'I like to close it immediately
oreader.read
catch
'your error checking...if needed.
finally
'this will always fire...
If not oreader is nothing then
If not oreader.isClosed then
oreader.close()
End if
End if
 
Hi Williams,

Thanks for your suggestion, we had almost replaced the
Code of Using Datareader to dataset except for fetching
Blob and Memo(text)data and reopening the Connection
whenever the Connection get Closed or Broken in the
Connection's Statechange event.

this resulted some success say about 70% of our problems
solved. But i still getting this error some times.

Some times I got "Unknown Error" followed by corresponding
Errors ( not all), like
1) ExecuteReader requires an open and available
Connection. The connection's current state is Closed
2) Object reference not set to an intance of an object.
3)The Connection string has not been initialized.

The case in Our Application is we are Using 2 Connections.
One connection is kept on thread which executes every 30
seconds to fetch some data and will get closed immediately.
The Second Connection is being used throught the
Application scope. it is a global Connection will not be
closed until the Application get closed.

Alternatively I developed a component which Handles the
complete DataAccess Functionality. ehich will close the
Connection immediately after it fetch the required data.

until the implementation of that component, i would like
Your Advise on this issue to resolve my issues.

Srinivas
 
Back
Top