SqlTransaction has completed, it is no longer usable, rollback issue

Question

I have been facing this error message once in a while:

The SqlTransaction has completed; it is no longer usable.

I am not sure what caused this error to pop up, I have tried setting SQL Timeout to infinity.

Is it because of my code structure?

This doesn't happen when small group of user, but happens frequently when huge group of user.

Public Function PostCustomerSet(ByVal ds As CustomerHeaderDetailDataSet, ByVal Table As String, ByRef SessionKeys As String, ByRef errMsg As String, ByVal SubmitType As Integer, ByVal callWrapper As Boolean) As String
    errMsg = Nothing
    Dim newkey As PrimaryKey
    Dim oldkey As PrimaryKey
    Try
        transaction = EnterpriseUtils.StartTransaction(Connection1, m_Dict)
        If ds.CustomerHeader.Rows.Count > 0 Then
            Dim SessionKey As PrimaryKey = PrimaryKey.FromString(SessionKeys)
            Dim CustomerHeaderKey As PrimaryKey = EnterpriseUtils.VerifyDataRowKeys(ds.CustomerHeader.Rows(0), SessionKey, SubmitType)
            Dim OldCustomerHeaderKey As PrimaryKey = New PrimaryKey(CustomerHeaderKey)
            newkey = CustomerHeaderKey
            oldkey = OldCustomerHeaderKey
            
            If CustomerHeaderKey Is Nothing Then
                Return Nothing
            Else
                If ds.CustomerHeader.Rows.Count > 0 Then 'check if record was not deleted during update
                    CustomerHeaderKey.Update(ds.CustomerHeader(0))
                    If SubmitType = 1 Then
                        EnterpriseUtils.UpdateTemporaryLinks("CustomerNumber", CustomerHeaderKey, OldCustomerHeaderKey, "CustomerDetail", transaction)
                        EnterpriseUtils.UpdateTemporaryLinks("CustomerNumber", CustomerHeaderKey, OldCustomerHeaderKey, "CustomerMultiPayments", transaction)
                    End If

                    
                    If callWrapper AndAlso ds.CustomerHeader(0).CustomerNumber.ToUpper <> "DEFAULT" Then
                        Dim Command As SqlCommand = New SqlCommand("enterprise.Customer_Control", Connection1)
                        Command.CommandType = CommandType.StoredProcedure
                        Command.Transaction = transaction
                        Dim parameter As SqlParameter
                        '@CompanyID
                        parameter = New SqlParameter("@CompanyID", SqlDbType.NVarChar, 36)
                        parameter.Value = ds.CustomerHeader(0).CompanyID
                        Command.Parameters.Add(parameter)
                        'DivisionID
                        parameter = New SqlParameter("@DivisionID", SqlDbType.NVarChar, 36)
                        parameter.Value = ds.CustomerHeader(0).DivisionID
                        Command.Parameters.Add(parameter)
                        '@DepartmentID
                        parameter = New SqlParameter("@DepartmentID", SqlDbType.NVarChar, 36)
                        parameter.Value = ds.CustomerHeader(0).DepartmentID
                        Command.Parameters.Add(parameter)
                        '@DocumentNumber
                        parameter = New SqlParameter("@DocumentNumber", SqlDbType.NVarChar, 36)
                        parameter.Value = ds.CustomerHeader(0).CustomerNumber
                        Command.Parameters.Add(parameter)
                        Command.ExecuteNonQuery()
                    End If
                End If
                transaction.Commit()
                transaction = Nothing
                Return CustomerHeaderKey.ToString()
            End If
        ElseIf ds.CustomerDetail.Count > 0 Then
            CustomerDetail_Adapter.Update(ds.CustomerDetail)
            transaction.Commit()
            transaction = Nothing
        ElseIf ds.CustomerMiscCharges.Count > 0 Then
            CustomerMiscCharges_Adapter.Update(ds.CustomerMiscCharges)
            transaction.Commit()
            transaction = Nothing
        ElseIf ds.CustomerMultiPayments.Count > 0 Then
            CustomerMultiPayment_Adapter.Update(ds.CustomerMultiPayments)
            transaction.Commit()
            transaction = Nothing
        End If
        Return Nothing       
    Catch e As SqlException
        errMsg = "Please contact your system administrator. (code number: " + e.Number + ")" 'Message + ")"
        WebUtils.ProcessError(e, errMsg, EDIErrorType.SendEmailNotification Or EDIErrorType.WriteLog)
        Return Nothing
    Catch e As Exception
        errMsg = e.Message
        WebUtils.ProcessError(e, errMsg, EDIErrorType.SendEmailNotification Or EDIErrorType.WriteLog)
        Return Nothing
    Finally
        If Not transaction Is Nothing Then
            transaction.Rollback()
        End If
        If Connection1.State = ConnectionState.Open Then
            Connection1.Close()
        End If
    End Try
