DataAdapter with Transaction (.net 1.1)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I created the following code, it is working, simple update of DataTable,
populating data to datatable/dataset is outside/before.

Problem: when I tried to use the transaction it doesn't.
It prompt error message "Execute requires the command to have a transaction
object when the connection assigned to the command is in a pending local
transaction. The Transaction property of the command has not been
initialized."

Kindly please advice about this.

Have a nice day,
Martin

--------------------------------------------
Function SetCommand(ByVal dataTable As DataTable, ByVal commandText As
String, ByVal connectionString As String, Optional ByVal isInsert As Boolean
= True, Optional ByVal isUpdate As Boolean = True, Optional ByVal isDelete As
Boolean = True) As Boolean
Dim result As Boolean

Dim connection As OleDbConnection
connection = New OleDbConnection(connectionString)

Dim adapter As OleDbDataAdapter
adapter = New OleDbDataAdapter(commandText, connection)

'Dim transaction As OleDbTransaction

Dim commandBuilder As OleDbCommandBuilder

If isInsert Then
commandBuilder = New OleDbCommandBuilder(adapter)
adapter.InsertCommand = commandBuilder.GetInsertCommand
'adapter.InsertCommand.Transaction = transaction
End If
If isUpdate Then
commandBuilder = New OleDbCommandBuilder(adapter)
adapter.UpdateCommand = commandBuilder.GetUpdateCommand
'adapter.UpdateCommand.Transaction = transaction
End If
If isDelete Then
commandBuilder = New OleDbCommandBuilder(adapter)
adapter.DeleteCommand = commandBuilder.GetDeleteCommand
'adapter.DeleteCommand.Transaction = transaction
End If

connection.Open()
'transaction = connection.BeginTransaction
'Try
adapter.Update(dataTable)
'transaction.Commit()
result = True
'Catch ex As Exception
'transaction.Rollback()
'result = False
'End Try
connection.Close()
connection.Dispose()

Return result
End Function
----------------------------------------------
 
Aha... I did it... by just re-arrange the codes:

----------------------------------------
Function ExecuteCommand(ByVal dataTable As DataTable, ByVal commandText As
String, ByVal connectionString As String, Optional ByVal isInsert As Boolean
= True, Optional ByVal isUpdate As Boolean = True, Optional ByVal isDelete As
Boolean = True) As Boolean
Dim result As Boolean

Dim connection As OleDbConnection
connection = New OleDbConnection(connectionString)

Dim adapter As OleDbDataAdapter
adapter = New OleDbDataAdapter(commandText, connection)

Dim commandBuilder As OleDbCommandBuilder
commandBuilder = New OleDbCommandBuilder(adapter)

If isInsert Then adapter.InsertCommand =
commandBuilder.GetInsertCommand
If isUpdate Then adapter.UpdateCommand =
commandBuilder.GetUpdateCommand
If isDelete Then adapter.DeleteCommand =
commandBuilder.GetDeleteCommand

Dim transaction As OleDbTransaction
connection.Open()
transaction = connection.BeginTransaction()

If isInsert Then adapter.InsertCommand.Transaction = transaction
If isUpdate Then adapter.UpdateCommand.Transaction = transaction
If isDelete Then adapter.DeleteCommand.Transaction = transaction

Try
adapter.Update(dataTable)
transaction.Commit()
result = True
Catch ex As Exception
transaction.Rollback()
result = False
Finally
connection.Close()

connection.Dispose()
connection.Dispose()
commandBuilder.Dispose()

connection = Nothing
connection = Nothing
commandBuilder = Nothing
End Try

Return result
End Function
 
Hi,
If you have found my post useful then please do click yes on "Was this post
helpful to you".

Thanks and Regards,
Manish Bafna.
MCP and MCTS.
 
Back
Top