Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: .NET ADO.NET C#4.0
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)
    {
        foreach (GridViewRow g1 in grd.Rows)
        {
 

            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;
 
            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();
                Trans.Commit();
                Response.Write("both are written to tables");
            }
            catch (Exception ep)
            {
                Trans.Rollback();
                Response.Write(ep.ToString());
                Response.Write("neither are written to tables");
            }
            finally
            {
               
                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 6-Feb-13 0:37am
Edited 7-Feb-13 23:49pm
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
here is the code to use SqlTransaction class for multiple query execution.
 

SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;uid=sa;pwd=sa;");
myConnection.Open();
 
// Start a local transaction
SqlTransaction myTrans = myConnection.BeginTransaction();
 
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;
try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "delete * from Region where RegionID=101";
 
// Attempt to commit the transaction. 
myCommand.ExecuteNonQuery();
myTrans.Commit();
Response.Write("Both records are written to database.");
}
catch (Exception ep)
{
// Attempt to roll back the transaction. 
myTrans.Rollback();
Response.Write(ep.ToString());
Response.Write("Neither record was written to database.");
}
finally
{
myConnection.Close();
}
 

 
-----------------------------------
 
or for further detail follow this url
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx
  Permalink  
v2
Comments
shivani 2013 at 6-Feb-13 6:57am
   
this is done only on a single table .......i am not able to achieve this in multiple tables.pls tell
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Here is the complete article for you. This should get you started:
 
A Beginner's Tutorial for Understanding Transactions and TransactionScope in ADO.NET[^]
  Permalink  
Comments
shivani 2013 at 6-Feb-13 7:09am
   
sir it is done on a single table i want in multiple table
Rahul Rajat Singh at 6-Feb-13 7:31am
   
It will be the same. If you are using the transactions then just associate the same transaction with both the commands that update the two tables. If you are using transactionscope then simply do everything inside the using block.
shivani 2013 at 8-Feb-13 5:48am
   
i have updated my question see to it

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 359
1 Richard MacCutchan 320
2 OriginalGriff 280
3 Jared Sanow 145
4 Snehasish_Nandy 115
0 Sergey Alexandrovich Kryukov 5,939
1 OriginalGriff 4,885
2 CPallini 2,473
3 Abhinav S 1,507
4 Richard MacCutchan 1,437


Advertise | Privacy | Mobile
Web02 | 2.8.140814.1 | Last Updated 8 Feb 2013
Copyright © CodeProject, 1999-2014
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