-
May 10th, 2021, 08:30 AM
#1
Thread Starter
New Member
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
-
May 10th, 2021, 09:25 AM
#2
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
-
May 10th, 2021, 11:40 AM
#3
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|