Programming in Visual Studio issue

We have an Intranet site that a Programmer created and there is a section that brings up Expiring Permits based upon an Expiration Date. The issue we are having is that the Expiring Permits outputted starts for the year 2014 and not 2015. It goes until 2019 and I cannot find where the setting(s) is that displays the years option.

I have included a screenshot of the output and also the coding of the page where I believe the settings are. I have tried contacting the programming consultant but to no avail. Any assistance offered would be appreciated:

Output starting with 2014
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="PermitStatus.aspx.vb" Inherits="DWMANet.PermitStatus" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
	<HEAD>
		<title>Permit Status</title>
		<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
		<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
		<meta name="vs_defaultClientScript" content="VBScript">
		<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
		<LINK href="../../Styles.css" type="text/css" rel="stylesheet">
		<script language="javascript" src="../ClientFunctions.js"></script>
	</HEAD>
	<body MS_POSITIONING="FlowLayout" leftmargin="0" topmargin="0">
		<form id="Form1" method="post" runat="server">
			<TABLE align="center" id="tblMain" width="830" border="0" bgColor="white">	
				<tr>
					<td colspan=6 bgcolor="#0099ff" align=center><font size=4 color="white"><b>Permit Status</b></font></td>
				</tr>				
				<%sThisMonth = getMonthName(CurDate.Month)
				sDispMonth = ""
				For Each dRow IN DTSearch.Rows
					If Not IsDBNull(dRow("Expires")) Then
						ExpDate = CType(dRow("Expires"), Date)
						sRecMonth = getMonthName(ExpDate.Month)
						If sRecMonth <> sDispMonth Then
							sDispMonth = sRecMonth%>
							<tr>
								<td colspan=6><hr></td>
							</tr>
							<tr>
								<td align=center colspan=6>									
									<%If sDispMonth = sThisMonth Then%>
										<b><font color="red">EXPIRING THIS MONTH (</font><font size=4 color="black"><%= sDispMonth%></font><font size=4 color="red">)</font></b>
									<%Else%>
										<b><font color="black">EXPIRING IN <%= sDispMonth%></font></b>
									<%End If%>								
								</td>								
							</tr>
							<tr>
								<td colspan=5>&nbsp;</td>
							</tr>
						<%End If
					End If%>
					<tr>
						<td valign=top>•&nbsp;</td>
						<td>
							<%If Not dRow("FullCert") Is DBNull.Value Then%>
								<a href="Permits.aspx?ID=<%= dRow("ID").ToString()%>"><img src="../images/Certified.gif" border="0"></a>
							<%End If%>
							<%If dRow("ShowAnyway").ToString() = "True" Then%>
								<a href="Permits.aspx?ID=<%= dRow("ID").ToString()%>"><img src="../images/Checkmark.jpg" border="0"></a>
							<%End If%>	
						</td>
						<td>
							<font color="blue"><a href="Permits.aspx?ID=<%= dRow("ID").ToString()%>">
							<%If Not IsDBNull(dRow("Expires")) Then%>							
									<%= ExpDate.toString("d")%>&nbsp;
							<%Else%>
								N/A&nbsp;
							<%End If%>									
							</font></a>						
						</td>
						<td>
							<font color="blue"><a href="Permits.aspx?ID=<%= dRow("ID").ToString()%>"><font color="blue">(<%= dRow("PM").toString()%>)&nbsp;</font></a>
						</td>
						<td>
							<a href="Permits.aspx?ID=<%= dRow("ID").ToString()%>">
							<%If Not IsDBNull(dRow("ItemTypeID")) Then%>							
								<%= dRow("PItemType")%>&nbsp;
							<%Else%>
								<%= dRow("PermitType")%>&nbsp;
							<%End If%>	
							</a>							
						</td>
						<td>
							<a href="Permits.aspx?ID=<%= dRow("ID").ToString()%>">													
								<%= dRow("Project")%>&nbsp;						
							</a>							
						</td>
					</tr>
				<%Next%>
			</TABLE>
		</form>
	</body>
</HTML>

Open in new window

regsampAsked:
Who is Participating?
 
Fernando SotoRetiredCommented:
Well that may be but according to the code they too are suppose to be returned. Now the only way this maybe happening now is that ShowAnyway was set to another value and for some reason they have been updated since. Or the code has been changed since then to include them.
0
 
Fernando SotoRetiredCommented:
Hi regsamp;

It looks like this is the code the fills the page with the value. The values seem to be coming from a DataTable object that most likely get fill from a database. Look at the code behind.

<%If Not IsDBNull(dRow("Expires")) Then%>							
    <%= ExpDate.toString("d")%>&nbsp;
<%Else%>
    N/A&nbsp;
<%End If%>

Open in new window

0
 