End Function

What did I do wrong?

Answer 1

You are setting
transaction = Nothing after

transaction.Commit()

Which is causing this issue, you need to set this as nothing after Finally Method.

Public Function PostCustomerSet(ByVal ds As CustomerHeaderDetailDataSet, ByVal Table As String, ByRef SessionKeys As String, ByRef errMsg As String, ByVal SubmitType As Integer, ByVal callWrapper As Boolean) As String
errMsg = Nothing
Dim newkey As PrimaryKey
Dim oldkey As PrimaryKey
Try
    transaction = EnterpriseUtils.StartTransaction(Connection1, m_Dict)
    If ds.CustomerHeader.Rows.Count > 0 Then
        Dim SessionKey As PrimaryKey = PrimaryKey.FromString(SessionKeys)
        Dim CustomerHeaderKey As PrimaryKey = EnterpriseUtils.VerifyDataRowKeys(ds.CustomerHeader.Rows(0), SessionKey, SubmitType)
        Dim OldCustomerHeaderKey As PrimaryKey = New PrimaryKey(CustomerHeaderKey)
        newkey = CustomerHeaderKey
        oldkey = OldCustomerHeaderKey

        If CustomerHeaderKey Is Nothing Then
            Return Nothing
        Else
            If ds.CustomerHeader.Rows.Count > 0 Then 'check if record was not deleted during update
                CustomerHeaderKey.Update(ds.CustomerHeader(0))
                If SubmitType = 1 Then
                    EnterpriseUtils.UpdateTemporaryLinks("CustomerNumber", CustomerHeaderKey, OldCustomerHeaderKey, "CustomerDetail", transaction)
                    EnterpriseUtils.UpdateTemporaryLinks("CustomerNumber", CustomerHeaderKey, OldCustomerHeaderKey, "CustomerMultiPayments", transaction)
                End If


                If callWrapper AndAlso ds.CustomerHeader(0).CustomerNumber.ToUpper <> "DEFAULT" Then
                    Dim Command As SqlCommand = New SqlCommand("enterprise.Customer_Control", Connection1)
                    Command.CommandType = CommandType.StoredProcedure
                    Command.Transaction = transaction
                    Dim parameter As SqlParameter
                    '@CompanyID
                    parameter = New SqlParameter("@CompanyID", SqlDbType.NVarChar, 36)
                    parameter.Value = ds.CustomerHeader(0).CompanyID
                    Command.Parameters.Add(parameter)
                    'DivisionID
                    parameter = New SqlParameter("@DivisionID", SqlDbType.NVarChar, 36)
                    parameter.Value = ds.CustomerHeader(0).DivisionID
                    Command.Parameters.Add(parameter)
                    '@DepartmentID
                    parameter = New SqlParameter("@DepartmentID", SqlDbType.NVarChar, 36)
                    parameter.Value = ds.CustomerHeader(0).DepartmentID
                    Command.Parameters.Add(parameter)
                    '@DocumentNumber
                    parameter = New SqlParameter("@DocumentNumber", SqlDbType.NVarChar, 36)
                    parameter.Value = ds.CustomerHeader(0).CustomerNumber
                    Command.Parameters.Add(parameter)
                    Command.ExecuteNonQuery()
                End If
            End If
            transaction.Commit()
            transaction = Nothing
            Return CustomerHeaderKey.ToString()
        End If
    ElseIf ds.CustomerDetail.Count > 0 Then
        CustomerDetail_Adapter.Update(ds.CustomerDetail)
        transaction.Commit()

    ElseIf ds.CustomerMiscCharges.Count > 0 Then
        CustomerMiscCharges_Adapter.Update(ds.CustomerMiscCharges)
        transaction.Commit()

    ElseIf ds.CustomerMultiPayments.Count > 0 Then
        CustomerMultiPayment_Adapter.Update(ds.CustomerMultiPayments)
        transaction.Commit()

    End If
    Return Nothing       
Catch e As SqlException
    errMsg = "Please contact your system administrator. (code number: " + e.Number + ")" 'Message + ")"
    WebUtils.ProcessError(e, errMsg, EDIErrorType.SendEmailNotification Or EDIErrorType.WriteLog)
    Return Nothing
Catch e As Exception
    errMsg = e.Message
    WebUtils.ProcessError(e, errMsg, EDIErrorType.SendEmailNotification Or EDIErrorType.WriteLog)
    Return Nothing
Finally
    If Not transaction Is Nothing Then
        transaction.Rollback()
    End If
    If Connection1.State = ConnectionState.Open Then
        Connection1.Close()
    End If
End Try
transaction = Nothing

End Function