We help IT Professionals succeed at work.

VB.net Error in an all or nothing scenario to add data to an Access table

Murray Brown
Murray Brown asked
on
Hu

I am using the following code to append all the lines of a DataGridView to
an Access table in an all or nothing scenarion
I added the four lines marked '+++++++ Transaction and rollback ++++++++
but get the error shown in the following image
 error
Sub AppendTransactions()

        Dim oBatchID As Integer
        Dim oTransactionID As Long
        Dim oLinkID As Integer
        Dim dTransactionDate As Date
        Dim dCaptureDate As Date
        Dim sPPeriod As String
        Dim sFinYear As String
        Dim sGDC As String
        Dim sReference As String
        Dim sDescription As String
        Dim sAccountNumber As String
        Dim sLinkAcc As String
        Dim oAmount As String
        Dim oTaxType As Integer
        Dim oTaxAmount As Decimal
        Dim sUserID As String
        Dim oSupplierID As Integer
        Dim oEmployeeID As String
        Dim sDescription2 As String
        Dim sDescription3 As String
        Dim sDescription4 As String
        Dim sDescription5 As String
        Dim blnPosted As Boolean
        Dim blnAccounting As Boolean
        Dim blnVoid As Boolean
        Dim sTransactionType As String
        Dim oDR_CR As String
        Dim oContraAccount As String
        Dim oDescriptionCode As String
        Dim oDocType As String
        Dim oSupplierName As String

        Dim i As Integer
        Dim cmd As OleDbCommand

        Dim cn As New OleDbConnection(ConnectionString)
        Dim trans As OleDb.OleDbTransaction '+++++++ Transaction and rollback ++++++++

        Try
            '    '// open the connection
            cn.Open()

            ' Make the transaction.
            trans = cn.BeginTransaction(IsolationLevel.ReadCommitted)   '+++++++ Transaction and rollback ++++++++

            For i = 0 To Me.DGV_Transactions.RowCount - 2 'Remember that it there is an extra ghost row in the DataGridView

                blnAppend_Failed = False
                Dim sSQL As String

                oBatchID = Me.DGV_Transactions.Rows(i).Cells(0).Value
                oTransactionID = Me.DGV_Transactions.Rows(i).Cells(1).Value
                oLinkID = Me.DGV_Transactions.Rows(i).Cells(2).Value
                dTransactionDate = Me.DGV_Transactions.Rows(i).Cells(3).Value
                dCaptureDate = Me.DGV_Transactions.Rows(i).Cells(4).Value
                sPPeriod = Me.DGV_Transactions.Rows(i).Cells(5).Value
                sFinYear = Me.DGV_Transactions.Rows(i).Cells(6).Value
                sGDC = Me.DGV_Transactions.Rows(i).Cells(7).Value
                sReference = Me.DGV_Transactions.Rows(i).Cells(8).Value
                sDescription = Me.DGV_Transactions.Rows(i).Cells(9).Value
                sAccountNumber = Me.DGV_Transactions.Rows(i).Cells(10).Value
                sLinkAcc = Me.DGV_Transactions.Rows(i).Cells(11).Value
                oAmount = Me.DGV_Transactions.Rows(i).Cells(12).Value
                oTaxType = Me.DGV_Transactions.Rows(i).Cells(13).Value
                oTaxAmount = Me.DGV_Transactions.Rows(i).Cells(14).Value
                sUserID = Me.DGV_Transactions.Rows(i).Cells(15).Value
                oSupplierID = Me.DGV_Transactions.Rows(i).Cells(16).Value
                oEmployeeID = Me.DGV_Transactions.Rows(i).Cells(17).Value
                sDescription2 = Me.DGV_Transactions.Rows(i).Cells(18).Value
                sDescription3 = Me.DGV_Transactions.Rows(i).Cells(19).Value
                sDescription4 = Me.DGV_Transactions.Rows(i).Cells(20).Value
                sDescription5 = Me.DGV_Transactions.Rows(i).Cells(21).Value
                blnPosted = Me.DGV_Transactions.Rows(i).Cells(22).Value
                blnAccounting = Me.DGV_Transactions.Rows(i).Cells(23).Value
                blnVoid = Me.DGV_Transactions.Rows(i).Cells(24).Value
                sTransactionType = Me.DGV_Transactions.Rows(i).Cells(25).Value
                oDR_CR = Me.DGV_Transactions.Rows(i).Cells(26).Value
                oContraAccount = Me.DGV_Transactions.Rows(i).Cells(27).Value
                oDescriptionCode = Me.DGV_Transactions.Rows(i).Cells(28).Value
                oDocType = Me.DGV_Transactions.Rows(i).Cells(29).Value
                oSupplierName = Me.DGV_Transactions.Rows(i).Cells(30).Value

                sSQL = "INSERT INTO Transactions ( BatchID, TransactionID, LinkID, [Capture Date], [Transaction Date], PPeriod, [Fin Year], GDC, Reference, Description, AccNumber, "
                sSQL = sSQL & "LinkAcc, Amount, TaxType, [Tax Amount], UserID, SupplierID, EmployeeID, [Description 2], [Description 3], [Description 4], "
                sSQL = sSQL & "[Description 5], Posted, Accounting, Void, [Transaction Type], DR_CR, [Contra Account], [Description Code], [DocType], [SupplierName] ) "
                sSQL = sSQL & "SELECT " & CStr(oBatchID) & " as Expr1, " & CStr(oTransactionID) & " as Expr2, " & CStr(oLinkID) & " as Expr3, "
                sSQL = sSQL & "#" & CStr(dCaptureDate) & "# As MyDate1, #" & CStr(dTransactionDate) & "# As MyDate2, "
                'sSQL = sSQL & "Format('" & Format(dCaptureDate, "yyy-MMM-dd") & "',""Short Date"") As MyDate1, Format('" & Format(dTransactionDate, "yyy-MMM-dd") & "',""Short Date"") As MyDate2, "
                sSQL = sSQL & "" & sPPeriod & " as Expr4, " & sFinYear & " As Expr5, " & "'" & sGDC & "' as Expr5a, "
                sSQL = sSQL & "'" & sReference & "' as Expr6, '" & SQLConvert(sDescription) & "' As Expr7, "
                sSQL = sSQL & "'" & sAccountNumber & "' as Expr8, '" & sLinkAcc & "' As Expr9, "
                sSQL = sSQL & "" & CStr(oAmount) & " as Expr10, " & CStr(oTaxType) & " As Expr11, "
                sSQL = sSQL & "" & CStr(oTaxAmount) & " as Expr12, '" & sUserID & "' As Expr13, "
                sSQL = sSQL & "" & CStr(oSupplierID) & " as ExprNew, " & CStr(oEmployeeID) & " As Expr15, "
                sSQL = sSQL & "'" & SQLConvert(sDescription2) & "' as Expr16, '" & SQLConvert(sDescription3) & "' As Expr17, "
                sSQL = sSQL & "'" & SQLConvert(sDescription4) & "' as Expr18, '" & SQLConvert(sDescription5) & "' As Expr19, "
                sSQL = sSQL & "" & CStr(blnPosted) & " as Expr20, " & CStr(blnAccounting) & " As Expr21, "
                sSQL = sSQL & "" & CStr(blnVoid) & " as Expr22, '" & CStr(sTransactionType) & "' As Expr23, "
                sSQL = sSQL & "'" & oDR_CR & "' as Expr24, '" & oContraAccount & "' As Expr25, '" & oDescriptionCode & "' As Expr26, "
                sSQL = sSQL & "'" & oDocType & "' as Expr27, '" & SQLConvert(oSupplierName) & "' As Expr28"


                '// define the sql statement to execute
                'Dim cmd As New OleDbCommand(sSQL, cn)
                cmd = New OleDbCommand(sSQL, cn)
                cmd.ExecuteNonQuery()
                'blnAppend_Failed = False

                'Dim cmd As New OleDbCommand
                'cmd.CommandText = sSQL
                'cmd.ExecuteNonQuery()
            Next



            trans.Commit()  '+++++++ Transaction and rollback ++++++++

            cmd = Nothing



            'System.Threading.Thread.Sleep(1200) 'A little append to make sure it hits the database before any further code is written

        Catch ex As Exception
            trans.Rollback() '+++++++ Transaction and rollback ++++++++

            If Me.Label_Error_Batches.Text = "." Then
                Me.Label_Error_Batches.Text = CStr(oBatchID)
            Else
                Me.Label_Error_Batches.Text = Me.Label_Error_Batches.Text & "," & CStr(oBatchID)
            End If

            MsgBox(ex.Message)
            blnAppend_Failed = True
            blnCRITICAL_ERROR = True
            Call DeleteBatch(oBatchID)
            Call Insert_Batch_Error(oBatchID)

        Finally

            If Not IsNothing(cmd) Then
                cmd.Dispose()
            End If

            If Not IsNothing(cn) Then
                cn.Dispose()
            End If

        End Try
    End Sub

Open in new window

Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2015

Commented:
Assign the transaction object to the command object before you execute the query:

command.Transaction = trans;

Open in new window

Most Valuable Expert 2011
Top Expert 2015
Commented:
Sorry:

cmd.Transaction = trans;

Open in new window

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Great. Worked perfectly. Thanks very much