Connection Goes Away

[Shaggy Hiker]

Here's the whole code, but then I'll focus on just a part:

 If mCurrentEventID = e.EventInfo.EventID Then
     Using cmd As SqlClient.SqlCommand = mERaiser.TransactionConnection.CreateCommand
         cmd.Transaction = mERaiser.Transaction
         Try
             If mIsNewRecord Then
                 'This is a new mortality record, so INSERT.
                 cmd.CommandText = "INSERT INTO Core.FISH_Mortality (EventID, AUID,Comments,TestMode,Uploaded) VALUES (@EID,@AUID,@Comments,@Test,@Up)"
                 cmd.Parameters.AddWithValue("@EID", e.EventInfo.EventID)
                 cmd.Parameters.AddWithValue("@AUID", e.SourceAssemblage)
                 If mSenderComments IsNot Nothing Then
                     cmd.Parameters.AddWithValue("@Comments", mSenderComments)
                 Else
                     cmd.Parameters.AddWithValue("@Comments", DBNull.Value)
                 End If
                 cmd.Parameters.AddWithValue("@Test", mERaiser.TestMode)
                 cmd.Parameters.AddWithValue("@Up", False)
                 cmd.ExecuteNonQuery()

                 'For a new record, need to update the event with the module version information.
                 cmd.Parameters.Clear()
                 cmd.CommandText = "UPDATE Core.FISH_Events SET SecondaryVersion = @p1 WHERE EventID = '" & e.EventInfo.EventID.ToString & "'"
                 cmd.Parameters.AddWithValue("@p1", mSenderModuleVersion)
                 cmd.ExecuteNonQuery()

             Else
                 If mSenderComments IsNot Nothing AndAlso mSenderComments <> "NOCHANGE" Then
                     'There is only one field that can be updated in mortality, which is the comment field.
                     'Update the comment
                     cmd.CommandText = "UPDATE Core.FISH_Mortality SET Comments = @p1,UploadDate = @p2 WHERE EventID = '" & mCurrentEventID.ToString & "'"
                     cmd.Parameters.AddWithValue("@p1", mSenderComments)
                     cmd.Parameters.AddWithValue("@p2", DBNull.Value)
                     cmd.ExecuteNonQuery()
                 End If
             End If
         Catch ex As SqlClient.SqlException
             mERaiser.CancelTransaction(mSenderModuleName, "ERROR writing mortality: " & ex.Message)
         Catch ex As Exception
             mERaiser.CancelTransaction(mSenderModuleName, "ERROR writing mortality: " & ex.Message)
         End Try
     End Using
 End If

The key piece appears to be this:

                If mSenderComments IsNot Nothing AndAlso mSenderComments <> "NOCHANGE" Then
                     'There is only one field that can be updated in mortality, which is the comment field.
                     'Update the comment
                     cmd.CommandText = "UPDATE Core.FISH_Mortality SET Comments = @p1,UploadDate = @p2 WHERE EventID = '" & mCurrentEventID.ToString & "'"
                     cmd.Parameters.AddWithValue("@p1", mSenderComments)
                     cmd.Parameters.AddWithValue("@p2", DBNull.Value)
                     cmd.ExecuteNonQuery()
                 End If

If I look at the transaction for the cmd at the start of this, it has a connection and all appears well. However, ExecuteNonQuery throws an exception because the column MortalityID doesn't exist in the database table. Of course, one might note that MortalityID doesn't exist in the query, either. However, that's not chosen at random. MortalityID is the old name for the EventID column. I changed it many moons ago to be consistent. So, it's true that it doesn't exist in the table, but it no longer exists in the query, either. In fact, I can't find it in the project at all (or at least VS isn't finding it).

That seems like it's just a glitch. It isn't even my main question. If anybody can suggest a means by which the old name is still hanging around, despite being in neither the query nor the DB table, that would be nice.

What is really intriguing me is that once the exception happens, the Connection property of the transaction becomes Nothing. There is a valid connection right up until the time the exception gets thrown, but on the first line of the exception handler, the connection is Nothing. That causes further problems. Interestingly, the transaction can still be rolled back, which it seems like it shouldn't if it no longer has a connection, but I kind of need that connection. I was getting it from the transaction, but now I can't.

Is that normal behavior?

[Shaggy Hiker]

The underlying problem has been solved. I had forgotten about a trigger on that table. The trigger still had the old MortalityID, which caused the exception.

However, that still leaves the larger question: Is it normal for the transaction to lose the connection if an exception occurs? Perhaps this was just a result of the exception arising in the trigger. Interestingly, it appeared that not just did the transaction lose the connection, but the command object appears to have lost the transaction. It's like everything just fell apart.