Debugging Is NO FUN!!!
This week, I've been testing the new Report Distribution application at work--the one I've been working on for months now (Well, of and on anyways, also been coding other applications among other things)!
I love developing programs... figuring out and solving problems logically... mathematically. Everyday, you watch in awe at your own creation as if it were a work of Art, as it slowly progresses and transforms into a new tool that others will one day use to make their work and LIFE A LOT better... And so, when testing phase comes, it's kind of bittersweet! You get a HUGE sigh of relief that the project is FINALLY complete (almost!)... but every programmer and Software Engineer knows what a pain it is to debug programs. Well, not only do you have to intentionally "break" the application, you have to undo what you did as well and figure out the following...
a.) What caused the program to break?
b.) How did it happen?
c.) Was it a compilation error... a run-time error?
d.) Or was it a syntax error?
e.) Did I use the right algorithm? (No, scratch that, did I use the BEST algorithm?)
f.) Did I follow the specs correctly? Is it "user-friendly"?
g.) Am I maintaining relationships and database schemas (if using SQL)?
h.) Am I getting good, PRECISE data (this is VERY important, especially for CPA's)?
i.) And the list goes on......
And today, I was stuck figuring out an "unhandled exception" on one of my pages. So, I thought, maybe somebody reading my blog can figure out part of my code... So, to all fellow Software Engineers and programmers out there... Here's a challenge. Maybe you can spot what was wrong with my code? By the way, I'm programming in Visual Basic (Microsoft Studio 2005) in ASP.NET with embedded SQL code (Microsoft SQL Server Management Studio 2005 - syntax compatible)... NOT MySQL!!!! If you think you know the problem, e-mail me: KerstinWalling(at)gmail(dot)com. I'll explain in a more-detailed fashion the sequence of errors that was thrown at me...
Oh, and here's my code (below) :-) HAVE FUN...
#Region "Data"
Private Sub Get_Data()
Dim dtRC As DataTable
Dim strConnection As String
Dim strSql As String
Dim strRecID, strLastName, strJobTitle, strCompany As String
Dim strSortBy As String
Dim strStatus, strStatusValue As String
Dim strStandard, strStandardValue As String
'*
'* Reset dgRC page to 0 dgRC.CurrentPageIndex = 0
strConnection = AppSettings(CType(Session("sCONNVAR"), String))
'*
'*** From Recipient Inquiry Request
If hdOrigin.Value = "RPTRCIREQ" Or strPrevOrigin = "RPTRCIREQ" Then
'*
strSortBy = Trim(Session("RPTRCIREQ_strSortBy"))
strStatus = Trim(Session("RPTRCIREQ_strStatus"))
strStandard = Trim(Session("RPTRCIREQ_strStandard"))
'*
If strSortBy = "LastName" Then
strLastName = Trim(Session("RPTRCIREQ_strLastName"))
ElseIf strSortBy = "JobTitle"
Then strJobTitle = Trim(Session("RPTRCIREQ_strJobTitle"))
ElseIf strSortBy = "Company" Then
strCompany = Trim(Session("RPTRCIREQ_strCompany"))
End If
'*
If strStatus = "Active" Then
strStatusValue = "A"
ElseIf strStatus = "Inactive" Then
strStatusValue = "I"
Else
strStatusValue = ""
End If
'*
If strStandard = "Yes" Then
strStandardValue = "Y"
ElseIf strStandard = "No" Then
strStandardValue = "N"
Else
strStandardValue = ""
End If
'*** From Recipient Multiple Inquiry
ElseIf hdOrigin.Value = "RPTRCIM" Or strPrevOrigin = "RPTRCIM" Then
strStatus = Trim(Session("RPTRCIM_strStat"))
strStandard = Trim(Session("RPTRCIM_strStd"))
'*
If strStatus = "Active" Then
strStatusValue = "A"
ElseIf strStatus = "Inactive" Then
strStatusValue = "I"
Else
strStatusValue = ""
End If
'*
If strStandard = "Yes" Then
strStandardValue = "Y"
ElseIf strStandard = "No" Then
strStandardValue = "N"
Else
strStandardValue = ""
End If
End If
'*
'*
strSql = "SELECT "
strSql = strSql & "RecipientID = IsNull(Rpt_Recipient.RecipientID, ''), "
strSql = strSql & "Stat = (CASE WHEN Rpt_Recipient.Stat = 'A' THEN 'Active' "
strSql = strSql & " WHEN Rpt_Recipient.Stat = 'I' THEN 'Inactive' END), "
strSql = strSql & "Standard = (CASE WHEN Rpt_Recipient.StdInd = 'Y' THEN 'Yes' "
strSql = strSql & " WHEN Rpt_Recipient.StdInd = 'N' THEN 'No' END), "
strSql = strSql & "LName = (CASE WHEN Rpt_Recipient.EmpID IS NULL THEN "
strSql = strSql & " IsNull(Rpt_Recipient.LName, '') "
strSql = strSql & " ELSE "
strSql = strSql & " IsNull(Emp_View1.LName, '') "
strSql = strSql & " END), "
strSql = strSql & "Name = (CASE WHEN Rpt_Recipient.EmpID IS NULL THEN "
strSql = strSql & " ((CASE WHEN Rpt_Recipient.FName IS NULL THEN '' "
strSql = strSql & " ELSE Rpt_Recipient.FName + ' ' END) "
strSql = strSql & " + (CASE WHEN Rpt_Recipient.MName IS NULL THEN '' " strSql = strSql & " ELSE Rpt_Recipient.MName + ' ' END) "
strSql = strSql & " + IsNull(Rpt_Recipient.LName, '') "
strSql = strSql & " + (CASE WHEN Rpt_Recipient.Suffix IS NULL THEN '' "
strSql = strSql & " ELSE ', ' + Rpt_Recipient.Suffix END)) "
strSql = strSql & " ELSE "
strSql = strSql & " (CASE WHEN Emp_View1.MI IS NULL THEN "
strSql = strSql & " (IsNull(Emp_View1.FName, '') + ' ' + IsNull(Emp_View1.LName, '')) "
strSql = strSql & " ELSE "
strSql = strSql & " (IsNull(Emp_View1.FName, '') + ' ' + IsNull(Emp_View1.MI, '') + '. ' "
strSql = strSql & " + IsNull(Emp_View1.LName, '')) "
strSql = strSql & " END) " strSql = strSql & " END), "
strSql = strSql & "JobTitle = (CASE WHEN Rpt_Recipient.EmpID IS NULL THEN "
strSql = strSql & " IsNull(Rpt_Recipient.JobTitle, '') "
strSql = strSql & " ELSE "
strSql = strSql & " IsNull(Emp_JobClassTitle.Descr, '') "
strSql = strSql & " END), "
strSql = strSql & "Company = (CASE WHEN Rpt_Recipient.EmpID IS NULL THEN "
strSql = strSql & " IsNull(Rpt_Recipient.Company, '') "
strSql = strSql & " ELSE "
strSql = strSql & " 'AUDITOR GENERAL' "
strSql = strSql & " END), "
strSql = strSql & "DivSect = (SUBSTRING(Emp_View1.Org,5,4) + ' ' + SUBSTRING (Emp_View1.Org,9,3)), "
strSql = strSql & "Addr11 = IsNull(Rpt_Recipient.Addr11, ''), Addr12 = IsNull(Rpt_Recipient.Addr12, ''), "
strSql = strSql & "Addr13 = IsNull(Rpt_Recipient.Addr13, ''), Addr14 = IsNull(Rpt_Recipient.Addr14, ''), Addr15 = IsNull(Rpt_Recipient.Addr15, ''),"
strSql = strSql & "City1 = (IsNull(Rpt_Recipient.City1, '') + ','), "
strSql = strSql & "State1 = IsNull(Gen_StateTitle1.Descr, ''), "
strSql = strSql & "Zip1 = (CASE WHEN LEN(Rpt_Recipient.Zip1) = 5 THEN "
strSql = strSql & " Rpt_Recipient.Zip1 "
strSql = strSql & " WHEN LEN(Rpt_Recipient.Zip1) = 9 THEN "
strSql = strSql & " (LEFT(Rpt_Recipient.Zip1,5) + '-' "
strSql = strSql & " + RIGHT(Rpt_Recipient.Zip1,4)) "
strSql = strSql & " END), "
strSql = strSql & "City2 = (IsNull(Rpt_Recipient.City2, '') + ','), "
strSql = strSql & "State2 = IsNull(Gen_StateTitle2.Descr, ''), "
strSql = strSql & "Zip2 = (CASE WHEN LEN(Rpt_Recipient.Zip2) = 5 THEN "
strSql = strSql & " Rpt_Recipient.Zip2 " strSql = strSql & " WHEN LEN(Rpt_Recipient.Zip2) = 9 THEN "
strSql = strSql & " (LEFT(Rpt_Recipient.Zip2,5) + '-' "
strSql = strSql & " + RIGHT(Rpt_Recipient.Zip2,4)) "
strSql = strSql & " END), "
strSql = strSql & "Addr21 = IsNull(Rpt_Recipient.Addr21, ''), Addr22 = IsNull(Rpt_Recipient.Addr22, ''), "
strSql = strSql & "Addr23 = IsNull(Rpt_Recipient.Addr23, ''), Addr24 = IsNull(Rpt_Recipient.Addr24, ''), Addr25 = IsNull(Rpt_Recipient.Addr25, ''), "
strSql = strSql & "Phone = (CASE WHEN Rpt_Recipient.EmpID IS NULL THEN "
strSql = strSql & " IsNull(Rpt_Recipient.Phone, '') "
strSql = strSql & " ELSE "
strSql = strSql & " (CASE WHEN Emp_View1.WrkPhone IS NOT NULL "
strSql = strSql & " AND Emp_View1.WrkCity = 'TALLAHASSEE' THEN "
strSql = strSql & " '(' + LEFT(Emp_View1.WrkPhone,3) + ') ' "
strSql = strSql & " + SUBSTRING(Emp_View1.WrkPhone,4,3) + '-' "
strSql = strSql & " + RIGHT(Emp_View1.WrkPhone,4) "
strSql = strSql & " WHEN Emp_View1.WrkSunCom IS NOT NULL "
strSql = strSql & " AND Emp_View1.WrkCity <> 'TALLAHASSEE' THEN "
strSql = strSql & " 'SC ' + SUBSTRING(Emp_View1.WrkSunCom,4,3) + '-' "
strSql = strSql & " + RIGHT(Emp_View1.WrkSunCom,4) "
strSql = strSql & " END) "
strSql = strSql & " END), "
strSql = strSql & "Extension = IsNull(Rpt_Recipient.Extension, ''), Fax = IsNull(Rpt_Recipient.Fax, ''), "
strSql = strSql & "HCCopies = (CASE WHEN Rpt_Recipient.HCCopies = 0 THEN "
strSql = strSql & " NULL "
strSql = strSql & " ELSE "
strSql = strSql & " Rpt_Recipient.HCCopies "
strSql = strSql & " END), "
strSql = strSql & "LetSalutation = IsNull(Rpt_Recipient.LetSalutation, ''), AddrSalutation = IsNull(Rpt_Recipient.AddrSalutation, ''), "
strSql = strSql & "Email1 = IsNull(Rpt_Recipient.Email1, ''), Email2 = IsNull(Rpt_Recipient.Email2, ''), DeptNo = IsNull(Rpt_Recipient.DeptNo, ''), "
strSql = strSql & "SendLetter = (CASE WHEN Rpt_Recipient.SendLetterInd = 'Y' THEN 'Yes' ELSE 'No' END), "
strSql = strSql & "FedListing = (CASE WHEN Rpt_Recipient.FedListInd = 'Y' THEN 'Yes' ELSE 'No' END), "
strSql = strSql & "Note = IsNull(Rpt_Recipient.Note, ''), "
strSql = strSql & "HCFormat = IsNull(Rpt_FormatTitle_HC.Descr, ''), EMFormat = IsNull(Rpt_FormatTitle_EM.Descr, ''), "
strSql = strSql & "HCDelivery = IsNull(Rpt_DeliveryTitle_HC.Descr, ''), "
strSql = strSql & "EMDelivery = IsNull(Rpt_DeliveryTitle_EM.Descr, ''), "
strSql = strSql & "HCShip = IsNull(Rpt_ShipTitle.Descr, ''), "
strSql = strSql & "LastChange = ( SELECT TOP 1 (CONVERT(Char(8), Rpt_RecChanges.AddDt, 01) "
strSql = strSql & " + ' ' + IsNull(Rpt_RecChanges.LastUpID, '') "
strSql = strSql & " + ' ' + IsNull(Rpt_RecChanges.Text, '')) "
strSql = strSql & " FROM Rpt_RecChanges "
strSql = strSql & " WHERE Rpt_RecChanges.RecipientID = Rpt_Recipient.RecipientID "
strSql = strSql & " ORDER BY Rpt_RecChanges.AddDt DESC ), "
strSql = strSql & "LastChangeCount = ( SELECT COUNT(Rpt_RecChanges.AddDt) "
strSql = strSql & " FROM Rpt_RecChanges "
strSql = strSql & " WHERE Rpt_RecChanges.RecipientID =Rpt_Recipient.RecipientID )"
strSql = strSql & "FROM Rpt_Recipient "
strSql = strSql & "LEFT OUTER JOIN Emp_View1 "
strSql = strSql & " ON Rpt_Recipient.EmpID = Emp_View1.EmpID "
strSql = strSql & "LEFT OUTER JOIN Emp_JobClassTitle "
strSql = strSql & " ON Emp_View1.JobClass = Emp_JobClassTitle.JobClass "
strSql = strSql & " AND Emp_JobClassTitle.CntInd = 'C' "
strSql = strSql & "LEFT OUTER JOIN Gen_StateTitle AS Gen_StateTitle1 "
strSql = strSql & " ON Rpt_Recipient.State1 = Gen_StateTitle1.State "
strSql = strSql & "LEFT OUTER JOIN Gen_StateTitle AS Gen_StateTitle2 "
strSql = strSql & " ON Rpt_Recipient.State2 = Gen_StateTitle2.State "
strSql = strSql & "LEFT OUTER JOIN Rpt_FormatTitle AS Rpt_FormatTitle_HC "
strSql = strSql & " ON Rpt_Recipient.HCFormatCode = Rpt_FormatTitle_HC.FormatCode " strSql = strSql & "LEFT OUTER JOIN Rpt_FormatTitle AS Rpt_FormatTitle_EM "
strSql = strSql & " ON Rpt_Recipient.EMFormatCode = Rpt_FormatTitle_EM.FormatCode " strSql = strSql & "LEFT OUTER JOIN Rpt_DeliveryTitle AS Rpt_DeliveryTitle_HC "
strSql = strSql & " ON Rpt_Recipient.HCDelCode = Rpt_DeliveryTitle_HC.DelCode "
strSql = strSql & "LEFT OUTER JOIN Rpt_DeliveryTitle AS Rpt_DeliveryTitle_EM "
strSql = strSql & " ON Rpt_Recipient.EMDelCode = Rpt_DeliveryTitle_EM.DelCode "
strSql = strSql & "LEFT OUTER JOIN Rpt_ShipTitle "
strSql = strSql & " ON Rpt_Recipient.HCShipCode = Rpt_ShipTitle.ShipCode "
'*
If hdOrigin.Value = "RPTRCIREQ" Or strPrevOrigin = "RPTRCIREQ" Then
strSql = strSql & "WHERE Rpt_Recipient.Stat LIKE '" & strStatusValue & "%' "
strSql = strSql & " AND Rpt_Recipient.StdInd LIKE '" & strStandardValue & "%' "
If strSortBy = "LastName" Then If Len(strLastName) <> 0 Then
strSql = strSql & " AND (Rpt_Recipient.LName LIKE '" & ReplaceQuote(strLastName) & "%strSql = strSql & " OR Emp_View1.LName LIKE '" & ReplaceQuote(strLastName) & "%') "
End If
strSql = strSql & " ORDER BY LName, Name, JobTitle, Company"
ElseIf strSortBy = "JobTitle" Then
If Len(strJobTitle) <> 0 Then
strSql = strSql & " AND (Rpt_Recipient.JobTitle LIKE '" & ReplaceQuote(strJobTitle) & "%' " strSql = strSql & " OR Emp_JobClassTitle.Descr LIKE '" & ReplaceQuote(strJobTitle) & "%') " End If
strSql = strSql & " ORDER BY JobTitle, LName, Name, Company"
ElseIf strSortBy = "Company" Then
If Len(strCompany) <> 0 Then
Dim strAG, strEditAG As String
'*
strAG = "AUDITOR GENERAL"
strEditAG = Left(strAG, Len(strCompany))
'*
strSql = strSql & " AND (Rpt_Recipient.Company LIKE '" & ReplaceQuote(strCompany) & "%' " strSql = strSql & " OR (Rpt_Recipient.EmpID IS NOT NULL " strSql = strSql & " AND '" & strEditAG & "' = '" & ReplaceQuote(strCompany) & "')) "
End If
strSql = strSql & " ORDER BY Company, LName, Name, JobTitle"
End If
ElseIf hdOrigin.Value = "RPTRCIM" Or strPrevOrigin = "RPTRCIM" Then
strRecID = Session("RPTRCIM_strRecipientID")
strSql = strSql & "WHERE Rpt_Recipient.Stat LIKE '" & strStatusValue & "%' "
strSql = strSql & " AND Rpt_Recipient.StdInd LIKE '" & strStandardValue & "%' "
strSql = strSql & " AND Rpt_Recipient.RecipientID = '" & ReplaceQuote(strRecID) & "'"
End If
'*
strConnection = AppSettings(CType(Session("sCONNVAR"), String))
'*
tRC = ExecuteDataset(strConnection, CommandType.Text, strSql).Tables(0)
'*
hdRecipientID.Value = dtRC.Rows(0).Item("RecipientID")
Session("RPTRCIS_dtRC") = dtRC
'*
blnDataFound = False
If dtRC.Rows.Count > 0 Then
blnDataFound = True
dgRC.DataSource = dtRC
'* Bind data here when Origin is the
'* 1) Inquiry Request or the Single Inquiry
'* 2) Update page to get the new dg.PageCount then DataBind again, see "Note" below dgRC.DataBind()
If hdOrigin.Value = "RPTRCU" Or hdOrigin.Value = "RPTRCIDG" Or hdOrigin.Value = "RPTRCUDG" Then
If Session("RPTRCIS_LastPage") = True Then
'* When the only record on the last page is deleted or no longer within original request
If CInt(Session("RPTRCIS_strPageCount_BeforeUpdate")) > dgRC.PageCount Then
'* Go back to prev page but one dgRC.CurrentPageIndex = CInt(Session("RPTRCIS_strPageNo")) - 1 Else
'* Go back to prev page
dgRC.CurrentPageIndex = CInt(Session("RPTRCIS_strPageNo"))
End If
Else
'* When not the last page
'* Go back to same page
dgRC.CurrentPageIndex = CInt(Session("RPTRCIS_strPageNo"))
End If
'* Note: Adjusting the page number only affects the page index being displayed,
'* need to rebind to show the correct data for the page index adjusted above
dgRC.DataBind()
End If
Else
blnDataFound = False
Exit Sub
End If
'*
'*
dtRC = Nothing
'*
End Sub
Private Sub Restore_RC_Data_BindDG()
dgRC.DataSource = Session("RPTRCIS_dtRC")
dgRC.DataBind()
End Sub
#End Region
I love developing programs... figuring out and solving problems logically... mathematically. Everyday, you watch in awe at your own creation as if it were a work of Art, as it slowly progresses and transforms into a new tool that others will one day use to make their work and LIFE A LOT better... And so, when testing phase comes, it's kind of bittersweet! You get a HUGE sigh of relief that the project is FINALLY complete (almost!)... but every programmer and Software Engineer knows what a pain it is to debug programs. Well, not only do you have to intentionally "break" the application, you have to undo what you did as well and figure out the following...
a.) What caused the program to break?
b.) How did it happen?
c.) Was it a compilation error... a run-time error?
d.) Or was it a syntax error?
e.) Did I use the right algorithm? (No, scratch that, did I use the BEST algorithm?)
f.) Did I follow the specs correctly? Is it "user-friendly"?
g.) Am I maintaining relationships and database schemas (if using SQL)?
h.) Am I getting good, PRECISE data (this is VERY important, especially for CPA's)?
i.) And the list goes on......
And today, I was stuck figuring out an "unhandled exception" on one of my pages. So, I thought, maybe somebody reading my blog can figure out part of my code... So, to all fellow Software Engineers and programmers out there... Here's a challenge. Maybe you can spot what was wrong with my code? By the way, I'm programming in Visual Basic (Microsoft Studio 2005) in ASP.NET with embedded SQL code (Microsoft SQL Server Management Studio 2005 - syntax compatible)... NOT MySQL!!!! If you think you know the problem, e-mail me: KerstinWalling(at)gmail(dot)com. I'll explain in a more-detailed fashion the sequence of errors that was thrown at me...
Oh, and here's my code (below) :-) HAVE FUN...
#Region "Data"
Private Sub Get_Data()
Dim dtRC As DataTable
Dim strConnection As String
Dim strSql As String
Dim strRecID, strLastName, strJobTitle, strCompany As String
Dim strSortBy As String
Dim strStatus, strStatusValue As String
Dim strStandard, strStandardValue As String
'*
'* Reset dgRC page to 0 dgRC.CurrentPageIndex = 0
strConnection = AppSettings(CType(Session("sCONNVAR"), String))
'*
'*** From Recipient Inquiry Request
If hdOrigin.Value = "RPTRCIREQ" Or strPrevOrigin = "RPTRCIREQ" Then
'*
strSortBy = Trim(Session("RPTRCIREQ_strSortBy"))
strStatus = Trim(Session("RPTRCIREQ_strStatus"))
strStandard = Trim(Session("RPTRCIREQ_strStandard"))
'*
If strSortBy = "LastName" Then
strLastName = Trim(Session("RPTRCIREQ_strLastName"))
ElseIf strSortBy = "JobTitle"
Then strJobTitle = Trim(Session("RPTRCIREQ_strJobTitle"))
ElseIf strSortBy = "Company" Then
strCompany = Trim(Session("RPTRCIREQ_strCompany"))
End If
'*
If strStatus = "Active" Then
strStatusValue = "A"
ElseIf strStatus = "Inactive" Then
strStatusValue = "I"
Else
strStatusValue = ""
End If
'*
If strStandard = "Yes" Then
strStandardValue = "Y"
ElseIf strStandard = "No" Then
strStandardValue = "N"
Else
strStandardValue = ""
End If
'*** From Recipient Multiple Inquiry
ElseIf hdOrigin.Value = "RPTRCIM" Or strPrevOrigin = "RPTRCIM" Then
strStatus = Trim(Session("RPTRCIM_strStat"))
strStandard = Trim(Session("RPTRCIM_strStd"))
'*
If strStatus = "Active" Then
strStatusValue = "A"
ElseIf strStatus = "Inactive" Then
strStatusValue = "I"
Else
strStatusValue = ""
End If
'*
If strStandard = "Yes" Then
strStandardValue = "Y"
ElseIf strStandard = "No" Then
strStandardValue = "N"
Else
strStandardValue = ""
End If
End If
'*
'*
strSql = "SELECT "
strSql = strSql & "RecipientID = IsNull(Rpt_Recipient.RecipientID, ''), "
strSql = strSql & "Stat = (CASE WHEN Rpt_Recipient.Stat = 'A' THEN 'Active' "
strSql = strSql & " WHEN Rpt_Recipient.Stat = 'I' THEN 'Inactive' END), "
strSql = strSql & "Standard = (CASE WHEN Rpt_Recipient.StdInd = 'Y' THEN 'Yes' "
strSql = strSql & " WHEN Rpt_Recipient.StdInd = 'N' THEN 'No' END), "
strSql = strSql & "LName = (CASE WHEN Rpt_Recipient.EmpID IS NULL THEN "
strSql = strSql & " IsNull(Rpt_Recipient.LName, '') "
strSql = strSql & " ELSE "
strSql = strSql & " IsNull(Emp_View1.LName, '') "
strSql = strSql & " END), "
strSql = strSql & "Name = (CASE WHEN Rpt_Recipient.EmpID IS NULL THEN "
strSql = strSql & " ((CASE WHEN Rpt_Recipient.FName IS NULL THEN '' "
strSql = strSql & " ELSE Rpt_Recipient.FName + ' ' END) "
strSql = strSql & " + (CASE WHEN Rpt_Recipient.MName IS NULL THEN '' " strSql = strSql & " ELSE Rpt_Recipient.MName + ' ' END) "
strSql = strSql & " + IsNull(Rpt_Recipient.LName, '') "
strSql = strSql & " + (CASE WHEN Rpt_Recipient.Suffix IS NULL THEN '' "
strSql = strSql & " ELSE ', ' + Rpt_Recipient.Suffix END)) "
strSql = strSql & " ELSE "
strSql = strSql & " (CASE WHEN Emp_View1.MI IS NULL THEN "
strSql = strSql & " (IsNull(Emp_View1.FName, '') + ' ' + IsNull(Emp_View1.LName, '')) "
strSql = strSql & " ELSE "
strSql = strSql & " (IsNull(Emp_View1.FName, '') + ' ' + IsNull(Emp_View1.MI, '') + '. ' "
strSql = strSql & " + IsNull(Emp_View1.LName, '')) "
strSql = strSql & " END) " strSql = strSql & " END), "
strSql = strSql & "JobTitle = (CASE WHEN Rpt_Recipient.EmpID IS NULL THEN "
strSql = strSql & " IsNull(Rpt_Recipient.JobTitle, '') "
strSql = strSql & " ELSE "
strSql = strSql & " IsNull(Emp_JobClassTitle.Descr, '') "
strSql = strSql & " END), "
strSql = strSql & "Company = (CASE WHEN Rpt_Recipient.EmpID IS NULL THEN "
strSql = strSql & " IsNull(Rpt_Recipient.Company, '') "
strSql = strSql & " ELSE "
strSql = strSql & " 'AUDITOR GENERAL' "
strSql = strSql & " END), "
strSql = strSql & "DivSect = (SUBSTRING(Emp_View1.Org,5,4) + ' ' + SUBSTRING (Emp_View1.Org,9,3)), "
strSql = strSql & "Addr11 = IsNull(Rpt_Recipient.Addr11, ''), Addr12 = IsNull(Rpt_Recipient.Addr12, ''), "
strSql = strSql & "Addr13 = IsNull(Rpt_Recipient.Addr13, ''), Addr14 = IsNull(Rpt_Recipient.Addr14, ''), Addr15 = IsNull(Rpt_Recipient.Addr15, ''),"
strSql = strSql & "City1 = (IsNull(Rpt_Recipient.City1, '') + ','), "
strSql = strSql & "State1 = IsNull(Gen_StateTitle1.Descr, ''), "
strSql = strSql & "Zip1 = (CASE WHEN LEN(Rpt_Recipient.Zip1) = 5 THEN "
strSql = strSql & " Rpt_Recipient.Zip1 "
strSql = strSql & " WHEN LEN(Rpt_Recipient.Zip1) = 9 THEN "
strSql = strSql & " (LEFT(Rpt_Recipient.Zip1,5) + '-' "
strSql = strSql & " + RIGHT(Rpt_Recipient.Zip1,4)) "
strSql = strSql & " END), "
strSql = strSql & "City2 = (IsNull(Rpt_Recipient.City2, '') + ','), "
strSql = strSql & "State2 = IsNull(Gen_StateTitle2.Descr, ''), "
strSql = strSql & "Zip2 = (CASE WHEN LEN(Rpt_Recipient.Zip2) = 5 THEN "
strSql = strSql & " Rpt_Recipient.Zip2 " strSql = strSql & " WHEN LEN(Rpt_Recipient.Zip2) = 9 THEN "
strSql = strSql & " (LEFT(Rpt_Recipient.Zip2,5) + '-' "
strSql = strSql & " + RIGHT(Rpt_Recipient.Zip2,4)) "
strSql = strSql & " END), "
strSql = strSql & "Addr21 = IsNull(Rpt_Recipient.Addr21, ''), Addr22 = IsNull(Rpt_Recipient.Addr22, ''), "
strSql = strSql & "Addr23 = IsNull(Rpt_Recipient.Addr23, ''), Addr24 = IsNull(Rpt_Recipient.Addr24, ''), Addr25 = IsNull(Rpt_Recipient.Addr25, ''), "
strSql = strSql & "Phone = (CASE WHEN Rpt_Recipient.EmpID IS NULL THEN "
strSql = strSql & " IsNull(Rpt_Recipient.Phone, '') "
strSql = strSql & " ELSE "
strSql = strSql & " (CASE WHEN Emp_View1.WrkPhone IS NOT NULL "
strSql = strSql & " AND Emp_View1.WrkCity = 'TALLAHASSEE' THEN "
strSql = strSql & " '(' + LEFT(Emp_View1.WrkPhone,3) + ') ' "
strSql = strSql & " + SUBSTRING(Emp_View1.WrkPhone,4,3) + '-' "
strSql = strSql & " + RIGHT(Emp_View1.WrkPhone,4) "
strSql = strSql & " WHEN Emp_View1.WrkSunCom IS NOT NULL "
strSql = strSql & " AND Emp_View1.WrkCity <> 'TALLAHASSEE' THEN "
strSql = strSql & " 'SC ' + SUBSTRING(Emp_View1.WrkSunCom,4,3) + '-' "
strSql = strSql & " + RIGHT(Emp_View1.WrkSunCom,4) "
strSql = strSql & " END) "
strSql = strSql & " END), "
strSql = strSql & "Extension = IsNull(Rpt_Recipient.Extension, ''), Fax = IsNull(Rpt_Recipient.Fax, ''), "
strSql = strSql & "HCCopies = (CASE WHEN Rpt_Recipient.HCCopies = 0 THEN "
strSql = strSql & " NULL "
strSql = strSql & " ELSE "
strSql = strSql & " Rpt_Recipient.HCCopies "
strSql = strSql & " END), "
strSql = strSql & "LetSalutation = IsNull(Rpt_Recipient.LetSalutation, ''), AddrSalutation = IsNull(Rpt_Recipient.AddrSalutation, ''), "
strSql = strSql & "Email1 = IsNull(Rpt_Recipient.Email1, ''), Email2 = IsNull(Rpt_Recipient.Email2, ''), DeptNo = IsNull(Rpt_Recipient.DeptNo, ''), "
strSql = strSql & "SendLetter = (CASE WHEN Rpt_Recipient.SendLetterInd = 'Y' THEN 'Yes' ELSE 'No' END), "
strSql = strSql & "FedListing = (CASE WHEN Rpt_Recipient.FedListInd = 'Y' THEN 'Yes' ELSE 'No' END), "
strSql = strSql & "Note = IsNull(Rpt_Recipient.Note, ''), "
strSql = strSql & "HCFormat = IsNull(Rpt_FormatTitle_HC.Descr, ''), EMFormat = IsNull(Rpt_FormatTitle_EM.Descr, ''), "
strSql = strSql & "HCDelivery = IsNull(Rpt_DeliveryTitle_HC.Descr, ''), "
strSql = strSql & "EMDelivery = IsNull(Rpt_DeliveryTitle_EM.Descr, ''), "
strSql = strSql & "HCShip = IsNull(Rpt_ShipTitle.Descr, ''), "
strSql = strSql & "LastChange = ( SELECT TOP 1 (CONVERT(Char(8), Rpt_RecChanges.AddDt, 01) "
strSql = strSql & " + ' ' + IsNull(Rpt_RecChanges.LastUpID, '') "
strSql = strSql & " + ' ' + IsNull(Rpt_RecChanges.Text, '')) "
strSql = strSql & " FROM Rpt_RecChanges "
strSql = strSql & " WHERE Rpt_RecChanges.RecipientID = Rpt_Recipient.RecipientID "
strSql = strSql & " ORDER BY Rpt_RecChanges.AddDt DESC ), "
strSql = strSql & "LastChangeCount = ( SELECT COUNT(Rpt_RecChanges.AddDt) "
strSql = strSql & " FROM Rpt_RecChanges "
strSql = strSql & " WHERE Rpt_RecChanges.RecipientID =Rpt_Recipient.RecipientID )"
strSql = strSql & "FROM Rpt_Recipient "
strSql = strSql & "LEFT OUTER JOIN Emp_View1 "
strSql = strSql & " ON Rpt_Recipient.EmpID = Emp_View1.EmpID "
strSql = strSql & "LEFT OUTER JOIN Emp_JobClassTitle "
strSql = strSql & " ON Emp_View1.JobClass = Emp_JobClassTitle.JobClass "
strSql = strSql & " AND Emp_JobClassTitle.CntInd = 'C' "
strSql = strSql & "LEFT OUTER JOIN Gen_StateTitle AS Gen_StateTitle1 "
strSql = strSql & " ON Rpt_Recipient.State1 = Gen_StateTitle1.State "
strSql = strSql & "LEFT OUTER JOIN Gen_StateTitle AS Gen_StateTitle2 "
strSql = strSql & " ON Rpt_Recipient.State2 = Gen_StateTitle2.State "
strSql = strSql & "LEFT OUTER JOIN Rpt_FormatTitle AS Rpt_FormatTitle_HC "
strSql = strSql & " ON Rpt_Recipient.HCFormatCode = Rpt_FormatTitle_HC.FormatCode " strSql = strSql & "LEFT OUTER JOIN Rpt_FormatTitle AS Rpt_FormatTitle_EM "
strSql = strSql & " ON Rpt_Recipient.EMFormatCode = Rpt_FormatTitle_EM.FormatCode " strSql = strSql & "LEFT OUTER JOIN Rpt_DeliveryTitle AS Rpt_DeliveryTitle_HC "
strSql = strSql & " ON Rpt_Recipient.HCDelCode = Rpt_DeliveryTitle_HC.DelCode "
strSql = strSql & "LEFT OUTER JOIN Rpt_DeliveryTitle AS Rpt_DeliveryTitle_EM "
strSql = strSql & " ON Rpt_Recipient.EMDelCode = Rpt_DeliveryTitle_EM.DelCode "
strSql = strSql & "LEFT OUTER JOIN Rpt_ShipTitle "
strSql = strSql & " ON Rpt_Recipient.HCShipCode = Rpt_ShipTitle.ShipCode "
'*
If hdOrigin.Value = "RPTRCIREQ" Or strPrevOrigin = "RPTRCIREQ" Then
strSql = strSql & "WHERE Rpt_Recipient.Stat LIKE '" & strStatusValue & "%' "
strSql = strSql & " AND Rpt_Recipient.StdInd LIKE '" & strStandardValue & "%' "
If strSortBy = "LastName" Then If Len(strLastName) <> 0 Then
strSql = strSql & " AND (Rpt_Recipient.LName LIKE '" & ReplaceQuote(strLastName) & "%strSql = strSql & " OR Emp_View1.LName LIKE '" & ReplaceQuote(strLastName) & "%') "
End If
strSql = strSql & " ORDER BY LName, Name, JobTitle, Company"
ElseIf strSortBy = "JobTitle" Then
If Len(strJobTitle) <> 0 Then
strSql = strSql & " AND (Rpt_Recipient.JobTitle LIKE '" & ReplaceQuote(strJobTitle) & "%' " strSql = strSql & " OR Emp_JobClassTitle.Descr LIKE '" & ReplaceQuote(strJobTitle) & "%') " End If
strSql = strSql & " ORDER BY JobTitle, LName, Name, Company"
ElseIf strSortBy = "Company" Then
If Len(strCompany) <> 0 Then
Dim strAG, strEditAG As String
'*
strAG = "AUDITOR GENERAL"
strEditAG = Left(strAG, Len(strCompany))
'*
strSql = strSql & " AND (Rpt_Recipient.Company LIKE '" & ReplaceQuote(strCompany) & "%' " strSql = strSql & " OR (Rpt_Recipient.EmpID IS NOT NULL " strSql = strSql & " AND '" & strEditAG & "' = '" & ReplaceQuote(strCompany) & "')) "
End If
strSql = strSql & " ORDER BY Company, LName, Name, JobTitle"
End If
ElseIf hdOrigin.Value = "RPTRCIM" Or strPrevOrigin = "RPTRCIM" Then
strRecID = Session("RPTRCIM_strRecipientID")
strSql = strSql & "WHERE Rpt_Recipient.Stat LIKE '" & strStatusValue & "%' "
strSql = strSql & " AND Rpt_Recipient.StdInd LIKE '" & strStandardValue & "%' "
strSql = strSql & " AND Rpt_Recipient.RecipientID = '" & ReplaceQuote(strRecID) & "'"
End If
'*
strConnection = AppSettings(CType(Session("sCONNVAR"), String))
'*
tRC = ExecuteDataset(strConnection, CommandType.Text, strSql).Tables(0)
'*
hdRecipientID.Value = dtRC.Rows(0).Item("RecipientID")
Session("RPTRCIS_dtRC") = dtRC
'*
blnDataFound = False
If dtRC.Rows.Count > 0 Then
blnDataFound = True
dgRC.DataSource = dtRC
'* Bind data here when Origin is the
'* 1) Inquiry Request or the Single Inquiry
'* 2) Update page to get the new dg.PageCount then DataBind again, see "Note" below dgRC.DataBind()
If hdOrigin.Value = "RPTRCU" Or hdOrigin.Value = "RPTRCIDG" Or hdOrigin.Value = "RPTRCUDG" Then
If Session("RPTRCIS_LastPage") = True Then
'* When the only record on the last page is deleted or no longer within original request
If CInt(Session("RPTRCIS_strPageCount_BeforeUpdate")) > dgRC.PageCount Then
'* Go back to prev page but one dgRC.CurrentPageIndex = CInt(Session("RPTRCIS_strPageNo")) - 1 Else
'* Go back to prev page
dgRC.CurrentPageIndex = CInt(Session("RPTRCIS_strPageNo"))
End If
Else
'* When not the last page
'* Go back to same page
dgRC.CurrentPageIndex = CInt(Session("RPTRCIS_strPageNo"))
End If
'* Note: Adjusting the page number only affects the page index being displayed,
'* need to rebind to show the correct data for the page index adjusted above
dgRC.DataBind()
End If
Else
blnDataFound = False
Exit Sub
End If
'*
'*
dtRC = Nothing
'*
End Sub
Private Sub Restore_RC_Data_BindDG()
dgRC.DataSource = Session("RPTRCIS_dtRC")
dgRC.DataBind()
End Sub
#End Region
posted by Kerstin Joy Walling | 1/25/2007 05:39:00 PM
1 Comments:
Busted! hehehe Well I'll come out in the open and tell you straight away that I'm one of your regular readers. =P
Anyway, I wish I could help you... but... sad to say... I only know cobol. hehehe But I sympathize with you girl... Been there done that. I know the pains and gains of being a programmer. :)
Good luck on your program! You'll find the problem soon. ;)
- Gennievi
Post a Comment
<< Home