我偶尔会遇到这个错误消息:
SqlTransaction已完成; 它不再可用。
我不确定是什么导致了这个错误弹出,我试图设置SQL超时无限。
是否因为我的代码结构?
当小组用户使用时不会发生这种情况,但当庞大的用户群发生时经常发生。
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任何人都可以给我一些提示,我做错了什么?
先谢谢了。
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 FunctionCan anyone give me some hints what I did wrong?
Thanks in advanced.
最满意答案
你正在设置 transaction = Nothing之后transaction = Nothing
transaction.Commit()这是造成这个问题的原因,你需要把Finally Method设置为Nothing。
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结束功能
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 = NothingEnd Function
更多推荐
发布评论