How to use executeScalar() inside executeReader()in asp.net using C#

Question

I am getting error :

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

Code is as follows :

I am generating dynamic table using 1 table & in every row of that table I want sum calculations from another Table

I have called readDr() first using sqlcommand "cmd" & ExecuteReader().
After that I have called CalTotAmt(string CC) which uses sqlcommand "cmdTotAmt" & ExecuteScalar().
I have used 2 diff sqlcommand still its giving error.

protected void readDr()
{
        string str = "select CCNo,TotalAmt,NoOfRect,Energy,New1,Theft,Misc from ChallanTable;";
        cmd = new SqlCommand(str, con);
        rdr = cmd.ExecuteReader();      
}


protected void CreateChallanTable()
{
        table.Caption = "Challan Entry";
        table.ID = "Challan Entry";
        table.BackColor = System.Drawing.Color.Maroon;
        table.ForeColor = System.Drawing.Color.Gray;

        readDr();      //call to function readDr()

        Panel2.Controls.Add(table);

        for (i = 0; i < 1; i++)
        {
            row = new TableRow();
            row.BorderStyle = BorderStyle.Ridge;
            m = 0;
            while (rdr.Read())
            {
                row = new TableRow();
                row.ID = "Row" + m;
                row.BorderStyle = BorderStyle.Ridge;

                for (n = 0; n <= 6; n++)
                {
                    cell = new TableCell();
                    cell.ID = "cell" + m + n;
                    cell.BorderWidth = 5;
                    cell.BorderStyle = BorderStyle.Ridge;
                    //cell.BorderColor = System.Drawing.Color.Azure;
                    for (n = 0; n <= 0; n++)
                    {
                        Label lbl = new Label();
                        lbl.ID = "lblCCRow" + m + n;
                        lbl.Text = Convert.ToString(rdr[0]);
                        lbl.Width = 70;
                        CCNo = lbl.Text;
                        // Add the control to the TableCell
                        cell.Controls.Add(lbl);
                    }

                    for (n = 1; n <= 1; n++)
                    {
                        Label lbl = new Label();
                        lbl.ID = "lblTotAmt" + m + n;
                        lbl.Text = Convert.ToString(rdr[1]);
                        lbl.Width = 70;
                        TotAmt = Convert.ToDouble(lbl.Text);
                        // Add the control to the TableCell
                        cell.Controls.Add(lbl);
                        Label2.Text = Convert.ToString(CalTotAmt(CCNo));
                    }
                    for (n = 2; n <= 2; n++)
                    {
                        Label lbl = new Label();
                        lbl.ID = "lblNoRect" + m + n;                            
                        lbl.Text = Convert.ToString(rdr[2]);
                        lbl.Width = 70;
                        NoofRects = Convert.ToDouble(lbl.Text);
                        // Add the control to the TableCell
                        cell.Controls.Add(lbl);
                    }
                    for (n = 2; n <= 2; n++)
                    {
                        Label lbl = new Label();
                        lbl.ID = "lblEnergy" + m + n;
                        lbl.Text = Convert.ToString(rdr[3]);
                        lbl.Width =70;
                        Energy = Convert.ToDouble(lbl.Text);
                        // Add the control to the TableCell
                        cell.Controls.Add(lbl);
                    }
                    for (n = 3; n <= 3; n++)
                    {
                        Label lbl = new Label();
                        lbl.ID = "lblNew" + m + n;
                        lbl.Text = Convert.ToString(rdr[4]);
                        lbl.Width =70;
                        NewSD = Convert.ToDouble(lbl.Text);
                        // Add the control to the TableCell
                        cell.Controls.Add(lbl);
                    }
                    for (n = 4; n <= 4; n++)
                    {
                        Label lbl = new Label();
                        lbl.ID = "lblTheft" + m + n;
                        lbl.Text = Convert.ToString(rdr[5]);
                        lbl.Width = 70;
                        Theft = Convert.ToDouble(lbl.Text);
                        // Add the control to the TableCell
                        cell.Controls.Add(lbl);
                    }
                    for (n = 5; n <= 5; n++)
                    {
                        Label lbl = new Label();
                        lbl.ID = "lblMisc" + m + n;
                        lbl.Text = Convert.ToString(rdr[6]);
                        lbl.Width = 70;
                        Misc = Convert.ToDouble(lbl.Text);
                        // Add the control to the TableCell
                        cell.Controls.Add(lbl);
                    }
                    row.Cells.Add(cell);
                }
                // Add the TableRow to the Table
                table.Rows.Add(row);
                //dr.NextResult();
                //outer for-loop end     
                m++;
            }
            rdr.Close();         
}

protected double CalTotAmt(string CC)
{
    double Total = 0;
    string str = "select Sum(Amount) from MainDataTable Where CC='" + CC + "' and BU ='" + LogInBU + "'";
    SqlCommand cmdTotAmt = new SqlCommand(str,con);
    Total = Convert.ToDouble(cmdTotAmt.ExecuteScalar());
    Label2.Text = Total.ToString();

    return Total;
}

Please help me out.

You should always say where (ie what line) the error is being thrown... — Chris, Apr 02 '13 at 10:45
You should write a *single* query that includes the `SUM()` as an additional column rather than doing each `SUM()` as a separate database call. — Damien_The_Unbeliever, Apr 02 '13 at 10:46
Man for (n = 0; n <= 0; n++), for (n = 1; n <= 1; n++) ... for (n = m; n <= m; n++)??? Can't it be replaced by if statement? http://en.wikipedia.org/wiki/Hard_coding — Alex, Apr 02 '13 at 10:47
voo: I hadn't noticed that. What's worse is that later on he's using m+n in places to generate unique IDs which will be really confusing... :) — Chris, Apr 02 '13 at 10:50

score 2 · Answer 1 · answered Apr 02 '13 at 10:48

2

Here you are trying to open multiple recordset open concurrently on a same connection. You can do that by adding MultipleActiveResultSets=True to the connection string.

answered Apr 02 '13 at 10:48

Amit Rai Sharma

4,015
1
28
35

score 0 · Answer 2 · answered Apr 02 '13 at 11:06

Try modifying you code, to make sure DataReader is closed properly:

Sample Code:

protected DataTable readDr()
{
        con.Open();
        string str = "select CCNo,TotalAmt,NoOfRect,Energy,New1,Theft,Misc from ChallanTable;";
        cmd = new SqlCommand(str, con);
        rdr = cmd.ExecuteReader();
        DataTable dt = new DataTable(); 
        dt.Load(rdr);
        rdr.Close();
        con.Close();
}

score 0 · Answer 3 · answered Apr 02 '13 at 12:20

You would generally fix it by writing a single query that computes all of the results in one go - rather than forcing your code to keep querying the database, over and over.

Something like:

select CCNo,TotalAmt,NoOfRect,Energy,New1,Theft,Misc,SumTotal
from ChallanTable ct
      cross apply
     (select Sum(Amount) as SumTotal from MainDataTable Where CC=ct.CCNo) t

Then you just have to process the results.

(Also, you have a bug in your display code at present - you have two attempts to do something when n==2, the second for will never be entered)

How to use executeScalar() inside executeReader()in asp.net using C#

3 Answers3