Error: ExecuteReader requires an open and available Connection.

Posted on 2004-11-17
Medium Priority
Last Modified: 2012-05-05

I have a C# online application where users logon to the app. Currently, all users share the same single connection to the database, and I use C# threading. The problem this is causing is when a call or update takes extra time, it causes the datareader to fail. Such as the case when one person is downloading a file while someone else is trying to run a more simple select. See the activity log excerpt below:

>select * from vw_appl_doc where application_id = 709 and file_id = 70819

>SELECT * from appl_trans WHERE applicant_id=70948  
>ExecuteReader requires an open and available Connection. The connection's current state is Open, Executing.

I am also getting other errors I believe due to the same issue. Such as:
ERROR [08002] [Microsoft][ODBC Driver Manager] Connection in use

Is there something I can do to prohibit these issues? My database is ASA6. Overall my pages use DataReaders which I dump into DataTables. I have been very careful to close all opened datareaders after use.

 Is there a way to solve these problems and keep a single database connection???

Thanks In Advance!!

Question by:PartnersIntl

Accepted Solution

sendmeblah earned 672 total points
ID: 12610642
Have all the users of the database use the same connection string and that will allow connection pooling.  If that is not enough then try raising the command timeout of the datareader.
LVL 96

Expert Comment

by:Bob Learned
ID: 12611273
Yes, for scaleability you should seriously looking into connection pooling.

LVL 20

Assisted Solution

ihenry earned 664 total points
ID: 12615967
>> Is there a way to solve these problems and keep a single database connection???
Are you sure you are doing it the correct way?

Excerpt from MSDN about OdcbDataReader
While the OdbcDataReader is in use, the associated OdbcConnection is busy serving the OdbcDataReader, and no other operations can be performed on the OdbcConnection other than closing it. This is the case until the Close method of the OdbcDataReader is called. For example, you cannot retrieve output parameters until after you call Close.

LVL 37

Assisted Solution

gregoryyoung earned 664 total points
ID: 12621525
you are getting these issues because you are trying to access the same connection from multiple threads.

There are two ways to fix this.

1) Do it right and use some form of connection pooling (either your own or use the frameworks)
2) Put on a bandaid and make the issuing of a sql statement a critical section (this will hurt your scalability but will alleviate the problem immeditatly).

Assuming that you are using a method to issue a SQL statement which you appear to be doing ...

wrap your code using the connection with a lock() { } this will define it as a critical section.

Given that it seems you have a production level app that is not getting very heavy use I would suggest the bandaid approach as an immediate solution. I would then head back and fix the real issue of only using a single connection.

put a "Beginning Critical Section" and Ending critical section for testing ...

you should always see this pattern
>Beginning Critical Section
>select * from vw_appl_doc where application_id = 709 and file_id = 70819
>Ending Critical Section
>Beginning Critical Section
>SELECT * from appl_trans WHERE applicant_id=70948  
>Ending Critical Section

your error is occurring because the operations are happenning like this ...

>Beginning Critical Section
>select * from vw_appl_doc where application_id = 709 and file_id = 70819
>SELECT * from appl_trans WHERE applicant_id=70948  

Hope this helps a bit.



Expert Comment

ID: 12631402
I had a thought to make two connections to the same database.  One from one thread and another from the other thread.  This will prevent the conflict.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month14 days, 5 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question