DataAdapter with Transaction (.net 1.1) -- dotnet ado.net

prof_martin
1/30/2007 4:19:01 PM

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

Manish Bafna
1/30/2007 9:14:00 PM

Hi,
You need to write following after "Dim transaction As OleDbTransaction":
' Start a local transaction with ReadCommitted isolation level.
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted).

You will find following urls useful
http://www.builderau.com.au/program/mysql/soa/Implementing_SQL_Server_transactions_with_ADO_NET/0,339028784,339130225,00.htm.

hope this helps you out.

Thanks and Regards,
Manish Bafna.
MCP and MCTS.

[quoted text, click to view]

Martin Adhie
1/31/2007 10:12:01 AM

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

----------------------------------------

Even we can execute command from dataadapter as input.
Anyway... Thank you very much for Manish Bafna support.

[quoted text, click to view]

Manish Bafna
1/31/2007 8:13:00 PM

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.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet ado.net

dotnet ado.net : DataAdapter with Transaction (.net 1.1)