regsampAuthor Commented:
Hi Fernando;

Yes, the data is stored in a SQL 2008 database. I have a little experience with programming and it has been awhile so I apologize. I have tried looking around for the right DataTable object and in the database but I can't seem to locate it.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Fernando SotoRetiredCommented:
Can you post the code behind for the page?
0
 
regsampAuthor Commented:
I hope this is right:

Imports System.Data
Imports System.Data.SqlClient

Public Class PermitStatus
    Inherits System.Web.UI.Page
    Protected WithEvents dgrdClients As System.Web.UI.WebControls.DataGrid
    Protected SDASearch As SqlDataAdapter
    Protected DSSearch As DataSet
    Protected DTSearch As DataTable
    Protected SQLConn As SqlConnection
    Protected dRow As DataRow
    Protected PEdit As Boolean
    Protected PDelete As Boolean
    Protected ExpDate As DateTime
    Protected CurDate As DateTime
    Protected i As Int32
    Protected sStartRow As String
    Protected StartRow As Int32
    Protected EndRow As Int32
    Protected sDispMonth As String
    Protected sRecMonth As String
    Protected sThisMonth As String
    Protected PMCode As String
    Protected PMA_ID As String
    Private DWMA As New DWMAGlobals()
    Private Usuario As New DWMAUser()
#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SQLConn = New SqlConnection(DWMA.ConnectionString)

        If Not IsPostBack Then
            Usuario.PullUserInfo(Page.User.Identity.Name)
            If Usuario.AccessViewPermits Or Usuario.AccessGod Then
                PMCode = Usuario.PMCode
                If Usuario.AccessEditPermits Or Usuario.AccessGod Then
                    PEdit = True
                End If
                If Usuario.AccessDeletePermits Or Usuario.AccessGod Then
                    PDelete = True
                End If

                BindDataGrid()
            Else
                Response.Redirect("../../Error.aspx?page=/Accounting/Clients/Clients.aspx&type=access")
            End If
        Else
            DTSearch = CType(Session("DTSearch"), DataTable)
        End If
        CurDate = New Date()
        If Request.Form("StartRow") <> "" Then
            sStartRow = Request.Form("StartRow")
            StartRow = CInt(sStartRow)
        Else
            StartRow = 0
        End If
    End Sub

    Private Sub BindDataGrid()
        Dim strSQL As String
        Dim sDate As String
        Dim sFutDate As String

        sDate = Date.Today.ToString("d")
        sFutDate = Date.Today.AddMonths(5).ToString("d")

        strSQL = "Select PermitStatus.ID, PermitStatus.Active, PermitStatus.ShowAnyway, PermitStatus.ItemTypeID, PermitItemTypesMstr.Name As PItemType, PermitStatus.PermitType, PermitStatus.Agency, PermitStatus.Project, PermitStatus.PM, Projects.PSDesc, PermitStatus.ProjectDesc,"
        strSQL &= " PermitStatus.PermitNum, PermitStatus.Expires, PermitStatus.FullCert, PermitStatus.Certified, PermitStatus.Revised"
        strSQL &= " From ((PermitStatus INNER JOIN Projects ON PermitStatus.ProjectID = Projects.ProjectsID) LEFT JOIN PermitItemTypesMstr ON PermitStatus.ItemTypeID = PermitItemTypesMstr.ID)"
        strSQL &= " Where PermitStatus.Expires Is Not Null AND ((PermitStatus.Expires >= '" & sDate & "' AND PermitStatus.Expires <= '" & sFutDate & "') OR PermitStatus.ShowAnyway = 1)"
        If PMCode <> "" Then
            strSQL &= " AND (PermitStatus.PM = '" & Usuario.PMCode & "' OR PermitStatus.PM_ID = " & Usuario.RecordNo & " OR PermitStatus.PM IN (Select PMCode From Users Where PMA_ID = " & Usuario.RecordNo & ") OR PermitStatus.PM_ID IN (Select RecordNo From Users Where PMA_ID = " & Usuario.RecordNo & "))"
        End If
        strSQL &= " ORDER BY PermitStatus.Expires"

        Try
            'Response.Write(strSQL)
            SDASearch = New SqlDataAdapter(strSQL, SQLConn)
            DSSearch = New DataSet()
            DTSearch = New DataTable()
            SDASearch.Fill(DSSearch)
            DTSearch = DSSearch.Tables(0)
            Session("DTSearch") = DTSearch
            strSQL = ""

            'If dgrdClients.Items.Count < 1 Then
            'Response.Redirect("../../Error.aspx?page=/Accounting/Clients/Clients.aspx&problem=No+records+found")
            'End If
        Catch e As SqlException
            'Response.Redirect("../../Error.aspx?page=/Accounting/Clients/Clients.aspx?problem=" & e.Message)
        End Try
    End Sub

    Function getMonthName(ByVal intMonth As Int16) As String
        Select Case intMonth
            Case 1
                getMonthName = "January"
            Case 2
                getMonthName = "February"
            Case 3
                getMonthName = "March"
            Case 4
                getMonthName = "April"
            Case 5
                getMonthName = "May"
            Case 6
                getMonthName = "June"
            Case 7
                getMonthName = "July"
            Case 8
                getMonthName = "August"
            Case 9
                getMonthName = "September"
            Case 10
                getMonthName = "October"
            Case 11
                getMonthName = "November"
            Case 12
                getMonthName = "December"
        End Select
    End Function
