This SqlTransaction has completed; it is no longer usable

[Bill Humphrey]

I get this intermittent error when running a stored procedure sometimes:

This SqlTransaction has completed; it is no longer usable.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.

Source Error:

Line 840: lNUTHSQLResponse.FetchedCollection = lobjCatADailyResponsesCollection
Line 841: Else
Line 842: lSQLTransaction.Rollback()
Line 843: lSQLConn.Close()
Line 844: End If

Source File: C:\Inetpub\wwwroot\OperationsPerformance\OperationsPerformanceBusiness\clsCatADailyResponses.vb Line: 842

Stack Trace:

[InvalidOperationException: This SqlTransaction has completed; it is no longer usable.]
System.Data.SqlClient.SqlTransaction.Rollback()
OperationsPerformanceBusiness.clsCatADailyResponses.GetCatACallDetails() in C:\Inetpub\wwwroot\OperationsPerformance\OperationsPerformanceBusiness\clsCatADailyResponses.vb:842
OperationsPerformancePresentation.CatADailyResponsesDetailsCTL.MainFunction() in c:\inetpub\wwwroot\OperationsPerformancePresentation\OperationsPerformance\CatADailyResponsesDetailsCTL.ascx.vb:141
OperationsPerformancePresentation.CatADailyResponsesDetailsCTL.ParentControlEventHandler(Object sender, ParentControlEventArgs e) in c:\inetpub\wwwroot\OperationsPerformancePresentation\OperationsPerformance\CatADailyResponsesDetailsCTL.ascx.vb:128
OperationsPerformancePresentation.CatADailyResponsesDetails.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\OperationsPerformancePresentation\OperationsPerformance\CatADailyResponsesDetails.aspx.vb:131
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()

________________________________________
Version Information: Microsoft .NET Framework Version:1.1.4322.2407; ASP.NET Version:1.1.4322.2407

Here is my method

Public Function GetCatACallDetails() As NUTHSQLResponse

Dim lNUTHSQLResponse As New NUTHSQLResponse
Dim lSqlCmd As New SqlCommand
Dim lSQLConn As New SqlConnection
Dim lSQLTransaction As SqlTransaction
Dim lNUTHMSTSQLSlave As New NUTHMSTSQLSlave
Dim lResultDataSet As New DataSet
Dim lDataRow As DataRow
Dim lobjCatADailyResponsesCollection As New ClsCatADailyResponsesCollection
Dim lDataTable As New DataTable
Dim lrowcount As Integer
Dim loffset As Integer

'**************************************************************
'Configure the Connection string object for the MST transaction
'**************************************************************
lSQLConn = lNUTHMSTSQLSlave.GetNewConnection()
lSQLTransaction = lSQLConn.BeginTransaction()
lSqlCmd.Transaction = lSQLTransaction
lSqlCmd.Connection = lSQLConn

'**************************************
'Configure the Connection string object
'**************************************
lSqlCmd.CommandText = "GetCatACallDetails"

lSqlCmd.Parameters.Add(New SqlParameter("@CaseId", SqlDbType.Int))
lSqlCmd.Parameters("@CaseId").Value = m_CaseId

lSqlCmd.Parameters.Add(New SqlParameter("@ErrorDesc", SqlDbType.VarChar, 100))
lSqlCmd.Parameters("@ErrorDesc").Direction = ParameterDirection.Output

lSqlCmd.Parameters.Add(New SqlParameter("@ErrorNo", SqlDbType.Int))
lSqlCmd.Parameters("@ErrorNo").Direction = ParameterDirection.Output

lSqlCmd.Parameters.Add(New SqlParameter("@RowsAffected", SqlDbType.Int))
lSqlCmd.Parameters("@RowsAffected").Direction = ParameterDirection.Output

lNUTHSQLResponse = lNUTHMSTSQLSlave.MSTGetdata(lSqlCmd, lResultDataSet)

If lNUTHSQLResponse.TXNResult = NUTHConst.TXNResult.SUCCESS Then
lSQLTransaction.Commit()
lSQLConn.Close()

'***************************************
'Load the values into the datacollection
'***************************************
lDataTable = lNUTHSQLResponse.FetchedDataset.Tables(0)
lrowcount = lDataTable.Rows.Count

