CODEWITHSUNDEEP
c#asp.netsql-serverstored-proceduresgridview
rickyProgrammer
rickyProgrammer

Reputation: 1167

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

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

Upvotes: 0

Views: 2407

Answers (1)

Iswanto San
Iswanto San

Reputation: 18569

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();

Upvotes: 4

Related Questions