Using a Transaction with a SqlDataSource

[Steven Blair]

I have a problem on my SqlDataSource.
Once my SqlDataSource has Inserted, I need to run a stored procedure.
The return from this stored procedure determines if Icommit or rollback.

Here is my approach:

http://rafb.net/p/b53pmq89.html

When the ExecuteNonQuery is fired the following exception is thrown:

"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count =
0."

Anyone know what the problem is, or is my approach wrong?

Steven

 

[Mr. Arnold]

I have a problem on my SqlDataSource.
Once my SqlDataSource has Inserted, I need to run a stored procedure.
The return from this stored procedure determines if Icommit or rollback.

Here is my approach:

http://rafb.net/p/b53pmq89.html

When the ExecuteNonQuery is fired the following exception is thrown:

"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count =
0."

Anyone know what the problem is, or is my approach wrong?

e.Command.Transaction.Commit() should be placed right after
e.Command.ExecuteNonQuery(), because the execution of the StoreProcedure
returned good. It took the non-exception path, the transactions should be
committed. What are you wafting for?

The e.Command.Transaction.Rollback() should be placed in the scope of the
Catch, with a possible check to see if the connection is Open and close the
connection before you Throw (ex) to leave the routine.

The Finally should be checking for connection Open and Close the connection
if open.

Have you heard of the ADO.Net Using statement, which should not to be
confused with the Namespace Using statement, use Google and look it up.


..

 

[Steven Blair]

Would the Using correct the problem (the exception I have)?

As for the structure of the code, in your example, how would you cater
for the stored procedure returning an invalid response (no exception
thrown but a report of failure?)
My appraoch caters for both exceptions and the stored proecedure
failing.

 

[Mr. Arnold]

Would the Using correct the problem (the exception I have)?

No, it's just a another way of opening a connection, which can be applied
down to the ADO.Reader level. The ADO.Net.Using also closes the things was
well. You should look up some examples using Google.

As for the structure of the code, in your example, how would you cater
for the stored procedure returning an invalid response (no exception
thrown but a report of failure?)
My appraoch caters for both exceptions and the stored proecedure
failing.

Well, I would set a Return Code on the Return statement of the Stored
Procedure.

If it returns zero, then do the commit, else Throw a New Exception, which
will make the logic go to the Catch.

That return of failure I would assume is something you're going to return
using the Return code yourself, because if there is a problem in the Stored
Procedure in executing, then an Exception is going to be thrown by SQL
Server.

I think your other problem the way you have it coded is the Finally is out
of the scope of the transaction, with the commit or rollback.