Executereader requires an open and available connection. the connection's current state is closed.

Question

Problem :

When I am attempting to connect to MSSQL database via ASP.NET online, I will often get the below error when two or more people try to connect simultaneously:

ExecuteReader requires an open and available Connection. The connection's current state is Connecting.

My site works fine on the localhost server.

May I know what might have gone wrong with my site and how can I fix the error?

Answer 1

If you are even using the static connections you're trying to create the lock for every thread trying to access your object. ASP.NET is the multithreading environment by its nature. So there is the great chance for these locks which causes the performance issues at best. Actually very soon or later you will start geting many different exceptions(like the ExecuteReader requires an open and available Connection etc).

Conclusion:

· Don't reuse the connections or any of the ADO.NET objects at all.

· Don't make them the static or shared(in VB.NET)

· Always try to create, open(in case of the Connections), use the close and dispose them where you need them(example  in the method)

· Use a using-statement to dispose and close(in case of  the Connections)  implicitly

That is very true not only for your Connections. Every object implementing the IDisposable should be disposed(simplest by the using-statement), all and more in a System.Data.SqlClient namespace.

All the above tries to speaks against the custom DB-Class which encapsulates and reuse the all objects.