Error: ExecuteReader requires an open and available Connection.


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!!

PartnersIntlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sendmeblahCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob LearnedCommented:
Yes, for scaleability you should seriously looking into connection pooling.

Bob
0
ihenryCommented:
>> 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.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataodbcodbcdatareaderclasstopic.asp
0
gregoryyoungCommented:
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  
>Error

Hope this helps a bit.

Cheers,

Greg
0
sendmeblahCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.