getting connection error when i am calling function inside

Sk Jha

getting error "There is already an open DataReader associated with this Command which must be closed first.The connection was not closed. The connection's current state is open."

Please any one can help me to solve the error.

here is my code

double totalExchange=0, totalBuyBack=0;
double markup=0;
void getData()
{
if (txtLotNumber.Text != "")
{
string total = "";
try
{
string com = "select * from PriceBreakupSaleBuyBack where itemNumber=@itemNumber;";
com += "select G_From, G_To, value from MarkupSlab where GorP='G'";
using (SqlCommand cmd = new SqlCommand(com, con))
{
cmd.Parameters.AddWithValue("@itemNumber", txtLotNumber.Text);
con.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
lblLotNumber.Text = dr["itemNumber"].ToString();
lblDescription.Text = dr["DesignDescription"].ToString();
lblGrossWt.Text = dr["GrossWeight"].ToString();
lblDesign.Text = dr["design"].ToString();
lblDiamondWt.Text = dr["DiamondWeight"].ToString();
lblGoldWt.Text = dr["GoldWeight"].ToString();
total = dr["itemDescription"].ToString();
if (total == "Total")
{
totalBuyBack += Convert.ToDouble(dr["Amount"].ToString());
totalExchange += Convert.ToDouble(dr["excAmount"].ToString());
}
}
double diaWt = Convert.ToDouble(lblDiamondWt.Text);
if (diaWt > 0.25)
{
lblTotalBuyBk.Text = "" + totalBuyBack;
lblTotalEx.Text = "" + totalExchange;
fillGrid();
note();
}
else
{
if (dr.NextResult())
{
while (dr.Read())
{
double Gfr = Convert.ToDouble(dr["G_From"].ToString());
double Gto = Convert.ToDouble(dr["G_To"].ToString());
if (totalExchange >= Gfr && totalExchange <= Gto)
{
markup = Convert.ToDouble(dr["value"].ToString());
}
}
}
con.Close();
totalExchange *= 1.25;
totalBuyBack *= 1.25;
totalExchange += totalExchange * markup / 100;
totalBuyBack += totalBuyBack * markup / 100;
lblTotalBuyBk.Text = "" + totalBuyBack;
lblTotalEx.Text = "" + totalExchange;
note();
}
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
else
{
Response.Write("");
}
}
void fillGrid()
{
try
{
string com = "select itemGroup, Item, ItemDescription, Pieces, Weight, ExcAmount, Amount from PriceBreakupSaleBuyBack";
SqlCommand cmd = new SqlCommand(com, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
string data;
for (int i = 0; i < (GridView1.Rows.Count); i++)
{
data=GridView1.Rows[i].Cells[0].Text.ToString();
data = data.Split(' ').First();
GridView1.Rows[i].Cells[0].Text = data;
}
if (GridView1.Rows.Count > 0)
{
GridView1.HeaderRow.Cells[0].Text = "Type";
GridView1.HeaderRow.Cells[3].Text = "Pcs/KT";
GridView1.HeaderRow.Cells[4].Text = "Wt";
}
color();
}
catch (Exception ex) { Response.Write(ex.Message); }
}
void color()
{
string data;
for (int i = 0; i < (GridView1.Rows.Count); i++)
{
data = GridView1.Rows[i].Cells[2].Text.ToString();
if (data.Equals("Total"))
{
GridView1.Rows[i].ForeColor = Color.Red;
}
}
}
void note()
{
string note="";
string com = "select * from PriceBreakupBuyBackNotes";
SqlCommand cmd = new SqlCommand(com, con);
con.Open();
SqlDataReader dr;
dr = cmd.ExecuteReader();
while (dr.Read())
{
note += dr["id"].ToString()+". ";
note += dr["note"].ToString()+"
";
}
lblNote.Text = note;
lblNote.ForeColor = Color.Red;
con.Close();
}

5 Replies

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

Amit Gupta8y ago
Hey

DataReader works on connected architecture and hence connection must be closed after its usage when it was used by another reader.

You can go for any of the 3 option
1. Use your connection in a using block

using( var con = new SqlConnection(connection string);
{
   // open your con here
  // Execute your DataReader and other c9mmands here
}
....
..



2. Close the con before opening it
con.Close();
con.Open();

3. Check your connection state before opening it.

if(con.State!= ConnectionState.Open)
   con.Open();
Good Luck
Altaf Ansari8y ago
i think you somewhere in your code you did not close the DataReader..
Please make sure you have Close the Data Reader wherever you are reading the DataReader..

Or Replace Your Code with the following..
double totalExchange = 0, totalBuyBack = 0;
        double markup = 0;
        void getData()
        {
            if (txtLotNumber.Text != "")
            {
                string total = "";
                try
                {
                    string com = "select * from PriceBreakupSaleBuyBack where itemNumber=@itemNumber;";
                    com += "select G_From, G_To, value from MarkupSlab where GorP='G'";
                    using (SqlCommand cmd = new SqlCommand(com, con))
                    {
                        cmd.Parameters.AddWithValue("@itemNumber", txtLotNumber.Text);
                        con.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {

                            while (dr.Read())
                            {
                                lblLotNumber.Text = dr["itemNumber"].ToString();
                                lblDescription.Text = dr["DesignDescription"].ToString();
                                lblGrossWt.Text = dr["GrossWeight"].ToString();
                                lblDesign.Text = dr["design"].ToString();
                                lblDiamondWt.Text = dr["DiamondWeight"].ToString();
                                lblGoldWt.Text = dr["GoldWeight"].ToString();
                                total = dr["itemDescription"].ToString();
                                if (total == "Total")
                                {
                                    totalBuyBack += Convert.ToDouble(dr["Amount"].ToString());
                                    totalExchange += Convert.ToDouble(dr["excAmount"].ToString());

                                }
                            }
                            double diaWt = Convert.ToDouble(lblDiamondWt.Text);
                            if (diaWt > 0.25)
                            {
                                lblTotalBuyBk.Text = "" + totalBuyBack;
                                lblTotalEx.Text = "" + totalExchange;
                                fillGrid();
                                note();
                            }

                            else
                            {
                                if (dr.NextResult())
                                {
                                    while (dr.Read())
                                    {
                                        double Gfr = Convert.ToDouble(dr["G_From"].ToString());
                                        double Gto = Convert.ToDouble(dr["G_To"].ToString());
                                        if (totalExchange >= Gfr && totalExchange <= Gto)
                                        {
                                            markup = Convert.ToDouble(dr["value"].ToString());
                                        }
                                    }
                                }
                                dr.Close();
                                con.Close();
                                totalExchange *= 1.25;
                                totalBuyBack *= 1.25;
                                totalExchange += totalExchange * markup / 100;
                                totalBuyBack += totalBuyBack * markup / 100;
                                lblTotalBuyBk.Text = "" + totalBuyBack;
                                lblTotalEx.Text = "" + totalExchange;
                                note();
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                }
            }
            else
            {
                Response.Write("");
            }
        }
        void fillGrid()
        {
            try
            {
                string com = "select itemGroup, Item, ItemDescription, Pieces, Weight, ExcAmount, Amount from PriceBreakupSaleBuyBack";
                SqlCommand cmd = new SqlCommand(com, con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                GridView1.DataSource = ds;
                GridView1.DataBind();
                string data;
                for (int i = 0; i < (GridView1.Rows.Count); i++)
                {
                    data = GridView1.Rows[i].Cells[0].Text.ToString();
                    data = data.Split(' ').First();
                    GridView1.Rows[i].Cells[0].Text = data;
                }
                if (GridView1.Rows.Count > 0)
                {
                    GridView1.HeaderRow.Cells[0].Text = "Type";
                    GridView1.HeaderRow.Cells[3].Text = "Pcs/KT";
                    GridView1.HeaderRow.Cells[4].Text = "Wt";
                }
                color();
            }
            catch (Exception ex) { Response.Write(ex.Message); }
        }
        void color()
        {
            string data;
            for (int i = 0; i < (GridView1.Rows.Count); i++)
            {
                data = GridView1.Rows[i].Cells[2].Text.ToString();
                if (data.Equals("Total"))
                {
                    GridView1.Rows[i].ForeColor = Color.Red;
                }

            }

        }
        void note()
        {
            string note = "";
            string com = "select * from PriceBreakupBuyBackNotes";
            SqlCommand cmd = new SqlCommand(com, con);
            con.Open();
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                note += dr["id"].ToString() + ". ";
                note += dr["note"].ToString() + "
";
            }
            lblNote.Text = note;
            lblNote.ForeColor = Color.Red;
            dr.Close();
            con.Close();
        }
Sk Jha8y ago
Dear Altaf, getting the error same error after dr.close();

There is already an open DataReader associated with this Command which must be closed first.The connection was not closed. The connection's current state is open.
Altaf Ansari8y ago
Hello Sk Jha,

Just write :
dr.close();
before closing the connection:
void note()
{
string note="";
string com = "select * from PriceBreakupBuyBackNotes";
SqlCommand cmd = new SqlCommand(com, con);
con.Open();
SqlDataReader dr;
dr = cmd.ExecuteReader();
while (dr.Read())
{
note += dr["id"].ToString()+". ";
note += dr["note"].ToString()+"
";
}
lblNote.Text = note;
lblNote.ForeColor = Color.Red;
dr.Close();
con.Close();
}
Sk Jha8y ago
when i am calling fillGrid() and Note() method inside getData() method then the error is thrown.