Now This is Something Worth Looking Into

When you see something like this in the call stack, you have to know more:

[InvalidOperationException: This SqlTransaction has completed; it is no longer usable.]
   System.Data.SqlClient.SqlTransaction.ZombieCheck() +1623536
   System.Data.SqlClient.SqlTransaction.Rollback() +172
   Framework.Common.Database.RollbackTransaction() in C:\Framework\Common\Database.vb:413 
   Test.uxCreate_Click(Object sender, EventArgs e) in C:\Test.aspx.vb:47
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +154
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3691

And the error message is interesting as well: “This SqlTransaction has completed; it is no longer usable”.

A little background on what is going on: this is a database class that is managing a SQL transaction to be used by multiple objects.  The database object instance gets passed along to the different objects and they use it, participating in the transaction.  Somewhere along the way, maybe a commit is occurring and then the transaction becomes invalid.  That’s how it seems.

Tossing in a bunch of debug.writelines to see what what happening inside the methods popped up a message about a SQLException being raised because of a non-existent column name.  Fixing the schema problem fixed the zombie problem.  But what was the reason for the original error?

Let’s say you bring a sandwich into work.  You give that sandwich to someone and tell them to put it in the refrigerator for you.  This person (the fridgemaster) puts it in the fridge and comes back to the refrigerator a little later to find your sandwich moldy and spoiled, so he throws the sandwich out.  Later on yet, you go to the fridgemaster and ask for your sandwich.  He says “this sandwich has spoiled; it is no longer usable.”  In response to your puzzled look, the fridgemaster says “I did a ZombieCheck and it was moldy.”

In coding terms, the order of events was: TX started, SQL error occurred, TX rolled back (by SQL Server), SQLException raised and caught, TX rolled back in catch block (by user code) and unable to because the TX already was rolled back by SQL Server.  This rollback behavior is dependent on the severity of the error. In the case of the schema error, which presumably was interpreted as “this will NEVER work”, this equated to being severe enough to roll back the transaction.