fiogf49gjkf0d The process I am trying to create inserts new account records into the Address and Account tables. But, I don't want to create a new Account without an Address, so I need to use a Transaction.
If I pass the SQL Insert statements to the OleDB provider individually, they execute, successfully. But, if I pass them to the provider in a oledb transaction, the statement fails to parse the SQL on the first command and the transaction is rolled back. 
Is the SLX OleDB provider able to handle transaction processing. And, if so, is it configured differently than a Microsoft's OleDB transaction?
Public Sub ExecuteTransaction(ByVal connectionString As String)
Using connection As New OleDbConnection(connectionString)
Dim command As New OleDbCommand()
Dim transaction As OleDbTransaction
' Set the Connection to the new OleDbConnection.
command.Connection = connection
' Open the connection and execute the transaction.
Try
connection.Open()
' Start a local transaction with ReadCommitted isolation level.
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
' Assign transaction object for a pending local transaction.
command.Connection = connection
command.Transaction = transaction
' Execute the commands.
command.CommandText = _
"Insert into Address(AddressID, Address1, City, State, PostalCode) VALUES ('a6UJ9A001WEC','1 Main Street','Reading','PA','19610'  "
command.ExecuteNonQuery()
command.CommandText = _
"Insert into Account(AccountID, AddressID, Account) VALUES ('A6UJ9A000VWK', 'a6UJ9A001WEC', 'Account Name')"
command.ExecuteNonQuery()
' Commit the transaction.
transaction.Commit()
Console.WriteLine("Both records are written to database.")
Catch ex As Exception
Console.WriteLine(ex.Message)
' Try to rollback the transaction
Try
transaction.Rollback()
Catch
' Do nothing here; transaction is not active.
End Try
End Try
' The connection is automatically closed when the
' code exits the Using block.
End Using
End Sub
Please advise?
Thanks!!!!
|