End Class

Open in new window

0
 
Fernando SotoRetiredCommented:
Hi regsamp;

Well if the info posted is correct, On the SQL server the table name is PermitStatus the column name Expires this table is joined with table Projects. Check to see if the information in the database is correct.
0
 
regsampAuthor Commented:
I do see the table PermitStatus and there is the column name Expires which I can see dates in there that look correct. I can run a query on it and it the results always seem to come back correctly. I can search for Expires before 2014 and well after 2019 but on the output it starts with 2014. Am I check the wrong information or should I be looking at something else?
0
 
David Johnson, CD, MVPOwnerCommented:
you need to look for the variable 'd' .. There is a problem with the code as something that expires before today's date is not 'expiring' but 'expired' .. It looks like the code is supposed to look for items expiring starting from today's date and for the next 5 months but it obviously isn't doing it.
0
 
Fernando SotoRetiredCommented:
The range of the date is made by where PermitStatus.Expire is between sDate and sFutDate inclusive
or PermitStatus.ShowAnyway has a value of 1.

The sDate and sFutDate are set by this near the top of the code

sDate = Date.Today.ToString("d")
sFutDate = Date.Today.AddMonths(5).ToString("d")

So between the date that the query is made and 5 months in the future.

I would take the complete SQL query that is in the code behind in variable strSQL and run it on the server hard coding the two dates and see if it returns the same results as running the web app.
0
 
regsampAuthor Commented:
"It looks like the code is supposed to look for items expiring starting from today's date and for the next 5 months but it obviously isn't doing it." Right David. The output use to be exactly that and we have not changed the coding but the output is incorrect now.

"I would take the complete SQL query that is in the code behind in variable strSQL and run it on the server hard coding the two dates and see if it returns the same results as running the web app." Can you copy and paste an example that I can run in management studio and update the results?
0
 
Fernando SotoRetiredCommented:
Hi regsamp;

In your last post you stated this, "It looks like the code is supposed to look for items expiring starting from today's date and for the next 5 months but it obviously isn't doing it.", but that is not the whole thing. The date do not have to be within that range if the field in the database PermitStatus.ShowAnyway = 1 then that record will also be displayed and could be any date. What is the meaning of PermitStatus.ShowAnyway = 1, for example if a value of 1 in that field means has already expired.
0
 
regsampAuthor Commented:
Fernando,

I was going off of David's post above. The code use to output the Permits five months in advance. I don't know how long it has "not" been doing that. I just discovered it today. I am not sure what the "PermitStatus.ShowAnyway = 1," means as the programming consultant wrote that and they have not gotten back to me yet.
0
 
David Johnson, CD, MVPOwnerCommented:
"PermitStatus.ShowAnyway = 1," That would imply that it will show all records
0
 
regsampAuthor Commented:
"PermitStatus.ShowAnyway = 1," That would imply that it will show all records" But how does it determine what it shows as in the Expires column in PermitStatus table we have dates going up to 2025 but the dates show 2014 to 2019?
0
 
Fernando SotoRetiredCommented:
Have you looked in the database to see if the column PermitStatus.ShowAnyway is set to 1 for any of the records? If they have some do they match the records that are being displayed that are not in the range that should be there?
0
 
regsampAuthor Commented:
"Have you looked in the database to see if the column PermitStatus.ShowAnyway is set to 1 for any of the records?" I have and and some are set to 1. The ones I have checked "do" match the records that are being displayed and according to the coded range, they should not be there right? As this is suppose to be five months only.
0
 
regsampAuthor Commented:
I see what you are saying Fernando. The very first record on the output image I posted is from 2014 and in the ShowAnyway column in PermitStatus for this record, it is set to '1' so this will keep showing up until that ShowAnyway value is not '1'. That is why it is ignoring the 5 month rule.
0
 
regsampAuthor Commented:
The consultant did do some changes in this area maybe five months ago so the code must have been changed to include them. I am not sure of the reason why but I will have to get in-touch with them and the supervisor who would have requested it is out for a few days. Okay, thank you very much everyone for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.