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

Question

I have mvc 4 website with DataHelperClass below to execute query. My problem is sometimes, website through exeception as title. I used using block to dispose SqlCommand and SqlDataAdapter but not success.

Please help me, sorry for my english.

        try
        {
            if (_conn.State == ConnectionState.Closed)
                _conn.Open();

            using (SqlCommand sqlCommand = new SqlCommand(query, _conn))
            {
                sqlCommand.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                    sqlCommand.Parameters.AddRange(parameters);

                //// check transaction is exist
                if (_trans != null)
                    sqlCommand.Transaction = _trans;

                DataTable dt = new DataTable();
                using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
                {
                    sqlDataAdapter.Fill(dt);
                }

                return dt;
            }
        }
        finally
        {
            //// close connection automatically if transaction is not exist
            if (_trans == null) { _conn.Close(); }
        }

Most likely static SqlConnection _conn that you did not show in the sample is the problem. — Alexei Levenkov
– Alexei Levenkov, Commented Dec 19, 2013 at 5:00

Community · Accepted Answer · 2017-05-23 12:02:39Z

9

It may be caused that your connection is not really opened, because when this code is called:

if (_conn.State == ConnectionState.Closed)
      _conn.Open();

Connection state could be: Broken, or Connecting or Fetching (See all enum list).

This may happen if you try to share your connection between many threads. I think you need to create a new connection each time this method is called. You can find many examples how to do it, including MSDN.

EDIT:

There is a great answer for such a question: ExecuteReader requires an open and available Connection. The connection's current state is Connecting

But if you really-really need it, try to prevent using the same connection with two or more threads by using lock (it is wrong actually, see the link above):

lock(_conn)
{
    DataTable dt = new DataTable();
    using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
    {
        sqlDataAdapter.Fill(dt);
    }
}

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Dec 19, 2013 at 5:06

Tony

7,4453 gold badges28 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Larry Dinh Over a year ago

I had replaced if (_conn.State == ConnectionState.Closed) _conn.Open(); with if (_trans == null) {_conn = new SqlConnection(Utility.Connect); _conn.Open(); } but problem cannot solve

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related