please tell me how can i control Data Entry in 3 different table with BeginTranaction / Commit /Rollback in vb.net 2008.
i am using sql server 2000 at back end and used SqlClient,SqlConnection, ExecNonQuery method.
Public Sub RunSqlTransaction(myConnString As String)
Dim myConnection As New SqlConnection(myConnString)
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
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"
Console.WriteLine("Both records are written to database.")
Catch e As Exception
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.")
Console.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while inserting the data.")
Console.WriteLine("Neither record was written to database.")
End Sub 'RunSqlTransaction
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.