locked
Help Needed On ADO.NET Transaction? RRS feed

  • Question

  • Hi
    
    I have created two tables so far in Access 2007 - raised stock (new stock) 
    and current stock.
    I would like to enter new stock details in the raised stock table, then this 
    will be automatically added to the current stock table by pressing a button.
    I know that I need to write some ADO.NET transactions using VB.
    
    Please could you give me any examples on how to do this?
    
    Thanks for all your help,
    Louisa Holt
    Friday, April 20, 2007 8:24 AM

Answers

  • Holts Shoes,

     

    You can begin, commit and roll back a transaction using the Connection and Transaction objects. The following steps are used to perform a transaction.

    To perform a transaction

    1. Call the BeginTransaction method of the Connection object to mark the start of the transaction. The BeginTransaction method returns a reference to the Transaction. This reference is assigned to the Command objects that are enlisted in the transaction.

    2. Assign the Transaction object to the Transaction property of the Command to be executed. If a Command is executed on a Connection with an active Transaction, and the Transaction object has not been assigned to the Transaction property of the Command, an exception is thrown.

    3. Execute the required commands.

    4. Call the Commit method of the Transaction object to complete the transaction, or call the Rollback method to cancel the transaction.

     

    The following code example demonstrates transactional logic using ADO.NET with Microsoft® SQL Server™. Since you have the Access database, please change the connection string and the Sql classes into OLEDB:

     

    Code Snippet

    Dim myConnection As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;")

    myConnection.Open()

     

    ' Start a local transaction.

    Dim myTrans As SqlTransaction = myConnection.BeginTransaction()

     

    ' Enlist the command in the current transaction.

    Dim myCommand As SqlCommand = myConnection.CreateCommand()

    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()

      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

     

    Friday, April 27, 2007 7:19 AM