Hello sir, I am getting error("Invalid attempt to call Read when reader is closed.") even though there should be data present. while executing the reader second time.Can u help me to resolve the problem.
MY CODE:
SqlCommand cmd6 = new SqlCommand("select productid,serialno,qnty,price,tax,total from invoicetran where qotranid='" + ddlqono.SelectedItem.Text + "' and company_id='" + company_id.Text + "' ", myconnection);
myconnection.Open();
SqlDataReader myDataReader16 = default(SqlDataReader);
myDataReader16 = cmd6.ExecuteReader(CommandBehavior.CloseConnection);
while ((myDataReader16.Read()) == true)
{
if (myDataReader16.HasRows == true)
{
invproductid.Text = myDataReader16["productid"].ToString();
invserialno.Text = myDataReader16["serialno"].ToString();
invtot1.Text = myDataReader16["total"].ToString();
qnty2.Text = myDataReader16["qnty"].ToString();
invprice.Text = myDataReader16["price"].ToString();
invtax.Text = myDataReader16["tax"].ToString();
untprice.Text = (Convert.ToDecimal(invprice.Text)).ToString();
unttax.Text = (Convert.ToDecimal(invtax.Text)).ToString();
unttot.Text = (Convert.ToDecimal(untprice.Text) + Convert.ToDecimal(unttax.Text)).ToString();
SqlCommand cmd4 = new SqlCommand("select productid,qnty from stock where productid='" + invproductid.Text + "' and status='A' and company_id='" + company_id.Text + "' ", myconnection1);
myconnection1.Open();
SqlDataReader myDataReader9 = default(SqlDataReader);
myDataReader9 = cmd4.ExecuteReader(CommandBehavior.CloseConnection);
while ((myDataReader9.Read()) == true)
{
productid11.Text = myDataReader9["productid"].ToString();
qnty1.Text = myDataReader9["qnty"].ToString();
}
myDataReader9.Close();
myconnection1.Close();
if (qnty1.Text == qnty2.Text)
{
SqlCommand cmd5 = new SqlCommand("update stock set status='NA' where productid='" + productid11.Text + "'and company_id='" + company_id.Text + "' ", myconnection2);
myconnection2.Open();
cmd5.ExecuteNonQuery();
myconnection2.Close();
string[] sernum = null;
int I = 0;
sernum = invserialno.Text.Split(',');
for (I = 0; I <= sernum.Length - 1; I++)
{
sernum1.Text = sernum[I];
SqlCommand cmd8 = new SqlCommand("update stockserialno set status='NA' where productid='" + productid11.Text + "' and Ltrim(serialno)='" + sernum1.Text + "' and company_id='" + company_id.Text + "' ", myconnection1);
myconnection1.Open();
cmd8.ExecuteNonQuery();
myconnection1.Close();
if (dept == 1)
{
SqlCommand mycmd = new SqlCommand("select typeid from product where productid='" + invproductid.Text + "' and company_id='" + company_id.Text + "' ", myconnection2);
myconnection2.Open();
SqlDataReader myDataReader4 = default(SqlDataReader);
myDataReader4 = mycmd.ExecuteReader();
while ((myDataReader4.Read()) == true)
{
typeid = Convert.ToInt16(myDataReader4["typeid"]);
}
myDataReader4.Close();
myconnection2.Close();
if (typeid != 17)
{
SqlCommand cmd9 = new SqlCommand("update temp set invid='" + lblinvoiceid.Text + "',invdate='" + lbldate.Text + "',invprice=" + untprice.Text + ",invtax=" + unttax.Text + ",invtotal=" + unttot.Text + " where productid='" + invproductid.Text + "' and serialno='" + sernum1.Text + "' and company_id='" + company_id.Text + "' ", myconnection2);
myconnection2.Open();
cmd9.ExecuteNonQuery();
myconnection2.Close();
}
else
{
SqlCommand cmd9 = new SqlCommand("update temp set invid='" + lblinvoiceid.Text + "',invdate='" + lbldate.Text + "',invprice=" + invprice.Text + ",invtax=" + invtax.Text + ",invtotal=" + invtot1.Text + " where productid='" + invproductid.Text + "' and serialno='" + sernum1.Text + "' and company_id='" + company_id.Text + "' ", myconnection2);
myconnection2.Open();
cmd9.ExecuteNonQuery();
myconnection2.Close();
}
SqlCommand cmd10 = new SqlCommand("select round(pototal,0)as pototal,round(invtotal,0) as invtotal from temp where productid='" + invproductid.Text + "' and serialno='" + sernum1.Text + "' and company_id='" + company_id.Text + "'", myconnection2);
myconnection2.Open();
SqlDataReader myDataReader10 = default(SqlDataReader);
myDataReader10 = cmd10.ExecuteReader(CommandBehavior.CloseConnection);
while ((myDataReader10.Read()) == true)
{
potot.Text = myDataReader10["pototal"].ToString();
invtot.Text = myDataReader10["invtotal"].ToString();
margin.Text = (Convert.ToInt64(invtot.Text) - Convert.ToInt64(potot.Text)).ToString();
SqlCommand cmd11 = new SqlCommand("update temp set margin=" + margin.Text + " where productid='" + invproductid.Text + "' and serialno='" + sernum1.Text + "' and company_id='" + company_id.Text + "' ", myconnection1);
myconnection1.Open();
cmd11.ExecuteNonQuery();
myconnection1.Close();
}
myconnection2.Close();
myDataReader10.Close();
}
else
{
Autotempid();
SqlCommand cmd14 = new SqlCommand("insert into temp values('" + lbltempid.Text + "','" + dept + "','','" + lbldate.Text + "','" + lblinvoiceid.Text + "','" + lbldate.Text + "','" + invproductid.Text + "','','','' ,'','','','" + invtot1.Text + "','" + invtot1.Text + "','" + branch_id.Text + "','" + company_id.Text + "')", myconnection1);
myconnection1.Open();
cmd14.ExecuteNonQuery();
myconnection1.Close();
}
}
}
else
{
SqlCommand cmd5 = new SqlCommand("update stock set qnty=" + qnty1.Text + "-" + qnty2.Text + " where productid='" + productid11.Text + "'and company_id='" + company_id.Text + "' ", myconnection2);
myconnection2.Open();
cmd5.ExecuteNonQuery();
myconnection2.Close();
string[] sernum = null;
int I = 0;
sernum = invserialno.Text.Split(',');
for (I = 0; I <= sernum.Length - 1; I++)
{
sernum1.Text = sernum[I];
SqlCommand cmd8 = new SqlCommand("update stockserialno set status='NA' where productid='" + productid11.Text + "' and Ltrim(serialno)='" + sernum1.Text + "' and company_id='" + company_id.Text + "' ", myconnection1);
myconnection1.Open();
cmd8.ExecuteNonQuery();
myconnection1.Close();
if (dept == 1)
{
SqlCommand mycmd = new SqlCommand("select typeid from product where productid='" + invproductid.Text + "' and company_id='" + company_id.Text + "' ", myconnection2);
myconnection2.Open();
SqlDataReader myDataReader4 = default(SqlDataReader);
myDataReader4 = mycmd.ExecuteReader();
while ((myDataReader4.Read()) == true)
{
typeid = Convert.ToInt16(myDataReader4["typeid"]);
}
myDataReader4.Close();
myconnection2.Close();
if (typeid != 17)
{
SqlCommand cmd9 = new SqlCommand("update temp set invid='" + lblinvoiceid.Text + "',invdate='" + lbldate.Text + "',invprice=" + untprice.Text + ",invtax=" + unttax.Text + ",invtotal=" + unttot.Text + " where productid='" + invproductid.Text + "' and serialno='" + sernum1.Text + "' and company_id='" + company_id.Text + "' ", myconnection2);
myconnection2.Open();
cmd9.ExecuteNonQuery();
myconnection2.Close();
}
else
{
SqlCommand cmd9 = new SqlCommand("update temp set invid='" + lblinvoiceid.Text + "',invdate='" + lbldate.Text + "',invprice=" + invprice.Text + ",invtax=" + invtax.Text + ",invtotal=" + invtot1.Text + " where productid='" + invproductid.Text + "' and serialno='" + sernum1.Text + "' and company_id='" + company_id.Text + "' ", myconnection2);
myconnection2.Open();
cmd9.ExecuteNonQuery();
myconnection2.Close();
}
SqlCommand cmd10 = new SqlCommand("select round(pototal,0)as pototal,round(invtotal,0) as invtotal from temp where productid='" + invproductid.Text + "' and serialno='" + sernum1.Text + "' and company_id='" + company_id.Text + "'", myconnection2);
myconnection2.Open();
SqlDataReader myDataReader10 = default(SqlDataReader);
myDataReader10 = cmd10.ExecuteReader(CommandBehavior.CloseConnection);
while ((myDataReader10.Read()) == true)
{
potot.Text = myDataReader10["pototal"].ToString();
invtot.Text = myDataReader10["invtotal"].ToString();
margin.Text = (Convert.ToInt64(invtot.Text) - Convert.ToInt64(potot.Text)).ToString();
SqlCommand cmd11 = new SqlCommand("update temp set margin=" + margin.Text + " where productid='" + invproductid.Text + "' and serialno='" + sernum1.Text + "' and company_id='" + company_id.Text + "' ", myconnection1);
myconnection1.Open();
cmd11.ExecuteNonQuery();
myconnection1.Close();
}
myconnection2.Close();
myDataReader10.Close();
}
else
{
Autotempid();
SqlCommand cmd14 = new SqlCommand("insert into temp values('" + lbltempid.Text + "','" + dept + "','','" + lbldate.Text + "','" + lblinvoiceid.Text + "','" + lbldate.Text + "','" + invproductid.Text + "','','','' ,'','','','" + invtot1.Text + "','" + invtot1.Text + "','" + branch_id.Text + "','" + company_id.Text + "')", myconnection1);
myconnection1.Open();
cmd14.ExecuteNonQuery();
myconnection1.Close();
}
}
}
}
}
myDataReader16.Close();
myconnection.Close();
|