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?
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