this is my code
String invoiceno = txtInvoiceNo.Text.Trim();
String pur_date = dtpPurchaseDate.Value.ToString("yyyy-MM-dd");
String suppliercode = lblSupCode.Text;
//String rack = txtRack.Text;
//Double disc = Double.Parse(txtDiscount.Text);
String stock_sql = "insert into master_stock (productcode,productname,category,batchno,quantity,Packing,Purchaserate," +
"PackMRP,UnitMRP,Unitpurchaserate,tax,reorder_level,manufacturer,expirydate,suppliercode,commission,DSLimit,invoiceno,purchasedate,Numoftablets,discount,rack,status) values ";
String purc_sql = "insert into purchases (billno,productcode,productname,category,batchno,quantity,packing,purchaserate," +
"tax,Unitpurchaserate,suppliercode,purchasedate,expirydate,commission,Numoftablets,taxamt,unittax,freeqnty,Amount,PackMRP,UnitMRP,reorder_level,manufacturer,DSLimit,discount,rack) values ";
foreach (DataRow row in dt.Rows)
{
String pname = row["ProductName"].ToString();
String pcode = row["Code"].ToString();
String category = row["Category"].ToString();
String batchno = row["BatchNo"].ToString();
Double qnty = Double.Parse(row["Quantity"].ToString());
Double packing = Double.Parse(row["Packing"].ToString());
Double purrate = Double.Parse(row["PurchaseRate"].ToString());
Double unitpurchaserate = Math.Round(purrate / packing, 2);
Double numoftablets = Math.Round(packing * qnty);
String packMRP = row["PackMRP"].ToString();
String unitMRP = row["UnitMRP"].ToString();
String tax = row["Tax"].ToString();
String ROlevel = row["ROLevel"].ToString();
String manufacturer = row["Manufacturer"].ToString();
String expdate = row["ExpDate"].ToString();
//String mfgdate = row["MfgDate"].ToString();
Double commi = Convert.ToDouble(row["Commission"].ToString());
String dslimit = row["DSLimit"].ToString();
Double disc = double.Parse(row["discount"].ToString());
Double amount = Double.Parse(row["Amount"].ToString());
String rack = row["rack"].ToString();
Double taxamt = Double.Parse(row["TaxAmt"].ToString());
Double unittax = Double.Parse(row["UnitTax"].ToString());
Double freeqnty = Double.Parse(row["FreeQnty"].ToString());
Double numoftablets1 = (qnty + freeqnty) * packing;
Double quantity = qnty + freeqnty;
String supname = cmbSupName.Text;
DataSet dd1 = new DataSet();
dd1 = dbe.getData("select * from master_stock where invoiceno='" + txtInvoiceNo.Text + "'");
if (dd1.Tables[0].Rows.Count > 0)
{
string sql3 = "update master_stock set productcode='" + pcode + "',productname='" + pname + "',category='" + category + "',batchno= '" +
batchno + "',quantity=" + quantity + ",Packing=" + packing + ",Purchaserate=" + purrate + ",PackMRP=" + packMRP + ",UnitMRP=" + unitMRP + ",Unitpurchaserate=" +
unitpurchaserate + ",tax=" + tax + ",reorder_level=" + ROlevel + ",manufacturer='" + manufacturer + "',expirydate='" + expdate + "',suppliercode=" +
suppliercode + ",commission=" + commi + ",DSLimit=" + dslimit + ",invoiceno='" + invoiceno + "',Numoftablets=" + numoftablets1 + ",discount=" + disc + ",rack='" +
rack + "' where invoiceno='" + txtInvoiceNo.Text + "'";
dbe.updateData(sql3);
}
else
{
stock_sql += "('" + pcode + "','" + pname + "','" + category + "','" + batchno + "'," +
quantity + "," + packing + "," + purrate + "," + packMRP + "," + unitMRP + "," + unitpurchaserate + "," + tax + "," + ROlevel + ",'" +
manufacturer + "','" + expdate + "'," + suppliercode + "," + commi + "," + dslimit + ",'" + invoiceno + "','" + pur_date + "'," + numoftablets1 + " ," + disc + ",'" + rack + "','0'),";
stock_sql = stock_sql.Substring(0, stock_sql.Length - 1);
dbe.updateData(stock_sql);
}
DataSet dd = new DataSet();
dd = dbe.getData("select * from purchases where billno='" + txtInvoiceNo.Text + "'");
if (dd.Tables[0].Rows.Count > 0)
{
string sql2 = "update purchases set productcode='" + pcode + "',productname='" + pname + "',category='" + category + "',batchno='" +
batchno + "',quantity=" + qnty + ",packing=" + packing + ",purchaserate=" + purrate + ",tax=" + tax + ",Unitpurchaserate=" + unitpurchaserate + ",suppliercode='" +
suppliercode + "',expirydate='" + expdate + "',commission=" + commi + ",Numoftablets=" + numoftablets1 + ",taxamt=" + taxamt + ",unittax=" + unittax + ",freeqnty=" +
freeqnty + ",Amount=" + amount + ",PackMRP=" + packMRP + ",UnitMRP=" + unitMRP + ",reorder_level=" + ROlevel + ",manufacturer='" +
manufacturer + "',DSLimit=" + dslimit + ",discount=" + disc + ",rack='" + rack + "' where billno='" + txtInvoiceNo.Text + "'";
dbe.updateData(sql2);
}
else
{
purc_sql += "('" + invoiceno + "','" + pcode + "','" + pname + "','" +
category + "','" + batchno + "'," + qnty + "," + packing + "," + purrate + "," + tax + "," + unitpurchaserate +
"," + suppliercode + ",'" + pur_date + "','" + expdate + "'," + commi + "," + numoftablets1 + "," + taxamt + "," + unittax + "," + freeqnty + "," + amount + "," + packMRP + "," + unitMRP + "," +
ROlevel + ",'" + manufacturer + "'," + dslimit + "," + disc + ",'" + rack + "'),";
purc_sql = purc_sql.Substring(0, purc_sql.Length - 1);
dbe.updateData(purc_sql);
}
}