Single Transaction for block of code RRS feed

  • Question

  • Hi folks: 


    My application uses datasets (DataSet1.xsd) files and TableAdapters to access and update data on a single

    Microsoft SQL 2000 database.  I would like to wrap several called functions into one single transaction.  So that I

    can have atomicity for this transaction.  But the functions use differnet table adapters and I am having trouble figuring

    out how to using either TransactionScope or sqltransaction for that purpose.  I am even unable to put

     "imports system.transactions" into my code.  VB.NET does'nt recognize it.  So I have included the code below.

    Any suggestions on how to make this code into a single transaction?


    Thanks in advance,  Ed


    Public Function PurchaseNew() As Integer
            Dim PurchaseidRtn As Integer
            Dim i As Integer
            Dim Lc As Integer
            Lc = 0

                Using db As New PurchaseTableAdapters.tbl_PurchaseTableAdapter
                    db.AllOthers_Purchase_Insert(_ToProcureOffice, _RequestingOffice, _ReceivingOfficeNum, _ContractNumber, _OrderDate, _UnitCode, _AccountingCode, _OrderNum, _SUBB, _ProcRequestNum, _FormDate, _PurchaseOrder, _DeliveryOrder, _CreditCard, _ToSeller, _ShipTo, _InsideDeliveryReq, _TechnicalContact, _TelephoneNum, _Justification, _FOBPoint, _DiscountTerms, _RequiredDelivery, _NegotiatedDelivery, _ShipVia, _EstimatedFreight, _RecSources, _Title, _Signature, _LastModifiedBy, _LastModifiedDate, _PurchaseDone, _PurchaseNumber, _Region, _FiscalYear, PurchaseidRtn)
                End Using
            Catch e As Exception

                Return 1
            End Try

                Using db As New PurchaseTableAdapters.tbl_PurchaseLineItemsTableAdapter
                    Lc = 1
                    For i = 0 To 11
                        If _LineItems(i).Description <> "" Then
                            db.AllOthers_PurchaseLineItems_Insert(PurchaseidRtn, Lc, _
                               _LineItems(i).ActCode, _LineItems(i).Description, _
                               _LineItems(i).BOC, _
                               _LineItems(i).AccLine, _
                               _LineItems(i).Quantity, _
                               _LineItems(i).UnitIssue, _
                               _LineItems(i).UnitPrice, Nothing, Nothing, Nothing, Nothing, "Pending", Nothing, Nothing, _LastModifiedBy, _LastModifiedDate, 0, _TechnicalContact, 0, Nothing)
                            Lc = Lc + 1
                        End If
                    Next i
                End Using
            Catch e As Exception
                Return 1
            End Try
            Return 0
    End Function


    Monday, September 15, 2008 6:19 PM