If lrowcount > 0 Then
For loffset = 0 To (lrowcount - 1)
lDataRow = lDataTable.Rows(loffset)

Dim lobjCatADailyResponses As New clsCatADailyResponses

With lobjCatADailyResponses
.CaseId = CInt(lDataRow.Item("CaseId"))
.Division = CStr(lDataRow.Item("Division"))
.CallTakerName = CStr(lDataRow.Item("CallTakerName"))
.Position = CInt(lDataRow.Item("Position"))
.SwitchT0 = CDate(lDataRow.Item("SwitchT0"))
.Answer = CDate(lDataRow.Item("Answer"))
.ReceivedT1 = CDate(lDataRow.Item("ReceivedT1"))
.AddressT2 = CDate(lDataRow.Item("AddressT2"))
.ProblemT3 = CDate(lDataRow.Item("ProblemT3"))
.CallCompleteT4 = CDate(lDataRow.Item("CallCompleteT4"))
.PrioritisedT5 = CDate(lDataRow.Item("PrioritisedT5"))
.TriageEndT6 = CDate(lDataRow.Item("TriageEndT6"))
.CallPriority = CStr(lDataRow.Item("CallPriority"))
.DispatchCode = CStr(lDataRow.Item("DispatchCode"))
.CallerTelephone = CStr(lDataRow.Item("CallerTelephone"))
.LocationAddress = CStr(lDataRow.Item("LocationAddress"))
.Hospital = CStr(lDataRow.Item("Hospital"))
.Department = CStr(lDataRow.Item("Department"))
.Postcode = CStr(lDataRow.Item("Postcode"))
.Location = CStr(lDataRow.Item("Location"))
.Road = CStr(lDataRow.Item("Road"))
.District = CStr(lDataRow.Item("District"))
.Town = CStr(lDataRow.Item("Town"))
.Zone = CStr(lDataRow.Item("Zone"))
.Desk = CStr(lDataRow.Item("Desk"))
.OSE = CInt(lDataRow.Item("OSE"))
.OSN = CInt(lDataRow.Item("OSN"))
.Instructions = CStr(lDataRow.Item("Instructions"))
.Problem = CStr(lDataRow.Item("Problem"))
.Gender = CStr(lDataRow.Item("Gender"))
.AgeRange = CStr(lDataRow.Item("AgeRange"))
.PatientAge = CStr(lDataRow.Item("PatientAge"))
.PatientForename = CStr(lDataRow.Item("PatientForename"))
.PatientSurname = CStr(lDataRow.Item("PatientSurname"))
.CallerForename = CStr(lDataRow.Item("CallerForename"))
.CallerSurname = CStr(lDataRow.Item("CallerSurname"))
.CallerRelationship = CStr(lDataRow.Item("CallerRelationship"))
.GPName = CStr(lDataRow.Item("GPName"))
.GPPractice = CStr(lDataRow.Item("GPPractice"))
.GPTelephone = CStr(lDataRow.Item("GPTelephone"))
.SiteSafety = CStr(lDataRow.Item("SiteSafety"))
.Casualties = CStr(lDataRow.Item("Casualties"))
.CrewFeedback = CStr(lDataRow.Item("CrewFeedback"))

End With

lobjCatADailyResponsesCollection.Add(lobjCatADailyResponses)
Next
End If

lNUTHSQLResponse.FetchedCollection = lobjCatADailyResponsesCollection
Else
lSQLTransaction.Rollback()
lSQLConn.Close()
End If

Return lNUTHSQLResponse

End Function


Here is my stored procedure

