I found following error when try to get data from sql server :-
Thread was being aborted.
Full Error :- at Sharenote.UI.DAL.GetBIRPNotesAndActivity(Int32 staffID, String staffIDs, Int32 clientID, String clientIDs, DateTime startDate, DateTime endDate, Int32 locID, Int32 estaffID, Boolean trashed, Int32 serviceID, String serviceIDs, Int32 noteStatusID, String noteStatusIDs, Int32 billingStatusID, String billingStatusIDs, Int32 insuranceTypeID, String fundingSourceIDs, Int32 locked, Int32 printed, Boolean isGlobal, String caseLoadIDs, String locIDs, Int32 providerID, Boolean qaqi) at Sharenote.UI.birpNoteCenter.GetNotes()
-----------used method GetNotes()--------------------------------
Private Sub GetNotes()
Dim staffID As Integer = CInt(ViewState("staffID"))
Dim locID As Integer = CInt(ViewState("locID"))
Dim startDate As Date = CDate(pickerStartDate.SelectedDate).ToShortDateString
Dim endDate As Date = CDate(pickerEndDate.SelectedDate).ToShortDateString & " 22:59:59"
Dim clientIDs As String = "0"
Dim staffIDs As String = "0"
Dim serviceIDs As String = "0"
Dim billingStatusIDs As String = "0"
Dim noteStatusIDs As String = "0"
Dim fundingSourceIDs As String = "0"
Dim caseLoadIDs As String = "0"
Dim locIDs As String = "0"
Dim snUser As ShareNoteUser = CType(ViewState("ShareNoteUser"), ShareNoteUser)
Try
'-- set parameter for users caseload
If Not snUser.Clients = "0" Then
caseLoadIDs = snUser.Clients.Replace(",", " ")
End If
'-- set parameter for users assigned location(s)
If Not snUser.Locations = "0" Then
locIDs = snUser.Locations.Replace(",", " ")
End If
Dim ss As New NoteSearchSettings
If cbShowMulti.Checked = True Then '-- they want search based off of multiple selections
ss.SelectMulti = True
For Each li As ListItem In lbxClients.Items
If li.Selected = True Then
clientIDs = clientIDs & " " & li.Value
End If
Next
ss.ClientID = 0
For Each li As ListItem In lbxStaff.Items
If li.Selected = True Then
staffIDs = staffIDs & " " & li.Value
End If
Next
ss.StaffID = 0
For Each li As ListItem In lbxServices.Items
If li.Selected = True Then
serviceIDs = serviceIDs & " " & li.Value
End If
Next
ss.ServiceID = 0
For Each li As ListItem In lbxBillingStatuses.Items
If li.Selected = True Then
billingStatusIDs = billingStatusIDs & " " & li.Value
End If
Next
ss.BillingStatusID = 0
For Each li As ListItem In lbxNoteStatuses.Items
If li.Selected = True Then
noteStatusIDs = noteStatusIDs & " " & li.Value
End If
Next
ss.NoteStatusID = 0
For Each li As ListItem In lbxFundingSources.Items
If li.Selected = True Then
fundingSourceIDs = fundingSourceIDs & " " & li.Value
End If
Next
ss.FundSourceID = 0
Else '-- normal selection method
ss.SelectMulti = False
ss.BillingStatusID = ddlBillingStatuses.SelectedValue
ss.NoteStatusID = ddlNoteStatuses.SelectedValue
ss.FundSourceID = ddlFundSources.SelectedValue
If Not ddlServices.SelectedIndex = 0 And Not ddlServices.SelectedIndex = -1 Then
'ss.ServiceID = ddlServices.SelectedValue
ss.ServiceID = ""
Else
ss.ServiceID = 0
ddlServices.SelectedIndex = 0
End If
' ss.ServiceID = ddlServices.SelectedValue
If Not ddlStaffFilter.SelectedIndex = 0 And Not ddlStaffFilter.SelectedIndex = 1 And Not ddlStaffFilter.SelectedItem Is Nothing Then
ss.StaffID = ddlStaffFilter.SelectedValue
Else
ss.StaffID = 0
End If
If Not ddlFilterClients.SelectedIndex = 0 And Not ddlFilterClients.SelectedIndex = 1 Then
ss.ClientID = ddlFilterClients.SelectedValue
Else
ss.ClientID = 0
End If
End If
'-- declare new search settings to save
ss.LocID = locID
ss.StartDate = startDate
ss.EndDate = endDate
ss.ItemsPerPage = ddlItemsPerPage.SelectedValue
ss.Locked = ddlFilterLocked.SelectedValue
ss.isGlobal = cbGlobal.Checked
ss.Trashed = cbTrashed.Checked
ss.Printed = ddlFilterPrinted.SelectedValue
ss.CaseLoadIDs = caseLoadIDs
ss.LocationIDs = locIDs
ss.isQAQI = snUser.isQAQI
'-- multi item search selections --
'-- these will be "0" unless something was chosen and the select multi box was checked
ss.ClientIDs = clientIDs
ss.StaffIDs = staffIDs
ss.ServiceIDs = serviceIDs
ss.FundingSourceIDs = fundingSourceIDs
ss.BillingStatusIDs = billingStatusIDs
ss.NoteStatusIDs = noteStatusIDs
'ss.SortExpressions = dgNoteList.MasterTableView.SortExpressions
'-- save to session
Session("bNoteSearchSettings") = ss
'-- Get notes only once
Try
Dim teamStaff As Generic.List(Of Integer) = CType(ViewState("staffList"), Generic.List(Of Integer))
'Dim teamClients As Generic.List(Of Integer) = CType(ViewState("teamClients"), Generic.List(Of Integer))
birpNotes = DAL.GetBIRPNotesAndActivity(ss.StaffID, _
ss.StaffIDs,
ss.ClientID, _
ss.ClientIDs, _
ss.StartDate, _
ss.EndDate, _
CInt(ViewState("locID")), _
staffID, _
ss.Trashed, _
ss.ServiceID, _
ss.ServiceIDs, _
ss.NoteStatusID, _
ss.NoteStatusIDs, _
ss.BillingStatusID, _
ss.BillingStatusIDs, _
ss.FundSourceID, _
ss.FundingSourceIDs, _
ss.Locked, _
ss.Printed, _
ss.isGlobal, _
ss.CaseLoadIDs, _
ss.LocationIDs, _
CInt(ViewState("providerID")), _
ss.isQAQI)
If teamStaff.Count > 0 Then
Dim dv As New DataView(birpNotes)
Dim s As New System.Text.StringBuilder
For Each i As Integer In teamStaff
s.Append(i & ", ")
Next
dv.RowFilter = String.Format("staffID IN ({0})", s.ToString.TrimEnd(","))
birpNotes = dv.ToTable
End If
'-- add to the user cache
If birpNotes.Rows.Count > 250 Then '-- cache the data
Cache.Insert(staffID.ToString, birpNotes, Nothing, Now.AddMinutes(5), TimeSpan.Zero)
Else '-- remove it from the cash if there is something there, dont need to keep it in that memory
If Not Cache.Get(ViewState("staffID")) Is Nothing Then
Cache.Remove(ViewState("staffID"))
End If
End If
Catch ex As SqlException
'lblMarquee.Text = ex.ToString
Response.Redirect(DAL.ErrorReport(Request.Url.AbsoluteUri.ToString, ex.Message.ToString(), DateTime.Now().ToString, ViewState("staffID"), ex.StackTrace.ToString(),
Request.Browser.Browser & " " & Request.Browser.Version, Request.Browser.Platform, Request.ServerVariables("REMOTE_ADDR")), False)
End Try
Catch ex As System.Exception
Response.Redirect(DAL.ErrorReport(Request.Url.AbsoluteUri.ToString, ex.Message.ToString(), DateTime.Now().ToString, ViewState("staffID"), ex.StackTrace.ToString(),
Request.Browser.Browser & " " & Request.Browser.Version, Request.Browser.Platform, Request.ServerVariables("REMOTE_ADDR")), False)
End Try
End Sub
Private Sub btnRefreshAuths_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRefreshAuths.Click
btnRefreshAuths.CommandArgument = "refresh"
dgClientAuths.Rebind()
btnRefreshAuths.CommandArgument = "none"
End Sub
End Class
--------------Used Stored Procedure in DAL Method is----------------------
CREATE PROCEDURE [dbo].[usp_GetBIRPNotesAndActivity]
-- Add the parameters for the stored procedure here
@providerID INT,
@staffID INT = 0,
@clientID INT = 0,
@billingStatusID INT = 0,
@serviceID INT = 0,
@locked INT = -1,
@printed INT = -1,
@noteStatusID INT = 0,
@trashed BIT = 0,
@insuranceTypeID INT = 0,
@locID INT = 0,
@estaffID INT = 0,
@startDate SMALLDATETIME,
@endDate SMALLDATETIME,
@clientIDs VARCHAR(8000) = '0',
@staffIDs VARCHAR(8000) = '0',
@fundingSourceIDs VARCHAR(200) = '0',
@noteStatusIDs VARCHAR(100) = '0',
@billingStatusIDs VARCHAR(100) = '0',
@serviceIDs VARCHAR(8000) = '0',
@caseloadIDs VARCHAR(8000) = '0',
@locIDs VARCHAR(8000) = '0',
@qaqi BIT = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @caseloadCount bit, @isQAQI BIT
SELECT @isQAQI = [qaqi] FROM dbo.tbl_SN_Staff WHERE [id] = @eStaffID;
DECLARE @teamClientCount INT
SELECT @teamClientCount = COUNT(clientID) FROM dbo.tbl_SN_TeamClients
JOIN dbo.tbl_SN_TeamStaff ON dbo.tbl_SN_TeamClients.teamID = dbo.tbl_SN_TeamStaff.teamID
WHERE dbo.tbl_SN_TeamStaff.staffID = @staffID;
---- Get the count of caseload for the current user
--IF EXISTS(SELECT clientID FROM dbo.tbl_SN_StaffClients WHERE staffID = @estaffID) AND (@isQAQI = 0)
-- Set @caseloadCount = 1
--else
--Set @caseloadCount = 0;
-- Insert statements for procedure here
WITH BIRPNoteActivity AS (
SELECT v.clientID, v.billingStatus, v.locked, v.printed, v.recordID,
v.dateAdded, v.bnoteID, v.serviceDate, v.currentPolicyNum,
v.staffID, stafffullname, clientfullname, v.contactNum,
v.denialCode, v.amtPaid, v.datePaid, v.billingStatusID, v.lastCommentDate, v.lastCommentAuthor,
v.contactType, v.[service], v.notestatus, v.inTrash, v.trashedBy,
v.trashedbylname, v.trashedbyfname, v.dateTrashed, v.serviceCode, v.companyname,
v.serviceID, v.totMileage, v.dob, v.startTime, v.numDaysShowLateNote, v.fullServiceCode, v.clientStatus,
v.endTime, v.staffPaid, v.dateStaffPaid, v.serviceRate, v.flagNote,
ISNULL((SELECT re.referrer from dbo.tbl_SN_ProviderReferrals re WHERE re.id = v.referralID), '*No Referral*') as referrer,
ISNULL((SELECT re.npi from dbo.tbl_SN_ProviderReferrals re WHERE re.id = v.referralID), '*N/A*') as refNPI,
(SELECT COUNT(h.id) FROM dbo.tbl_SN_BIRP_NoteComments h WHERE h.bnoteID = v.bnoteID) AS numcomments,
dbo.func_GetBIRPNoteGoalsXML(v.bnoteID) as 'Goals',
(SELECT TOP 1 a.custAuthID FROM dbo.tbl_SN_Authorizations a
WHERE ((v.serviceID = a.serviceID)
or (v.serviceID IN (SELECT s.serviceID FROM dbo.tbl_SN_AuthServices s WHERE s.authID = a.[id])))
AND (v.clientID = a.clientID)
AND (v.serviceDate BETWEEN a.effectiveDate AND a.lapseDate)) AS [custAuthID],
v.theTotalMinutes, v.theService, v.locName, v.specificLocation,
v.signDate, v.dateSigned,
(SELECT insuranceType FROM dbo.tbl_SN_InsuranceTypes WHERE [id] = v.[currentFundingSourceID]) AS [insuranceType],
v.currentPolicyNum AS [policyNum],
ISNULL((SELECT TOP 1 cd.dxcode FROM dbo.tbl_SN_ClientDiags cd
WHERE (cd.clientID = v.clientID)
AND (cd.isPrimary = 1)), 'NO PRIMARY Dx') as diag,
totalUnits, v.setting, (SELECT numdaysflaglatenote FROM tbl_sn_providers p WHERE p.id = v.providerID) AS [numDaysFlagLateNote],
v.lockedbyfname, v.lockedbylname,
(SELECT COUNT(bns.id) FROM dbo.tbl_SN_BIRP_NoteSignatures bns WHERE bns.signedBy = 'AS' AND bns.bnoteID = v.bnoteID) AS [addSigCount]
FROM dbo.v_sn_birpnoteactivity v WITH (NOLOCK)
JOIN iter_intlist_to_tbl(@clientIDs) c ON (v.clientID = c.number OR @clientIDs = '0')
JOIN iter_intlist_to_tbl(@staffIDs) s ON (v.staffID = s.number OR @staffIDs = '0')
JOIN iter_intlist_to_tbl(@fundingSourceIDs) f ON (v.[currentFundingSourceID] = f.number OR @fundingSourceIDs = '0')
JOIN iter_intlist_to_tbl(@billingStatusIDs) b ON (v.billingStatusID = b.number OR @billingStatusIDs = '0')
JOIN iter_intlist_to_tbl(@noteStatusIDs) ns ON (v.statusID = ns.number OR @noteStatusIDs = '0')
JOIN iter_intlist_to_tbl(@serviceIDs) ser ON (v.serviceID = ser.number OR @serviceIDs = '0')
--JOIN dbo.udf_tbl_StaffClientIDs(@estaffID, @caseloadIDs, @providerID, @qaqi) sc ON (v.clientID = sc.clientID OR (@caseLoadIDs = '0' AND @teamClientCount = 0))
JOIN dbo.udf_tbl_StaffClientIDs(@estaffID, @caseloadIDs, @providerID, @qaqi) sc ON ((v.clientID = sc.clientID) OR (sc.clientID = 0))--(@caseLoadIDs = '0' AND @teamClientCount = 0)
--JOIN iter_intlist_to_tbl(@caseloadIDs) ca ON (v.clientID = ca.number OR @caseloadIDs = '0')
JOIN iter_intlist_to_tbl(@locIDs) lo ON (v.locID = lo.number OR @locIDs = '0')
WHERE (v.providerID = @providerID) AND
(v.locID = @locID OR @locID = 0) AND
(v.inTrash = @trashed) AND
(v.serviceID = @serviceID OR @serviceID = 0) AND
(v.statusID = @noteStatusID OR @noteStatusID = 0) AND
(v.locked = @locked OR @locked = -1) AND
(v.printed = @printed OR @printed = -1) AND
(v.billingStatusID = @billingStatusID OR @billingStatusID = 0) AND
(v.[currentFundingSourceID] = @insuranceTypeID OR @insuranceTypeID = 0) AND
(v.staffID = @staffID OR @staffID = 0) AND
(v.serviceDate BETWEEN @startDate AND @endDate) AND
(v.clientID = @clientID OR @clientID = 0) --AND
-- caseload
--((v.clientID IN (SELECT sc.[id] FROM dbo.func_tbl_StaffClients(@estaffID) sc)) OR @caseloadCount = 0)
)
SELECT
clientID,
staffID,
locName,
companyname,
dob,
--abbreviation,
--isPrivate,
billingStatus,
dateAdded,
locked,
printed,
recordID,
insurancetype,
bnoteID,
serviceDate,
currentPolicyNum AS [policyNum],
stafffullname,
clientfullname,
datePaid,
amtPaid,
denialCode,
contactType,
contactNum,
[service],
[serviceCode],
serviceRate,
[theService],
[fullServiceCode],
[noteStatus],
[noteStatus] AS [status],
serviceID,
staffPaid,
dateStaffPaid,
[setting],
--[goalName],
Goals,
[diag],
referrer,
refNPI,
--providerNum,
custAuthID,
theTotalMinutes,
--theTotalMinutes AS [totalMins],
[totalUnits],
totalUnits * serviceRate AS [amtBilled],
specificLocation,
--numdayslatenote,
--numdaysshowlatenote,
numcomments,
[totMileage],
inTrash,
[dateTrashed],
[trashedbyfname],
[trashedbylname],
startTime,
endTime,
[lastCommentDate],
[lastCommentAuthor],
[dateSigned],
[signDate],
[trashedBy],
[dateTrashed],
[billingStatusID],
billingStatusID AS [salStatusID],
billingStatus AS [salStatus],
[numDaysShowLateNote],
[numDaysFlagLateNote],
[lockedbyfname],
[lockedbylname],
[flagNote],
[clientStatus],
[addSig] =
CASE
WHEN addSigCount > 0 THEN ' (Add. Sig)'
ELSE ''
END
FROM BIRPNoteActivity
ORDER BY servicedate
END
------------------------------------------------------------------------
Note :- Hello Friends, i know my given information is very large...so if you have any suggetion,please suggest me..
Submit an article or tip