Transaction error

[Chris Zopers]

Hello,

I've developed a web application. The application uses one static
connection object. Some methods use Transactions. Everything works fine,
except when several users start using the webapplication at the same
time. I often get the following message:

"Execute requires the Command object to have a Transaction object when
the Connection object assigned to the Command is in a pending local
transaction. The Transaction property of the Command has not been
initialized."

I think it has something to do with using one static connection for all
users, but do not know this for sure. I tried locking the
connection-code with the Lock statement, but the error still occurs.

The webserver has multiple processors, my local machine has only one
processor. Can this have something to do with it? I do not know what can
cause this, the code seems to be correct, no errors occur when the code
is run by one user. We use Oracle 9i as the database.

Greetings,
Chris

 

[Eliyahu Goldin]

When you execute a query on a Command object within a transaction, you need
to get the Transaction object for your connection and assign it the
Transaction property of your Command. Something like this:

OracleTransaction myTransaction = myConnection.BeginTransaction();
myCommand.Transaction = myTransaction;
myCommand.ExecuteNonQuery();


--
Eliyahu Goldin,
Software Developer
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin

Hello,

I've developed a web application. The application uses one static
connection object. Some methods use Transactions. Everything works fine,
except when several users start using the webapplication at the same
time. I often get the following message:

"Execute requires the Command object to have a Transaction object when
the Connection object assigned to the Command is in a pending local
transaction. The Transaction property of the Command has not been
initialized."

I think it has something to do with using one static connection for all
users, but do not know this for sure. I tried locking the
connection-code with the Lock statement, but the error still occurs.

The webserver has multiple processors, my local machine has only one
processor. Can this have something to do with it? I do not know what can
cause this, the code seems to be correct, no errors occur when the code
is run by one user. We use Oracle 9i as the database.

Greetings,
Chris



__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4068 (20090512) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4068 (20090512) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Chris Zopers]

This is the situation at the moment:

1) There is a webapplication with a lot of different methods that access
an Oracle database. These methods do not interact with each other, each
method performs its own tasks.
2) Some methods use a transaction, some execute only a single SQL
statement and therefore do not use a transaction.
3) All make use of one static connection object.

When one user uses the application, everything works fine. There is no
error in code or transactions that do not work.

When other users start using the application at the same time, sometimes
the error mentioned before occurs. This error can occur in methods that
do not even have anything to do with methods that use transactions.

Hope someone recognizes this problem....

Greetings,
Christian

 

[Eliyahu Goldin]

I suggest you to re-think the idea of using a static connection object. You
can safely rely on connection pooling that you get out of box with ASP.NET.
A good practice is to close the connection at the end of every data access.
The connection pooling will take care of optimizing it for you.


--
Eliyahu Goldin,
Software Developer
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin

This is the situation at the moment:

1) There is a webapplication with a lot of different methods that access
an Oracle database. These methods do not interact with each other, each
method performs its own tasks.
2) Some methods use a transaction, some execute only a single SQL
statement and therefore do not use a transaction.
3) All make use of one static connection object.

When one user uses the application, everything works fine. There is no
error in code or transactions that do not work.

When other users start using the application at the same time, sometimes
the error mentioned before occurs. This error can occur in methods that
do not even have anything to do with methods that use transactions.

Hope someone recognizes this problem....

Greetings,
Christian



__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4069 (20090512) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4069 (20090512) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Chris Zopers]

Thanks for your answer.

I noticed a weird thing. Each error occurs in methods that use the
ExecuteScalar method. I changed every method that uses ExecuteScalar
into ExecuteReader and use the first value of the first row from the
Reader. Now the error has disappeared! Strange huh?

Greetings,
Chris