Error: ExecuteReader requires an open and available Connection.

Posted on 2004-11-17
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
    LVL 1

    Accepted Solution

    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
    Yes, for scaleability you should seriously looking into connection pooling.

    LVL 20

    Assisted Solution

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

    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.


    LVL 1

    Expert Comment

    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Bit flags and bit flag manipulation is perhaps one of the most underrated strategies in programming, likely because most programmers developing in high-level languages rely too much on the high-level features, and forget about the low-level ones. Th…
    This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now