getting an error in transaction ...i have created table Tbl_Invoice (Invoice_Id ,Product_Name,Amount,Product_Quantity,Product_Price) and table Tbl_Product(Product_Id,Product_Name,Product_Quantity,Product_Amount)
now i have one button through which i need to add the data of textboxes(Product Name,Amount,Product Quantity,Product Price) which is filled by user runtime into the grid and i made filled 3 rows of grid and when i click on save button
protected void save_Click(object sender, EventArgs e)
{ SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["test"].ConnectionString);
con.Open();
SqlTransaction Trans = con.BeginTransaction();
SqlCommand com = new SqlCommand();
com.Connection = con;
com.Transaction = Trans;
foreach (GridViewRow g1 in grd.Rows)
{
try
{
if (Convert.ToInt32(g1.Cells[4].Text) > 10)
{
throw (new Exception("not greater than 10"));
}
com.CommandText = "insert into Tbl_Invoice(Product_Name,Amount,Product_Quantity,Product_Price) values ('" + g1.Cells[2].Text + "'," + Convert.ToDouble(g1.Cells[3].Text) + "," + Convert.ToInt32(g1.Cells[4].Text) + "," + Convert.ToDouble(g1.Cells[5].Text) + ")";
com.CommandText = "update Tbl_Product set Product_Quantity=Product_Quantity+" + Convert.ToInt32(g1.Cells[4].Text) + ", Product_Amount=Product_Amount+" + Convert.ToDouble(g1.Cells[3].Text) + " where Product_Name='" + g1.Cells[2].Text + "'";
com.ExecuteNonQuery();
Response.Write("both are written to tables");
}
catch (Exception ep)
{
Trans.Rollback();
Response.Write(ep.ToString());
Response.Write("neither are written to tables");
}
finally
{
Trans.Commit();
con.Close();
} } }
runtime 3 rows were added in grid repective (Product Name,Amount,Product Quantity,Product Price)
first row Shirt,125,1,125
second row tie,50,2,25
third row pant,1500,12,50
these are dummy values i put here which is in grid
now as per exception i created qty>10 should roll back whole transaction but it only roll back last row not the whole transaction as per acid properties either should execute all queries or no query .It has affected Tbl_Invoice and Tbl_Product
with first 2 rows which is not desired ..what i missed or written wrong
pls tell,regards