By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,510 Members | 1,810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,510 IT Pros & Developers. It's quick & easy.

How to use Transactions?

P: n/a
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.ValidateTransact ion(OleDbTransaction
transaction) at
System.Data.OleDb.OleDbCommand.ValidateConnectionA ndTransaction(String
method, Int32& localState) at
System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method) at
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at
Eventsdb._default.submitEventRequest_Clicked(Objec t sender, EventArgs e)
in D:\Documents and Settings\Bijoy
Naick\VSWebCache\mmsurveys.ca.nortel.com\eventsdb\ default.aspx.vb:line
385

------

Line 385 corresponds to a myCommand.ExecuteNonQuery() call

What am I doing wrong?

---------

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 18 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

"Bijoy Naick" <b_*****@donoteamil.yahoo.ca> wrote in message news:OB***************@tk2msftngp13.phx.gbl...
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.ValidateTransact ion(OleDbTransaction
transaction) at
System.Data.OleDb.OleDbCommand.ValidateConnectionA ndTransaction(String
method, Int32& localState) at
System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method) at
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at
Eventsdb._default.submitEventRequest_Clicked(Objec t sender, EventArgs e)
in D:\Documents and Settings\Bijoy
Naick\VSWebCache\mmsurveys.ca.nortel.com\eventsdb\ default.aspx.vb:line
385

------

Line 385 corresponds to a myCommand.ExecuteNonQuery() call

What am I doing wrong?

---------

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 18 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.