asp.net, ExecuteReader requires an open and available Connection

Question

I have this code on a method:

DataGrid2.DataSource = Show1(Convert.ToInt32(Request.QueryString["Cr"]));
DataGrid2.DataBind();

this is the show method that is asigned to the datasource:

static SqlConnection sqlConntest = new SqlConnection( ConfigurationSettings .AppSettings["conn"].ToString () );

public static SqlDataReader Show1(int cr)
 {
   SqlDataReader dr;
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = sqlConntest;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp1";
                cmd.Parameters.Add("@Cr", SqlDbType.Int);
                cmd.Parameters["@Cr"].Value = crewID;
 sqlConntest.Open();
                dr = cmd.ExecuteReader();

                return dr;
}

when I run the program I get the error message:

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

Why is this happening and how can I solve this? thanks.

Answer 1

Now i've reopened the question since my proposed duplicate might be helpful and is related but seems not to be an exact duplicate. I'll post our comments here:

Using a static connection in ASP.NET is not a good idea usually, all the more if you use connection-pooling which is enabled by default.

You: "I have removed the static attribute from the sqlconnection but I still get the error"

Use also the using-statement to always close the connection as soon as possible. You should also dispose the SqlDataReader and SqlCommand with a using.

You: "I added the using but now I'm getting error "Invalid attempt to FieldCount when reader is closed error""

I assume that this is caused by the fact that now the connection will be closed in this method (which is good). But you use the datareader as DataSource for the GridView, a datareader is a stream which needs an open connection to the database. It is consumed outside of the method at DataGrid2.DataBind(). Therefore you get the exception.

I would simply use a SqlDataAdapter to fill a DataTable, return that and use it as DataSource instead. It's just an in-memory object which does not need an open connection:

public static DataTable Show1(int cr)
{
    DataTable table = new DataTable();
    using (var con = new SqlConnection(ConfigurationSettings.AppSettings["conn"].ToString()))
    using (var cmd = new SqlCommand("sp1", con) { CommandType = CommandType.StoredProcedure })
    using (var da = new SqlDataAdapter(cmd))
        da.Fill(table);  // Fill opens the connection automatically
    return table;
}