Using transaction object

• • Reply

  14 years ago

  by lexian

  Alexis Méndez

  , Puerto Rico

  Joined 14 years ago

  Hi,

  Is there any way that I can execute different commands from different procedures into one transaction object?

  The meaning of this is because in one System transaction there can be made different sales or rents in which each one of them updates the client that required the transaction and the items that are being rented or sold in the transaction, if the user wants to cancel the transaction (rents or sales) just rollback the transaction object to cancel all the commands made in that system transaction.

  ¿Is there is any way of doing these with the transaction objects? ¿Is there is any other way of doing something like this?

  What I want to do is something like this:
  

  Imports System.Data
  Imports System.Data.Odbc
  
  
  Public Class TransactionsWindow
  
  Dim cn As OdbcConnection
  Dim SystemTransaction As OdbcTransaction
  
  Private Sub TransactionsWindow_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Call SetTransaction()
  End Sub
  
  Public Sub SetTransaction()
      cn = New OdbcConnection("Connection String")
      cn.Open()
  
      Try
  
          SystemTransaction = cn.BeginTransaction
  
      Catch ex As Exception
          MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
      Finally
          cn.Close()
      End Try
  End Sub
  
  Private Sub AddRentBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddRentBtn.Click
      cn = New OdbcConnection("Connection String")
      cn.Open()
  
      Try
  
          Dim AddRentCmd As New OdbcCommand("Insert Rent Query", cn)
          Dim UpdateItemCmd As New OdbcCommand("Update Item has Rented Query", cn)
  
          AddRentCmd.Transaction = SystemTransaction
          UpdateItemCmd.Transaction = SystemTransaction
  
          AddRentCmd.ExecuteNonQuery()
          UpdateItemCmd.ExecuteNonQuery()
  
      Catch ex As Exception
          MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
      Finally
          cn.Close()
      End Try
  End Sub
  
  Private Sub AddSaleBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddSaleBtn.Click
      cn = New OdbcConnection("Connection String")
      cn.Open()
  
      Try
  
          Dim AddSaleCmd As New OdbcCommand("Insert Sale Query", cn)
          Dim UpdateItemCmd As New OdbcCommand("Update Item Inventory Query", cn)
  
          AddSaleCmd.Transaction = SystemTransaction
          UpdateItemCmd.Transaction = SystemTransaction
  
          AddSaleCmd.ExecuteNonQuery()
          UpdateItemCmd.ExecuteNonQuery()
  
      Catch ex As Exception
          MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
      Finally
          cn.Close()
      End Try
  End Sub
  
  Private Sub NewTransactionBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewTransactionBtn.Click
      Try
  
          SystemTransaction.Commit()
          Call SetTransaction()
  
      Catch ex As Exception
          If Not SystemTransaction Is Nothing Then
              SystemTransaction.Rollback()
          End If
          MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
      End Try
  End Sub
  
  Private Sub CancelTransactionBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CancelTransactionBtn.Click
      Try
  
          SystemTransaction.Rollback()
          Call SetTransaction()
  
      Catch ex As Exception
          MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
      End Try
  End Sub
  
  
  End Class
  

  
  

  Report abuse
• • Reply

  14 years ago

  by konikula

  Matej Kasper

  Trutnov, Czech Republic

  Joined 15 years ago

  I am sure it is possible.
  But in all that 'inside' working procedures you must use the same connection, cannot be done by having in every procedure open, close between transaction start and submit... (so think I :)
  
  like this?
  
  con.open
  transaction.start
  
  proc1 - update etc
  proc2 - insert, read, insert, read...
  proc...
  
  transaction.commit
  con.close
  
  
  
  
  
  
  
  
  
  
  grogen dag
  
  
  
  
  
  
  
  
  
  

  Report abuse
• • Reply

  14 years ago

  by lexian

  Alexis Méndez

  , Puerto Rico

  Joined 14 years ago

  Hi,

  I tried doing that one time, but it threw the exception that it doesn't belong to the same connection or something like that, even using the same connection.

   

  Report abuse