Results 1 to 3 of 3

Thread: Using Transactions with OLEDB Adapters

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2021
    Posts
    2

    Using Transactions with OLEDB Adapters

    Hi,
    I have an Windows Forms application that uses an Access database. I am trying to implement Commit & Rollback into parts of it where it makes multiple updates to the db. I cannot get it to work with Data Adapters. To work out what I need to do I have just created a very simple piece of code to test out the various things I want to do (see below). It works fine on the INSERT statements however when I want to update a Dataset using an Adapter it fails at the line: oDA.UpdateCommand.Transaction = tra. I get the error message: Object Reference not set to an instance of an object. When I stop the code at that line tra does exist. Can someone please let me know how to get this working.


    Code:
        Private Sub btnStart_Click(sender As Object, e As EventArgs) Handles btnStart.Click
    
            Dim cmd As OleDb.OleDbCommand
            Dim conDb As New OleDb.OleDbConnection
    
            Dim intRows As Int16
            Dim intTest2Count As Int16
    
            Dim oCB As OleDb.OleDbCommandBuilder
            Dim oDA As OleDb.OleDbDataAdapter
            Dim oDT As DataTable
            Dim oDtS As New DataSet()
    
            Dim strSQL As String
            Dim strTable As String
    
            Dim tra As OleDb.OleDbTransaction
    
            conDb.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\Users\Nigel\Documents\Tms\VB.Net\Development\Tms_data.accdb'"
    
            conDb.Open
                                                        ' Clear tblTest
            strSQL = "DELETE * FROM tblTest"
            cmd = New OleDb.OleDbCommand(strSQL, conDb)
            intRows = cmd.ExecuteNonQuery()
    
                                                        ' Put some data from tblTest2 into a dataset
            strSQL = "SELECT * FROM tblTest2 WHERE TestCountryID = 1"
            strTable = "Test2"
    
            oDA = New OleDb.OleDbDataAdapter()
            oDA.SelectCommand = New OleDb.OleDbCommand(strSQL, conDb)
            oDA.Fill(oDtS, strTable)
            intTest2Count = oDtS.Tables(strTable).Rows.Count
    
                                                        ' Change a piece of data in tblTest2
            oDtS.Tables(strTable).Rows(1).Item("End1") = "Fred"
    
            tra = conDb.BeginTransaction
    
            Try
                                                        ' Insert 3 rows into tblTest
                strSQL = "INSERT INTO tblTest (ID, TableName, ColumnName, AliasValue, MasterTableID) " & _
                    "VALUES (1000, 'tblCommit', 'FirstName', 'Tom', 10000)"
    
                cmd = New OleDb.OleDbCommand(strSQL, conDb, tra)
                intRows = cmd.ExecuteNonQuery()
    
                strSQL = "INSERT INTO tblTest (ID, TableName, ColumnName, AliasValue, MasterTableID) " & _
                    "VALUES (1001, 'tblCommit', 'FirstName', 'Dick', 10001)"
    
                cmd = New OleDb.OleDbCommand(strSQL, conDb, tra)
                intRows += cmd.ExecuteNonQuery()
    
                strSQL = "INSERT INTO tblTest (ID, TableName, ColumnName, AliasValue, MasterTableID) " & _
                    "VALUES (1002, 'tblCommit', 'FirstName', 'Harry', 10002)"
    
                cmd = New OleDb.OleDbCommand(strSQL, conDb, tra)
                intRows += cmd.ExecuteNonQuery()
    
                                                        ' Write the updated dataset back to tblTest2
                oCB = New OleDb.OleDbCommandBuilder(oDA)
                oDA.ContinueUpdateOnError = False
                oDA.UpdateCommand.Transaction = tra     ' Error generated here
                oDA.UpdateCommand = oCB.GetUpdateCommand
                oDA.UpdateCommand.Connection = conDb
                oDA.Update(oDtS, strTable)
    
            Catch ex As Exception
                MsgBox("Error identified. Rolling Back." & vbNewLine & ex.Message, vbOKOnly, TMS_SystemName)
                tra.Rollback
                Exit Sub
            End Try
    
            tra.Commit
            MsgBox(CStr(intTest2Count) & " rows in Dataset." & vbNewLine & CStr(intRows) & " rows added.", vbOKOnly, TMS_SystemName)
    
        End Sub

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Using Transactions with OLEDB Adapters

    tra isn't the problem on that line. It's one of the other objects... on the other side of the equals...you have three objects there:
    oDa
    oDA.UpdateCommand
    oDA.UpdateCommand.Transaction

    One of those is Nothing, causing the error.

    Given what the next line is... it should be obvious which one of those is the problem.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2021
    Posts
    2

    Re: Using Transactions with OLEDB Adapters

    tg,

    I see what you mean I am setting the Transaction property of oDA.UpdateCommand before I have assigned a value to it. So I switched the 2 lines round:

    Code:
                oDA.UpdateCommand = oCB.GetUpdateCommand
                oDA.UpdateCommand.Transaction = tra
                oDA.UpdateCommand.Connection = conDb
                oDA.Update(oDtS, strTable)

    and now I get the error below on the line: oDA.UpdateCommand = oCB.GetUpdateCommand

    "ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."

    Any ideas?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width