DataReader associated with this Command which must be closed

Ravishankar Velladurai

6y2.9k.NET

There is already an open DataReader associated with this Command which must be closed first.

Line 33: SqlDataAdapter da = new SqlDataAdapter(query, con);

Line 34: DataSet ds = new DataSet();

Line 35: da.Fill(ds);

Line 36: GridView1.DataSource = ds;

Line 37: GridView1.DataBind();

Stack Trace:

[InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.]

System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command) +1642350

System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) +91

System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) +274

System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +119

System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +65

System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +170

System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +43

System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +12

System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140

System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +138

System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +89 cssapp.WebForm1.rep_bind() in C:\Users\Ravi\Documents\Visual Studio 2019\Project\cssapp\cssapp\WebForm1.aspx.cs:35

cssapp.WebForm1.Button1_Click(Object sender, EventArgs e) in C:\Users\Ravi\Documents\Visual Studio 2019\Project\cssapp\cssapp\WebForm1.aspx.cs:51

System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9784114 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +211

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +12

System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +15

System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35

System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1696

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar6y ago
Accepted answer
Hello Ravishankar, To avoid such kind of error use 'using'. The using statement will ensure correct disposal of the object and freeing of resources. If you forget then you are leaving the cleaning up to the garbage collector, which could take a while. Refer below code for example.
using(SqlConnection connection = new SqlConnection("connection string"))
{
    connection.Open();
    using(SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection))
    {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader != null) // Do null check before access object
            {
                while (reader.Read())
                {
                    //do something
                }
            }
        } // reader closed and disposed up here
    } // command disposed here
} //connection closed and disposed here

This occurs when you have multiple DataReaders open concurrently on the same connection, ie you call SqlCommand.ExecuteReader but don't close the SqlDataReader returned by this method before calling it again (either on the same command or another command on the same connection). Where do you create your SqlDataReader? You should be closing it and setting it to null when you're done with it and then recreating it when you need to query the database again Just to let you know a datareader represents an open connection to the database. to run 2 readers simultaneously, you need to use two seperate connections
Jignesh Kumar6y ago
Hi,

Please use this link,
https://forums.asp.net/t/2144108.aspx?Error+There+is+already+an+open+DataReader+associated+with+this+Command+which+must+be+closed+first+
Ravi Patel6y ago
Hi,

try this

con.Close();
Line 33: SqlDataAdapter da = new SqlDataAdapter(query, con);
Line 34: DataSet ds = new DataSet();
Line 35: da.Fill(ds);
Line 36: GridView1.DataSource = ds;
Line 37: GridView1.DataBind();
Vinay Singh6y ago
Hi

You first need to opne the connectino and then close the connection after reading the data
SqlDataAdapter da = new SqlDataAdapter(query, con);
con.Open();
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
GridView1.DataSource = ds.Table[0];
GridView1.DataBind();