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

Question

I have two gridviews in asp.net separated using Ajax's tab container. In one button click event, I want the two gridviews to be populated using a datasource from two different stored procedures.

First gridview - detailed summary of sales per tenant

Second gridview - Consolidated sales group per date

Here's the code

        SqlCommand cmd = new SqlCommand("spDSRDetailed", con);
        cmd.CommandTimeout = 120;
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@dateFrom", txtdatefrom.Text);
        cmd.Parameters.AddWithValue("@dateTo", txtdateto.Text);
        cmd.Parameters.AddWithValue("@Location", hdnLoc.Value);
        cmd.Parameters.AddWithValue("@RP", hdnRP.Value);

        try
        {
            con.Open();
            grdDailySalesReport.EmptyDataText = "No Records Found";
            grdDailySalesReport.DataSource = cmd.ExecuteReader();
            grdDailySalesReport.DataBind();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }

This code works only with one gridview, I know it is possible to do this using SQLDatasource, But that method is not my option because I have used a complex SQL queries and not ideal to do it using SQLDATASOURCE.SELECTCOMMAND.

I tried this and it gives me this error

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

        SqlCommand cmd = new SqlCommand("spDSRDetailed", con);
        cmd.CommandTimeout = 120;
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        SqlCommand cmd2 = new SqlCommand("spDSRConso", con);
        cmd2.CommandTimeout = 120;
        cmd2.CommandType = System.Data.CommandType.StoredProcedure;


        cmd.Parameters.AddWithValue("@dateFrom", txtdatefrom.Text);
        cmd.Parameters.AddWithValue("@dateTo", txtdateto.Text);
        cmd.Parameters.AddWithValue("@Location", hdnLoc.Value);
        cmd.Parameters.AddWithValue("@RP", hdnRP.Value);

        cmd2.Parameters.AddWithValue("@dateFrom", txtdatefrom.Text);
        cmd2.Parameters.AddWithValue("@dateTo", txtdateto.Text);
        cmd2.Parameters.AddWithValue("@Location", hdnLoc.Value);
        cmd2.Parameters.AddWithValue("@RP", hdnRP.Value);


        try
        {
            con.Open();
            grdDailySalesReport.EmptyDataText = "No Records Found";
            grdDailySalesReport.DataSource = cmd.ExecuteReader();
            grdDailySalesReport.DataBind();


            grdDSRConso.EmptyDataText = "No Records Found";
            grdDSRConso.DataSource = cmd2.ExecuteReader();
            grdDSRConso.DataBind();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }

I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, May 02 '15 at 03:55
Hi @John, is it okay to change the title of my question to: There is already an open DataReader associated with this Command which must be closed first. (the actual error) — rickyProgrammer, May 02 '15 at 04:06
Sure, but the "in ASP.NET C#" part should be left out. It's not even relevant. — John Saunders, May 02 '15 at 08:44
I see, I just thought that part is needed to isolate the error to asp.net c# only, Thanks a lot @John :) — rickyProgrammer, May 02 '15 at 15:52
But what makes you think the error is specific to ASP.NET C#? Maybe it would also happen for ASP.NET VB? Maybe not specific to ASP.NET at all? — John Saunders, May 02 '15 at 17:47
Also, please learn about `using` blocks, and don't do `try {/* code */} catch (Exception ex){throw ex;}`. It's better to just do `/* code */` — John Saunders, May 04 '15 at 01:36
Since college days, I learned and was asked to always use try catch in my codes to avoid the system in crashing. Is using the "using" block has the same effects with try catch? — rickyProgrammer, May 04 '15 at 01:52
Your college instructors lied to you about try/catch. It should be used rarely, and your code does nothing except make it difficult to ever learn where the real exception came from. — John Saunders, May 04 '15 at 02:09
ok. I'll take that, but how about when the system crashes? the try catch function works well with that. — rickyProgrammer, May 04 '15 at 02:15
try/catch is too often used to _hide_ exceptions. Instead, let them propagate to the outermost layer of the application, log them, _and then let the application crash_. Crashing means you made a mistake. Logging the exception allows you to fix the mistake. Hiding exceptions prevents you from fixing your errors, but you **still** made the mistake, and catching the exception will not fix it. — John Saunders, May 04 '15 at 07:12
See http://stackoverflow.com/questions/22623/throwing-exceptions-best-practices, http://stackoverflow.com/questions/881473/why-catch-and-rethrow-an-exception-in-c, and http://stackoverflow.com/questions/2469822/handling-exceptions-is-this-a-good-way. — John Saunders, May 04 '15 at 07:16
Ok! I got the point, so you are saying that it is okay for the web-based application to crash, for the purpose of getting the correct error for it to be fixed. However as a user of the system, it is better to see something like a page or a message box rather than a lines of codes — rickyProgrammer, May 04 '15 at 07:35
There is a `customErrors` section in the web.config file which will allow a pretty error page to be used. Don't worry so much about how it looks when the application fails - instead worry about fixing the application and doing what you can to make sure the problem doesn't happen in the future. — John Saunders, May 04 '15 at 07:40

score 4 · Accepted Answer · answered May 02 '15 at 03:40

4

You are using SqlCommand.ExecuteReader and the message said :

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

So you need to close the first SqlCommand.ExecuteReader first.

Try this:

SqlDataReader reader = cmd.ExecuteReader();
grdDailySalesReport.EmptyDataText = "No Records Found";
grdDailySalesReport.DataSource = reader;
grdDailySalesReport.DataBind();

reader.Close();

reader = cmd2.ExecuteReader();
grdDSRConso.EmptyDataText = "No Records Found";
grdDSRConso.DataSource = reader;
grdDSRConso.DataBind();

reader.Close();

answered May 02 '15 at 03:40

Iswanto San

18,263
13
58
79

2

Better still, the `SqlCommand`, `SqlDataReader`s and `SqlConnection` all need to be in `using` blocks. – John Saunders May 02 '15 at 03:55
Hi @Iswanto, thanks it worked! thanks a lot. Do you think I used the best method in terms of performance / speed or are there any alternatives to meet what I want? – rickyProgrammer May 02 '15 at 04:04
Anyway I'll accept your answer, but if there's someone who can do the alternative to say it is better in terms of speed I will accept that :) thanks a lot @Iswanto for help. God bless you – rickyProgrammer May 02 '15 at 04:09
1

@rickyProgrammer to get the best performance you want to use `using` blocks for all `SqlConnections` and make them as small as possible. .NET uses [connection pooling](https://msdn.microsoft.com/en-us/library/bb399543%28v=vs.110%29.aspx) so when you exit the using block it does not actually close the connection but instead return it to the pool. This lets some other part of the code re-use the connection an not have the overhead of initiating the connection. – Scott Chamberlain May 02 '15 at 05:49
2

@ScottChamberlain: more importantly, a `using` block ensures that the object in it _will_ get cleaned up in a timely manner, even if an exception is thrown. It should be used for all objects that implement the `IDisposable` interface, in this case, the `SqlCommand`, `SqlDataReader`s and `SqlConnection`. – John Saunders May 02 '15 at 08:46
Thank you both for your wisdom and advices :) very much appreciated – rickyProgrammer May 02 '15 at 15:56

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

1 Answers1