Using RollBack Transactions in VB.NET

Question

Hi..

Is there a method for using rollback transactions in VB.NET. In my application i am using 1 sql statement and 1 stored procedure first for deleting the entire table and then inserting the grid details using insert stored procedure.

My problem is when first sql statement is executed and some problem occured then the table is deleted and insertion also will not be done. So i need to use transactions in .NET , any one have solutuon please reply asap.

I am using database layer also, since it is vb
Thanks and Regards

Sankar
Cochin

Sairam · Answer

I have just provided you the sample code. to set the SqlClient.SqlTransaction to perform commit and rollback options. Make changes according to your need dim err Dim gTrans As SqlClient.SqlTransaction Dim gConnection As New System.Data.SqlClient.SqlConnection Dim SqlSelectTrans As System.Data.SqlClient.SqlCommand 'SEt the connection String '************************** gConnection.ConnectionString = ConnectionString try If gConnection.State = ConnectionState.Closed Then gConnection.Open() End If Catch sqlEx As SqlClient.SqlException Exit Function End If End Try try gTrans = gConnection.BeginTransaction 'make a call to your StoredProcedure and insertion query 'Set the below command SqlSelectTrans.Transaction = gTrans SqlSelectTrans.Connection = gConnection SqlSelectTrans.ExecuteNonquery() catch ex as exception err=ex.message End try 'Final Commit/Rollback will take place here If Err  Then gTrans.Rollback() gConnection.Close() Else gTrans.Commit() End If

Sanjeeb Lenka · Answer

refer to this link it may help you: http://www.vb-helper.com/howto_net_db_transaction.html