Re: Using a Transaction with a SqlDataSource

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


"Steven Blair" <steven.blair@xxxxxxxxxxxxxx> wrote in message news:eNBUKdImHHA.4768@xxxxxxxxxxxxxxxxxxxxxxx
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.


..

.

Relevant Pages

  • Re: Nested Transactions
    ... then it is possible that transaction was opened ... against one connection and rollback called against another one. ... Trying this with ADO and SQLOLEDB, ... > Rollback doesn't work, saying, there is no corresponding BEGIN statement. ...
    (microsoft.public.data.ado)
  • Re: Transaction when client shutdown
    ... >> killing a process or dropping a connection. ... >> there is indeed no automati commit or rollback of open transactions. ... >> The transaction remains open and any locks are held until the ... >> Best, Hugo ...
    (microsoft.public.sqlserver.programming)
  • Re: DB Wrapper, IDisposable richtig implementiert?
    ... In der Methode BeginTransaction() könnte ich ... so solltest Du auch Commit und Rollback implementieren. ... Und diese anstatt der Connection Instanz zu verwenden ... // die Connection und Transaction zusammenfasst ...
    (microsoft.public.de.german.entwickler.dotnet.csharp)
  • Re: Rollback timeout ???
    ... When you call the BeginTransaction API method we will mark this connection ... When you call the Transaction.Commit or Rollback we will unmark the ... If you close a connection that is marked as having a transaction active we ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Classic Nest SP with Transaction Question
    ... What I'd like to do is begin a transaction in an outer SP. ... I could hold off on performing the rollback until OuterSP examines the ... > immediately after returning from the CHILD call. ... >> receive the return code from the stored procedure call. ...
    (microsoft.public.sqlserver.programming)