How to use Transactions?

[Bijoy Naick]

I am having trouble implementing transactions.. Here's my code..

-----

Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim myReader As OleDbDataReader
Dim transaction As OleDbTransaction

myConnection = New OleDbConnection(connStr)
myConnection.Open()

transaction = myConnection.BeginTransaction()

Try

myCommand.Transaction = transaction

sql = "insert into table values (1,2)"
myCommand = New OleDbCommand(sql, myConnection)
myCommand.ExecuteNonQuery()

sql = "insert into table2 values (3,4)"
myCommand = New OleDbCommand(sql, myConnection)
myCommand.ExecuteNonQuery()

transaction.commit()

Catch ex As Exception
display ex.Message and ex.ToString
transaction.rollback()
End Try

myConnection.Close()

----------

The error I get is

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.

System.InvalidOperationException: 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. at
System.Data.OleDb.OleDbConnection.ValidateTransaction(OleDbTransaction
transaction) at
System.Data.OleDb.OleDbCommand.ValidateConnectionAndTransaction(String
method, Int32& localState) at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) at
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at
Eventsdb._default.submitEventRequest_Clicked(Object sender, EventArgs e)
in D:\Documents and Settings\Bijoy
Naick\VSWebCache\mmsurveys.ca.nortel.com\eventsdb\default.aspx.vb:line
385

 

[Raterus]

Just having the transaction "up and running" isn't enough, you must specifically set it to the command object, if oledb transactions are like sqltransactions then you need to do this

myCommand = New OleDbCommand(sql, myConnection, transaction)

--Michael