Arthur Liew Arthur Liew -4 years ago 131
Vb.net Question

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

I has 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


Can anyone give me some hints what I did wrong?

Thanks in advanced.

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download