How I use transaction to UPDATE 2 MS Access tables?

[NewComer]

I would like to use transaction (start, complete, accept change) to UPDATE 2 MS Access tables, but I don't know how:

Dim dbConn = New OleDbConnection(strAccess)
Dim cmdMSA As OleDbCommand
dbConn.Open()

' Update GuestList
cmdText = "UPDATE GuestList SET Conf = '123456789' WHERE Name = 'TESTING'"

cmdMSA = New OleDbCommand(cmdText, dbConn)
Try
If Not (cmdMSA.ExecuteScalar() = Nothing) Then
f_Failed = True
End If
Catch ex As Exception
DisplayError(Now().ToString() & " Can't UPDATE GuestList")
End Try

cmdMSA.Dispose()

' Update CompanyList
cmdText = "UPDATE CompanyList SET Name = 'ABC' WHERE Phone = '514-222-3333'"

cmdMSA = New OleDbCommand(cmdText, dbConn)
Try
If Not (cmdMSA.ExecuteScalar() = Nothing) Then
f_Failed = True
End If
Catch ex As Exception
DisplayError(Now().ToString() & " Can't UPDATE CompanyList")
End Try

cmdMSA.Dispose()

dbConn.Close()

Thank you for any help

 

[demausdauth]

What is a transaction? Essentially, it allows you to group SQL statements and then commit them all at once. So for your code you would want to wrap everything with the transaction.

Typed it all from memory so you'll want to double check everything, but otherwise this is the jist of how I would approach transactions. You may even be able to use your f_failed variable, I just use IsOk because that is what I have used in the past.

Dim dbTran As OleDbTransaction
Dim IsOk As Boolean = True

Dim dbConn = New OleDbConnection(strAccess)
Dim cmdMSA As OleDbCommand
dbConn.Open()

'Start Transaction
dbTran = dbConn.BeginTransaction()

' Update GuestList
cmdText = "UPDATE GuestList SET Conf = '123456789' WHERE Name = 'TESTING'"

cmdMSA = New OleDbCommand(cmdText, dbConn)
Try
     If Not (cmdMSA.ExecuteScalar() = Nothing) Then
     f_Failed = True
End If
Catch ex As Exception
     DisplayError(Now().ToString() & " Can't UPDATE GuestList")
     IsOk = False
End Try

cmdMSA.Dispose()

'No need to try and process this if the first SQL statement failed
If IsOk Then
      ' Update CompanyList
      cmdText = "UPDATE CompanyList SET Name = 'ABC' WHERE Phone = '514-222-3333'"

      cmdMSA = New OleDbCommand(cmdText, dbConn)
      Try
           If Not (cmdMSA.ExecuteScalar() = Nothing) Then
                 f_Failed = True
           End If
      Catch ex As Exception
           DisplayError(Now().ToString() & " Can't UPDATE CompanyList")
           IsOk = False
      End Try

      cmdMSA.Dispose()
End If

If IsOk Then
      dbTran.Commit()
Else
      dbTran.Rollback()
End If

dbConn.Close()

 

[NewComer]

Re:

I did try as you suggested, but after running debug it had the Catch Exception at the first execution of:

cmdMSA = New OleDbCommand(cmdText, dbConn)
Try
If Not (cmdMSA.ExecuteScalar() = Nothing) Then
f_Failed = True
End If
Catch ex As Exception
IsOk = False
DisplayError(Now().ToString() & " Can't UPDATE GuestList")
End Try

The error show:

ExecuteScalar implies that the order consisting of a transaction when the attributed connection to the order is located in a local transaction in expectation. The property Transaction of the order was not initialized

*) I forgot to mention that my cmdText is UPDATE more items (but no syntax error) than I showed only one in the original Message, just because I want to simplify my question!

Thank for help but do you have any suggestion?

 

[demausdauth]

aaarrrggghhh -- forgot something very important

cmdMSA = New OleDbCommand(cmdText, dbConn)

change to

cmdMSA = New OleDbCommand(cmdText, dbTran.Connection, dbTran)

 

[NewComer]

It works!

It works as a charm!

Many thanks for providing help

 

[cjard]

using TableAdapters, this code would be simplified to:

Using ts as New TransactionScope
  myTableAdapter.UpdateGuestList()
  myTableAdapter.UpdateCompanyList()
  ts.Complete()
End Using