Refer below code.
protected void OnSave(object sender, EventArgs e)
{
if (Page.IsValid)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] { new DataColumn("Item"), new DataColumn("Price"), new DataColumn("Quantity") });
foreach (Control c in ph1.Controls)
{
if (c.GetType().Name.ToLower() == "usercontrol_ascx")
{
UserControl uc = (UserControl)c;
TextBox tbItem = uc.FindControl("txtItem") as TextBox;
TextBox tbPrice = uc.FindControl("txtPrice") as TextBox;
TextBox tqty = uc.FindControl("txtQuantity") as TextBox;
if (!string.IsNullOrEmpty(tbItem.Text.Trim()) && !string.IsNullOrEmpty(tbPrice.Text.Trim()) && !string.IsNullOrEmpty(tqty.Text.Trim()))
{
dt.Rows.Add(tbItem.Text.Trim(), tbPrice.Text.Trim(), tqty.Text.Trim());
if (Convert.ToInt32(tqty.Text.Trim()) > 0)
{
int availableQuantity = Convert.ToInt32(GetAvailableQuantity(tbItem.Text.Trim()));
if (Convert.ToInt32(tqty.Text.Trim()) > availableQuantity)
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('No of Quantity entered is not available in Stock')", true);
}
else
{
int inserted = Insert(tbItem.Text.Trim(), tbPrice.Text.Trim(), tqty.Text.Trim());
if (inserted > 0)
{
UpdateStock(tbItem.Text.Trim(), tqty.Text.Trim());
ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('Data Submitted Successfully ... !!')", true);
}
}
}
}
}
}
// BindGridID();
gvInsertedRecords.DataSource = dt;
gvInsertedRecords.DataBind();
}
}
private string GetAvailableQuantity(string item)
{
string availableQuantity = "";
string constr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
string query = "SELECT Qty FROM Stock WHERE Item = @Item";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Item", item);
con.Open();
availableQuantity = Convert.ToString(cmd.ExecuteScalar());
con.Close();
}
}
return availableQuantity;
}
private int Insert(string item, string price, string qty)
{
int i = 0;
string constr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
string query = "INSERT INTO ItemOrdered (Store,SellsPerson,Receipt,Item,Price,Quantity,Sum) VALUES (@Store,@SellsPerson,@Receipt,@Item,@Price,@Quantity,@Sum)";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Store", Department.SelectedItem.Text);
cmd.Parameters.AddWithValue("@SellsPerson", HttpContext.Current.User.Identity.Name);
cmd.Parameters.AddWithValue("@Receipt", txtrecipt.Text);
cmd.Parameters.AddWithValue("@Item", item);
cmd.Parameters.AddWithValue("@Price", price);
cmd.Parameters.AddWithValue("@Quantity", qty);
cmd.Parameters.AddWithValue("@Sum", Convert.ToDecimal(price) * Convert.ToDecimal(qty));
con.Open();
i = cmd.ExecuteNonQuery();
con.Close();
}
return i;
}
}
private void UpdateStock(string item, string qty)
{
string constr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
string query = "UPDATE Stock SET Qty = Qty - @Quantity WHERE Item = @Item";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Item", item);
cmd.Parameters.AddWithValue("@Quantity", qty);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}