The following is the ASP page that was created. What am I trying to
accomplish is to make the pull down menu "Approve/Deny" update the
"Status" column that is displayed to the browser once the submit
"Update" button is selected at the bottom of the page. The "Status"
column will update to whatever is selected from the pull down menu,
"Approve, "Deny" or "Pending." Additionally, the "Pending" status is
the default value in the "Status" column only until it is updated when
the submit "Update" button is selected.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="includes/functions.inc.asp"-->
<%
ConnectToDatabase("") ' Connect to the database
Dim days ' Our variable to hold the # of days to display
'If nothing is submitted from the user then set it to 60.
If Request("Days") = "" Then
days = 60
Else
days = CInt(Request("Days"))
End If
Dim approve, strMessage, strDelivery, strEmail, key
strMessage = Request("Message") ' Store our display message
bLogin = false ' Boolean variable to tell whether the user is logged in or not
approve = false ' Boolean which grants permission to approve/deny requests
' If they want to logout clear their cookies
If Request("Logout") = 1 Then
Response.Cookies("LoginType") = ""
Response.Cookies("Unit") = ""
End If
' If a password has been submitted attempt to log the user in
If Request("Password") <> "" Then
' Select the unit they specified and check the password against it
Query "SELECT * FROM Units WHERE ID = " & Request("Unit") & " AND
ApprovalPassword = '" & Request("Password") & "'", 0
' If it validates then setup our login variables and store the
authentication cookies
If NOT dbResult(0).EOF Then
approve = true
bLogin = true
Response.Cookies("LoginType") = "approve"
Response.Cookies("Unit") = dbResult(0)("ID")
End If
CloseQuery(0)
' If we're still not logged in check them against the "view-only" password.
If NOT bLogin Then
Query "SELECT * FROM Units WHERE ID = " & Request("Unit") & " AND
ViewPassword = '" & Request("Password") & "'", 0
' Setup their authentication but don't include the approve = true
If NOT dbResult(0).EOF Then
bLogin = true
Response.Cookies("LoginType") = "view"
Response.Cookies("Unit") = dbResult(0)("ID")
End If
CloseQuery(0)
End If
' If they're still not validated it's a bad password so let them know!
If NOT bLogin Then
strMessage = "The password you entered is invalid, please try again."
End If
' Redirect with the appropriate message
Response.Redirect("viewall1.asp?Message=" & strMessage)
End If
' Setup our authentication variables based on the cookies we stored above
If Request.Cookies("LoginType") <> "" Then
bLogin = true
If Request.Cookies("LoginType") = "approve" Then
approve = true
End If
End If
If Request("Update") <> "" AND approve Then
dbResult(0).CursorType=2
dbResult(0).LockType=3
Query "SELECT * FROM Requests WHERE EndDate >= #" & Now() - days &
"# AND Unit = " & Request("Unit") & " ORDER BY StartDate DESC, EndDate
DESC, LastName DESC", 0
Do While NOT dbResult(0).EOF
If Request("RECORD_" & dbResult(0)("Key")) <> "" Then
' If the status has changed from whats in the database then update it
If Request("RECORD_" & dbResult(0)("Key")) <> dbResult(0)("Status") Then
dbResult(0)("Status") = Request("RECORD_" & dbResult(0)("Key"))
dbResult(0)("DateApproved") = Now()
dbResult(0).Update
Dim objCDOMail 'Holds the CDONTS NewMail Object
Set objCDOMail = Server.CreateObject("CDONTS.NewMail")
strEmail = "Sir/Ma'am, your travel request is now " &
dbResult(0)("Status") & ". Please <a
href='http://source/applications/travel/view.asp?ID=" &
dbResult(0)("ID") & "'>click here</a> to view the request."
objCDOMail.From = "IBM /Helpdesk <helpdesk@ibm.com>"
objCDOMail.To = dbResult(0)("Email")
objCDOMail.Subject = "Out of Town Travel Request - Status Update"
objCDOMail.BodyFormat = 0
objCDOMail.MailFormat = 0
objCDOMail.Body = strEmail
objCDOMail.Importance = 1
objCDOMail.Value("Reply-To") = "ibm <helpdesk@ibm.com>"
objCDOMail.Send
Set objCDOMail = Nothing
End If
End If
dbResult(0).MoveNext
Loop
CloseQuery(0)
strMessage = "The requests have been updated."
Response.Redirect("viewall1.asp?Message=" & strMessage)
End If
StartHTML("View All Requests")%>
<center>
<table border="0" cellpadding="1" cellspacing="1" class="content"
style="margin-left:5px">
<%
' If they aren't logged in display the below login form
If NOT bLogin Then %>
<form action="viewall1.asp" method="post">
<tr>
<td class="head_big" align="center" colspan="3"> 39 ABW
Travel Request </td>
</tr>
<tr>
<td class="text"> Unit: </td>
<td class="text"> <select name="Unit" size="1">
<%
Query "SELECT * FROM Units ORDER BY Name ASC", 0
Do While NOT dbResult(0).EOF
%>
<option value="<%=dbResult(0)("ID")%>"><%=dbResult(0)("Name")%></a></option>
<%
dbResult(0).MoveNext
Loop
CloseQuery(0)%>
</select> </td>
</tr>
<tr>
<td class="text"> Password: </td>
<td class="text"> <input type="password" name="password"
value=""> </td>
</tr>
<% If strMessage <> "" Then %>
<tr>
<td class="text" colspan="2"><%=strMessage%></td>
</tr>
<% End If%>
<tr>
<td class="head" align="center" colspan="2"><input type="submit"
name="submit" value="Submit"></td>
</tr>
</form>
<%
' End of the login form
Else
' Display below if they are logged in
%>
<% Query "SELECT * FROM Units WHERE ID = " & Request("Unit"), 1 %>
<form action="viewall1.asp" method="post">
<input type="hidden" name="Days" value="<%=days%>">
<input type="hidden" name="Unit" value="<%=Request("Unit")%>">
<tr>
<td class="head_big" align="center"
colspan="5"> <%=dbResult(1)("Name")%> Travel
Requests </td>
</tr>
<% CloseQuery(1) %>
<tr>
<td class="subHead"> User </td>
<td class="subHead"> Travel Dates </td>
<td class="subHead"> Date Requested </td>
<td class="subHead"> Status </td>
<% If approve Then %>
<td class="subHead"> Approve/Deny </td>
<% Else %>
<td class="subHead" width="0"> </td>
<% End If %>
</tr>
<%
' Select the requests that are within the number of days setup above.
Query "SELECT * FROM Requests WHERE EndDate >= #" & Now() - days & "#
AND Unit = " & Request("Unit") & " ORDER BY StartDate DESC, EndDate
DESC, LastName DESC", 0
' Loop through and display the requests
Do While NOT dbResult(0).EOF
' If the user has approval authority then display this
If approve Then
%>
<tr>
<td class="text" nowrap> <a
href="approve.asp?UID=<%=dbResult(0)("Key")%>"><%=dbResult(0)("LastName")%>,
<%=dbResult(0)("FirstName")%> <%=dbResult(0)("Rank")%></a> </td>
<td class="text" nowrap> <%=dbResult(0)("StartDate")%> -
<%=dbResult(0)("EndDate")%> </td>
<td class="text" nowrap> <%=dbResult(0)("DateSubmitted")%> </td>
<td class="text" nowrap> <%=PrintStatus(dbResult(0)("Status"))%> </td>
<td class="text" nowrap> <select
name="RECORD_<%=dbResult(0)("ID")%>" size="1">
<option value="Pending"<% If dbResult(0)("Status") = "Pending" Then
%> selected<% End If %>>Pending</option>
<option value="Approved"<% If dbResult(0)("Status") = "Approved"
Then %> selected<% End If %>>Approved</option>
<option value="Denied"<% If dbResult(0)("Status") = "Denied" Then
%> selected<% End If %>>Denied</option>
</select> </td>
</tr>
<%
' Otherwise display the view only mode
Else
%>
<tr>
<td class="text" nowrap> <a
href="view.asp?ID=<%=dbResult(0)("ID")%>"><%=dbResult(0)("LastName")%>,
<%=dbResult(0)("FirstName")%> <%=dbResult(0)("Rank")%></a> </td>
<td class="text" nowrap> <%=dbResult(0)("StartDate")%> -
<%=dbResult(0)("EndDate")%> </td>
<td class="text" nowrap> <%=dbResult(0)("DateSubmitted")%> </td>
<td class="text" nowrap> <%=PrintStatus(dbResult(0)("Status"))%> </td>
<td class="text" nowrap> <select
name="RECORD_<%=dbResult(0)("ID")%>" size="1">
<option value="Pending"<% If dbResult(0)("Status") = "Pending" Then
%> selected<% End If %>>Pending</option>
<option value="Approved"<% If dbResult(0)("Status") = "Approved"
Then %> selected<% End If %>>Approved</option>
<option value="Denied"<% If dbResult(0)("Status") = "Denied" Then
%> selected<% End If %>>Denied</option>
</select
></tr>
<%
End If
dbResult(0).MoveNext
Loop
CloseQuery(0)
If approve Then
%>
<tr>
<td class="text" colspan="4"> </td>
<td class="head" align="center"><input type="submit" name="Update"
value="Update"></td>
</tr>
</form>
<form action="viewall1.asp" method="get" name="switch">
<tr>
<td class="subHead" align="center" colspan="5">View From Last:
<select name="Days" size="1"
onChange="javascript:document.forms['switch'].submit();">
<option value="14"></option>
<option value="14"<% If Request("Days") = 14 Then %> selected<% End
If%>>2 Weeks</option>
<option value="30"<% If Request("Days") = 30 Then %> selected<% End
If%>>30 Days</option>
<option value="60"<% If Request("Days") = 60 Then %> selected<% End
If%>>60 Days</option>
<option value="90"<% If Request("Days") = 90 Then %> selected<% End
If%>>90 Days</option>
<option value="180"<% If Request("Days") = 180 Then %> selected<%
End If%>>Six Months</option>
<option value="365"<% If Request("Days") = 365 Then %> selected<%
End If%>>Year</option>
</select></td>
</tr>
</form>
<% End If %>
<% End If %>
</table>
</center>
<%EndHTML()%> |
