Public Sub RunSqlTransaction(myConnString As String) Dim myConnection As New SqlConnection(myConnString) myConnection.Open() Dim myCommand As SqlCommand = myConnection.CreateCommand() Dim myTrans As SqlTransaction ' Start a local transaction myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted, "SampleTransaction") ' Must assign both transaction object and connection ' to Command object for a pending local transaction myCommand.Connection = myConnection myCommand.Transaction = myTrans Try myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')" myCommand.ExecuteNonQuery() myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')" myCommand.ExecuteNonQuery() myTrans.Commit() Console.WriteLine("Both records are written to database.") Catch e As Exception Try myTrans.Rollback("SampleTransaction") Catch ex As SqlException If Not myTrans.Connection Is Nothing Then Console.WriteLine("An exception of type " & ex.GetType().ToString() & _ " was encountered while attempting to roll back the transaction.") End If End Try Console.WriteLine("An exception of type " & e.GetType().ToString() & _ "was encountered while inserting the data.") Console.WriteLine("Neither record was written to database.") Finally myConnection.Close() End Try End Sub 'RunSqlTransaction
For more help please visit following links
hi, thanks for your code but
how if you have to insert into two or multiple tables ?
I don't understand the problem. You do it the same way you would insert into one table. If the question is about transactions it's the same as above. You create the transaction then do the inserts inside a try/catch block then either rollback or coommit the transaction dependinig on whether or not you trapped an error.
Edited 1 Year Ago by Reverend Jim
i too have the same problem
i am stiring some data in tables in a sub procedure...but it calls a function to generate a number and if its new number it will get updated in master file...
when i am using this commit and rollback the data in the procedure is getting saved but the records which are need to be updated in the called function are not getting saved...
how to achieve this
Hi. I have a form with list box : lst_product, datagridview : grd_order and button: btn_addline. lst_product has a list of product ids selected from database (MS Acess 2013) , grd_order is by default empty except for 2 headers and btn_addline adds rows to grd_order.
Private Sub btn_addline_Click(ByVal ...
OK, so HostGator for some reason no longer allows gcc/g++ access unless you have a Designated Server account, which is a lot of money to spend just to compile my "Hello World" program. Thus I figured I'd compile at home, then upload. Program is your regular old bare-bones Hello World ...
Hi, as I was told that my code doesn’t scale well at all, I thought perhaps I’d try to get a better understanding of interfaces/abstract classes and classes and the relationship between them.
I don’t want at this stage work on a big separate project as I've already got plenty ...