Click here to Skip to main content
11,437,816 members (53,278 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET C#4.0 .NET4
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)
                {//created exception on qty>10 if filled by user so that it should roll back the whole transaction
                    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
Posted 7-Feb-13 23:33pm
Edited 8-Feb-13 1:02am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You can set a savepoint during the transaction and if it fails you can rollback to that save point. FOr more info you should look into SAVE TRANSACTION[^]

Good luck,
OI
  Permalink  
Comments
shivani 2013 at 8-Feb-13 5:24am
   
it is not serving my purpose........can you tell what mistake i did in this??????????
Orcun Iyigun at 8-Feb-13 5:44am
   
How come it is not serving the purpose? Have you read the documentation?? Once you set your savepoint you are basically done but seems like you did not understand it. In your code it just rollbacks the last query because the first two are successfully committed and the transactions for them are re-instantiated. Try defining your Transaction before the loop and commit in your finally statement..
shivani 2013 at 8-Feb-13 6:06am
   
I defined Transaction before the loop and commit in finally statement..i updated my question but again it is doing same and with error
"This SqlTransaction has completed; it is no longer usable.".

pls tell wat to modify here
regards
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Only the update statement is executed.
You need
com.CommandText = "insert into..."
com.ExecuteNonQuery();
com.CommandText = "update..."
com.ExecuteNonQuery();
Also, you have Trans.Commit() in finally block, which may be called after Trans.Rollback().


I havent noticed there is a loop. Try something like this..
 
try
{
 
  foreach...
  {
  ...
  com.CommandText = "insert into..."
  com.ExecuteNonQuery();
  com.CommandText = "update..."
  com.ExecuteNonQuery();
  }
 
  Trans.Commit()
}
catch ...
{
  Trans.Rolback()
}
 
  Permalink  
v4
Comments
shivani 2013 at 9-Feb-13 4:13am
   
i have already done com.executenonquery for both
still i am not getting desired result

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.150506.1 | Last Updated 9 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100