USE [OperationsPerformance]
GO
/****** Object: StoredProcedure [dbo].[GetCatACallDetails] Script Date: 09/14/2009 13:43:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Bill Humphrey
-- Create date: 03/09/2009
-- Description: Call Details
-- =============================================
ALTER PROCEDURE [dbo].[GetCatACallDetails]

@CaseID INTEGER,
@ErrorDesc VARCHAR(100) OUTPUT,
@ErrorNo INT OUTPUT,
@RowsAffected INT OUTPUT

AS
BEGIN

DECLARE @lErrorNo INTEGER
DECLARE @lRowsAffected INTEGER

SELECT TOP 1 id AS CaseId,
CASE WHEN report_desk_text = 'northb' OR report_desk_text = 'northumberland' THEN 'North' ELSE ISNULL(report_desk_text, 'Unknown') END AS Division,
-- Call Details
user_name AS CallTakerName,
operator_pos_id AS Position,
dt_switch AS SwitchT0,
dt_answered AS Answer,
dt_rcvd AS ReceivedT1,
dt_address_confirmed AS AddressT2,
dt_what_problem AS ProblemT3,
dt_call_reason AS CallCompleteT4,
dt_prioritised AS PrioritisedT5,
dt_triage_end AS TriageEndT6,
priority_text AS CallPriority,
pathway_triage_dispocode AS DispatchCode,
-- Person Making Call
isnull(caller_telephone, '') as CallerTelephone,
isnull(ltrim(rtrim(ad1_location)) + ' ' + ltrim(rtrim(ad1_road)) + ', ' + ltrim(rtrim(ad1_district)) + ', ' + ltrim(rtrim(ad2_district)), caller_telephone) as LocationAddress,
-- Incident Location
isnull(ad2_hosp_name, '') as Hospital,
isnull(ad2_dept_name, '') as Department,
isnull(ad2_post_code, '') as Postcode,
isnull(ad2_location, '') as Location,
isnull(ad2_Road, '') as Road,
isnull(ad2_district, '') as District,
isnull(ad2_town, '') as Town,
isnull(ad2_zone_name, '') as Zone,
isnull(ad2_desk_text, '') as Desk,
isnull(ad2_os_easting, '') as OSE,
isnull(ad2_os_northing, '') as OSN,
-- Patient Details
isnull(dispdetail_text, '') as Instructions,
isnull(diagnosis_text, '') as Problem,
isnull(patient_sex_text, '') as Gender,
isnull(patient_age_range_text, '') as AgeRange,
isnull(patient_age, '') as PatientAge,
isnull(patient_forename, '') as PatientForename,
isnull(patient_surname, '') as PatientSurname,
-- Caller Details
isnull(caller_forename, '') as CallerForename,
isnull(caller_surname, '') as CallerSurname,
isnull(caller_relationship_text, '') as CallerRelationship,
-- GP Details
isnull(doctors_name + ' ' + doctor_surname, '') as GPName,
isnull(practice_name, '') as GPPractice,
isnull(doctor_telephone, '') as GPTelephone,
-- Other Details
isnull(site_safety_text, '') as SiteSafety,
isnull(casualty_count, 0) as Casualties,
isnull(crew_feedback_text, '') as CrewFeedback

FROM dbo.vw_cadcasedetails

GROUP BY id, report_desk_text, user_name, operator_pos_id, dt_switch, dt_answered, dt_rcvd, dt_address_confirmed, dt_what_problem,
dt_call_reason, dt_prioritised, dt_triage_end, priority_text, pathway_triage_dispocode, caller_telephone,
ad1_location, ad1_road, ad1_district, ad2_district, ad2_hosp_name, ad2_dept_name, ad2_post_code, ad2_location,
ad2_Road, ad2_district, ad2_town, ad2_zone_name, ad2_desk_text, ad2_os_easting, ad2_os_northing, caller_relationship_text,
caller_forename, caller_surname, doctors_name, doctor_surname, practice_name, doctor_telephone, site_safety_text, casualty_count, crew_feedback_text,
dispdetail_text, diagnosis_text, patient_sex_text, patient_age_range_text, patient_age, patient_forename, patient_surname

HAVING (id = @CaseID)

SELECT @lRowsAffected = @@ROWCOUNT, @lErrorNo = @@ERROR
IF @lErrorNo > 0
BEGIN
SET @ErrorDesc = 'Error with List of GetJobDetails'
SET @ErrorNo = @lErrorNo
SET @RowsAffected = 0
RETURN
END
ELSE
BEGIN
SET @ErrorDesc = 'Get was Successful for GetJobDetails'
SET @ErrorNo = 0
SET @RowsAffected = @lRowsAffected
RETURN
END

END