BeginTranaction / Commit /Rollback in vb.net 2008

[zurehman]

Hi , members

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.

Thanks
Zia

[tqmd1]

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
http://www.vb-helper.com/howto_net_db_transaction.html
http://authors.aspalliance.com/aspxtreme/sys/data/sqlclient/SqlConnectionClassBeginTransaction.aspx

[jodesengi]

hi, thanks for your code but
how if you have to insert into two or multiple tables ?

[Reverend Jim]

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.

[Naveen_8]

hi

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
thanks