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.

Yuriy Galanter: sqlConntest.open(); before ExecuteReader ?............yes, if I add it after ExecuteReader i get the same error message when the program reaches the ExecuteReader line. — pyram, Jul 23 '14 at 20:46
Maybe i've voted too early to close for duplicate.However, the other question might be useful anyway since you're also using a static connection in ASP.NET. Not sure if it solves your issue though. — Tim Schmelter, Jul 23 '14 at 20:48
@Pyram yes that you will get for opening connection *after* it's used. Hence my comment stated **before** — Yuriy Galanter, Jul 23 '14 at 20:49
I have removed the static attribute from the sqlconnection but I still get the error — pyram, Jul 23 '14 at 21:06
@pyram: 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`. — Tim Schmelter, Jul 23 '14 at 21:09
@TimSchmelter... I added the using but now I'm getting error "Invalid attempt to FieldCount when reader is closed error" — pyram, Jul 23 '14 at 21:29
@pyram: i assume that this is caused by the fact that now the connection is closed in this method (which is good). But since you use the datareader as DataSource which is consumed outside of the method(at `DataGrid2.DataBind()`) 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. — Tim Schmelter, Jul 23 '14 at 21:35
@TimSchmelter a good example to use a DataTable in this case would be the example you posted on the duplicate question. Ok. I will try that and tell you how it worked out. — pyram, Jul 23 '14 at 21:41
By the way, my proposed duplicate's accepted answer also shows an example with a `SqlDataAdapter.` Basically you just need this: `using(var con=new SqlConnection("con-string")) using(var da=new SqlDataAdapter(sql, con))da.Fill(dataTable);`. The datadapter does not need to be opened/closed, Fill will do that for you. Here's another example with a stored-procedure: http://stackoverflow.com/questions/3528305/how-to-use-a-dataadapter-with-stored-procedure-and-parameter — Tim Schmelter, Jul 23 '14 at 21:42

score 1 · Accepted Answer · edited May 23 '17 at 11:50

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

Hi Tim, I modify the code with your answer and I got the error: "Cannot convert type 'System.Data.DataRowView' to 'System.Data.Common.DbDataRecord'"........How can I solve this? — pyram, Jul 23 '14 at 22:47
Then you have code where you cast GridViewRow.DataItem to DbDataRecord instead of DataRowView. I assume its in RowDataBound. — Tim Schmelter, Jul 24 '14 at 05:15
The problem was on the DataGrid2_ItemDataBound event. Once this event was fired, a method was called and that method had another sql connection but it didn't had the sqlConnection.Open(); So that's why I got the error "ExecuteReader requires an open and available Connection. The connection's current state is closed". So I didn't changed the SqlDataReader code. I just added the sqlConnection.Open() to that one method. I've marked your answer as the accepted answer. Thanks for all the help! — pyram, Jul 24 '14 at 17:25

asp.net, ExecuteReader requires an open and available Connection

1 Answers1