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.

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
– Tim Schmelter, Commented Jul 23, 2014 at 20:48
@Pyram yes that you will get for opening connection after it's used. Hence my comment stated before — Comfortably Numb
– Comfortably Numb, Commented Jul 23, 2014 at 20:49
@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
– Tim Schmelter, Commented Jul 23, 2014 at 21:09
@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
– Tim Schmelter, Commented Jul 23, 2014 at 21:35

Community · Accepted Answer · 2017-05-23 11:50:19Z

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

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Jul 23, 2014 at 21:57

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

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

3 Comments

pyram Over a year ago

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?

Tim Schmelter Over a year ago

Then you have code where you cast GridViewRow.DataItem to DbDataRecord instead of DataRowView. I assume its in RowDataBound.

pyram Over a year ago

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!

Collectives™ on Stack Overflow

asp.net, ExecuteReader requires an open and available Connection

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related