“ExecuteReader requires an open and available Conn

Question

I am wiorking on a web application when i click on to fetch data from database i get the following error:

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

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

I declare my connection on a seperate class as the following:

public Database()
{
    valid = false;

    using (connection = new SqlConnection(connectionString))       
    {                  
        connection.Open();
    }    
}

Where i get the error:

and i call it from a WebForm. Why do i get this error when the connection is already open (I used to use odbc connection and it works fine, however the SqlConnection is not working)

What is the reason?

Answer 1

SQL connection is open within using scope only:

  using (var connection = new SqlConnection(connectionString)) {
    connection.Open(); // open here
    ...
  } // close here

So put your command(s) into the using scope:

  using (var connection = new SqlConnection(connectionString)) {
    connection.Open(); 

    // command creation and execution should be within connection "using" scope
    using (var q = new SqlCommand()) {
      q.Connection = connection;
      ...

      // reader should be within command "using" scope
      using (var reader = q.ExecuteReader()) {
        ...
      }
    }  
    ...
  } // close here