In effort to retire the use of Microsoft Access, in large part due to the increasing error, and drift occurring in data quality and data process, as well as the limited flexibility with MS Access in general, rewriting the entire application into a responsive web application was embarked on.
Building on the success of the student portal in IIS with VBS/ASP, I imported the Access Database into SQL Server, and hand wrote the responsive UI that empowered data-entry in any browser, and the project coordinator to gain real-time insights from their mobile device wherever they were. Additionally, this enabled the opportunity to expose the analysis reports, in real-time to the district partners participating in the process.
admissions.asp
<%@ LANGUAGE=VBSCRIPT %>
<% OPTION EXPLICIT %>
<% 'ON ERROR RESUME NEXT
If LEN(Session("sAMAccountName")) = 0 Then
Response.Redirect "/my/?path=/my/Admissions/applications.asp"
End If
Dim MyConn, SQL_Query, RS, RS2, stringList, strRowNum, strStyle, strendStyle, item, strAppRcvd, strDWT, strOH, strOHL, strPageHeading, strPageHeader
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.open "PROVIDER=SQLNCLI11;Data Source=xxxxxxx;Initial Catalog=Admissions;User ID=xxxxxxxx;Password=xxxxxxxxxx "
strRowNum = 0
' Build Lab List
SQL_Query = "select cte.code from cteprogram as cte where cte.active = 1"+_
" and cte.statecode > 0"+_
" and cte.code is NOT NULL"+_
" order by cte.code asc"
Set RS2 = MyConn.Execute(SQL_Query)
While Not RS2.EOF
stringList = stringList & "<option value='" & RS2("code") & "'>" & RS2("code") & "</option>" & vbCrLf
RS2.MoveNext
Wend
RS2.Close
Set RS2=Nothing
%>
<!-- #include virtual="/header.inc" -->
<div class="w3-container w3-padding-small">
<%
Response.Write "<a href='/my/Admissions/'>Admissions</a> > <a href='applications.asp'>Applications</a><br>" & vbCrLf
If NOT Session("isInstructor") = 1 and NOT isNULL(Session("PRG")) = 1 AND Request.QueryString("test") <> 2 Then
If Request.QueryString("hsName") = "" AND Request.QueryString("prgCode") = "" AND Request.QueryString("all") = "" Then
' Build Home School List
SQL_Query = "select distinct [Home School] as hsname from [2024] where [Home School] is not NULL"+_
" order by hsName asc"
Set RS2 = MyConn.Execute(SQL_Query)
Response.Write "<br><br><b><u>Links</u></b><br><br>" & vbCrLf
Response.Write "<a href='studentApp.pdf'>Printable Student Application</a>" & vbCrLf
Response.Write "<br><br>"
Response.Write "<h4><a href='search.asp'><u>New / Student / Search</a></u></h4>"
Response.Write "<br>"
Response.Write "<a href='applications.asp?all=1'><h4>All Students</h4></a>"
Response.Write "<br>"
Response.Write "<form class='w3-form' name='adm' method='GET' action=''>" & vbCrLf
Response.Write "<div class='w3-row'>" & vbCrLf
Response.Write "<div class='w3-col s12 m12 l12'><h4><u>Sending School Search</u></h4></div>" & vbCrLf
Response.Write "<div class='w3-col s11 m5 l3'><select class='form-control' name='hsName' value=''>" & vbCrLf
Response.Write "<option value=''></option>" & vbCrLf
While Not RS2.EOF
Response.Write "<option value='" & RS2("hsName") & "'>" & RS2("hsName") & "</option>" & vbCrLf
RS2.MoveNext
Wend
RS2.Close
Response.Write "</select></div>" & vbCrLf
Response.Write "<div class='w3-col s12 m1 l1'><button class='btn btn-default' onClick='form.adm.submit'>Submit</button></div>" & vbCrLf
Response.Write "</div></form>" & vbCrLf
Response.Write "<div>"
SQL_Query = "select distinct RTRIM(cprgCode) as cprgCode, RTRIM(cprgname) as cprgName from IC_CTEPrograms where cprgstatus = 'Active'"+_
" order by cprgCode asc"
Set RS2 = MyConn.Execute(SQL_Query)
Response.Write "<form class='w3-form' name='adm2' method='GET' action=''>" & vbCrLf
Response.Write "<div class='w3-row'>" & vbCrLf
Response.Write "<div class='w3-col s12 m12 l12'>" & vbCrLf
Response.Write "<h4><u>Program Search</u></h4></div>" & vbCrLf
Response.Write "<div class='w3-col s11 m5 l3'><select class='form-control' id=" & chr(34) & "prgCode" & chr(34) & " name='prgCode' value=''>" & vbCrLf
Response.Write "<option value=''></option>" & vbCrLf
While Not RS2.EOF
Response.Write "<option value='" & RS2("cPrgCode") & "'>" & RS2("cPrgName") & "</option>" & vbCrLf
RS2.MoveNext
Wend
RS2.Close
Set RS2=Nothing
Response.Write "</select></div>" & vbCrLf
Response.Write "<div class='w3-col s12 m3 l3'><button class='btn btn-default' onClick='form.adm2.submit'>Submit</button>" & vbCrLf
%>
<script>
function teacherView(){
var sel = document.getElementById("prgCode");
var opt = sel.value;
if (sel.selectedIndex > 0) {
var string = 'applications.asp?test=2&prg=' + opt;
window.location.href = string;
} else {
alert("You must select a Program to Search")
}
}
</script>
<%
Response.Write "<button type='button' class='btn btn-default' onClick=" & chr(34) & "teacherView();" & chr(34) & ">Teacher View</button>" & vbCrLf
Response.Write "</div>" & vbCrLf
Response.Write "</div></form>" & vbCrLf
Response.Write "<div>"
IF Session("sAMAccountName") = "jsmith3266" or Session("sAMAccountName") = "lsorensen" THEN
END IF
Else
IF Request.QueryString("hsName") <> "" OR Request.QueryString("all") = 1 Then
SQL_Query = "exec ADM_Application_Counts '" & Request.QueryString("hsName") & "'"
Set RS2 = MyConn.Execute(SQL_Query)
SQL_query = "SELECT distinct lastName, firstName, [Home School], [PASecureID], [AppLab], [AppRecieved], CONVERT(VARCHAR(10),[AppDate],126) as AppDate, CONVERT(VARCHAR(10),[StatusDate],126) as StatusDate, [Status], [Comments], [ADMTot], [2nd Favorite], PrimaryKey, [Dinner Attendees] as dinner, [Open House Attend] as oha, [Open House Lab] as ohl FROM [2024] "
If Request.QueryString("hsName") <> "" THEN
SQL_query = SQL_query & " where [Home School] = '" & Request.QueryString("hsName") & "' "
End If
SQL_query = SQL_query & " order by lastName asc"
Set RS = MyConn.Execute(SQL_query)
strPageHeading = "Sending School : "
strPageHeader = Request.QueryString("hsName")
ElseIf Request.QueryString("prgCode") <> "" Then
SQL_Query = "exec ADM_Application_Counts2 '" & Request.QueryString("prgCode") & "'"
Set RS2 = MyConn.Execute(SQL_Query)
SQL_query = "SELECT distinct lastName, firstName, [Home School], [PASecureID], [AppLab], [AppRecieved], CONVERT(VARCHAR(10),[AppDate],126) as AppDate, CONVERT(VARCHAR(10),[StatusDate],126) as StatusDate, [Status], [Comments], PrimaryKey, [ADMTot], [2nd Favorite], [Dinner Attendees] as dinner, [Open House Attend] as oha, [Open House Lab] as ohl FROM [2024] where [APPLAB] = '" & Request.QueryString("prgCode") & "' order by [Home School], [status], [ADMTot] desc"
Set RS = MyConn.Execute(SQL_query)
strPageHeading = "Program : "
strPageHeader = Request.QueryString("prgCode")
End If
Response.Write "<form name='visit' method='POST' action='updateApplications.asp'>"
Response.Write "<div class='w3-row'>" & vbCrLf
Response.Write "<div class='w3-col s12 m4 l4'>" & vbCrLf
Response.Write "<h2 style='font-size: 16pt'><b>" & strPageHeading & "</b><a href='?'><font style='font-size: 10pt;'>(change)</font></a><h3 style='font-size: 12pt'>" & strPageHeader & vbCrLf & "</h3></h2></div>" & vbCrLf
Response.Write "<div class='w3-col s12 m4 l4'></div>" & vbCrLf
Response.Write "<div class='w3-col s12 m4 l4'>" & vbCrLf
Response.Write "</div></div>" & vbCrLf
Response.Write "<div class='table-responsive'>" & vbCrLf
Response.Write "<table class='w3-table-all'><tr>" & vbCrLf
IF NOT RS2.EOF Then
for each item in RS2.Fields
Response.Write "<th><b>" & item.Name & "</b></th>" & vbCrLf
Next
Response.Write "</tr>" & vbCrLf
While Not RS2.EOF
IF RS2("Status") = "ADMIT" OR RS2("Status") = "Return" OR RS2("Status") = "Quota" THEN
strStyle = "<b>"
strendStyle = "</b>"
ELSE
strStyle = "<i>"
strendStyle = "</i>"
END IF
Response.Write "<tr>" & vbCrLf
for each item in RS2.Fields
If IsNull(RS2(item.Name)) Then
Response.Write "<td>" & strStyle & "0" & strendStyle & "</td>" & vbCrLf
Else
Response.Write "<td>" & strStyle & RS2(item.Name) & strendStyle & "</td>" & vbCrLf
End If
Next
Response.Write "</tr>" & vbCrLf
RS2.MoveNext
Wend
End If
Response.Write "</table></div><br><br>" & vbCrLf
RS2.Close
Set RS2=Nothing
Response.Write "<div class='w3-row'>" & vbCrLf
Response.Write "<div class='w3-col s12 m12 l2'><label>Last, First Name</label><font style='font-size: 10pt;'> (click name to enter application)</font></div>" & vbCrLf
IF Request.QueryString("prgCode") <> "" Then
Response.Write "<div class='w3-col s4 m4 l1 w3-center'><label>District</label></div>" & vbCrLf
Else
Response.Write "<div class='w3-col s4 m4 l1 w3-center'><label>AppLab</label></div>" & vbCrLf
End If
Response.Write "<div class='w3-col s4 m4 l2 w3-center'><label>Date</label></div>" & vbCrLf
IF Request.QueryString("prgCode") <> "" Then
Response.Write "<div class='w3-col s1 m1 l1 w3-center'><label>Score</label></div>" & vbCrLf
Else
Response.Write "<div class='w3-col s1 m1 l1 w3-center'><label>Received</label></div>" & vbCrLf
End If
Response.Write "<div class='w3-col s1 m1 l1 w3-center'><label>Dinner</label></div>" & vbCrLf
Response.Write "<div class='w3-col s1 m1 l1 w3-center'><label>Open House</label></div>" & vbCrLf
Response.Write "<div class='w3-col s4 m4 l1 w3-center'><label>Status</label></div>" & vbCrLf
Response.Write "<div class='w3-col s4 m4 l1 w3-center'><label>Date</label></div>" & vbCrLf
Response.Write "<div class='w3-col s12 m12 l2'><label>Comments</label></div>" & vbCrLf
Response.Write "</div>" & vbCrLf
WHILE NOT RS.EOF
strRowNum = strRowNum + 1
Response.Write "<input type='hidden' value='" & strRowNum & "' name='rowNum' >" & vbCrLf
Response.Write "<input type='hidden' value='" & RS("PrimaryKey") & "' name='PK-" & strRowNum & "'>" & vbCrLf
Response.Write "<input type='hidden' value='" & RS("PASecureID") & "' name='PAID-" & strRowNum & "'>" & vbCrLf
Response.Write "<div class='w3-row w3-padding-small'>" & vbCrLf
Response.Write "<a id='" & RS("PrimaryKey") & "'>" & VbCrLf
Response.Write "<a onClick=""location.href='applicationform.asp?record=" & RS("PrimaryKey") & "'"" style='cursor: pointer;'>" & vbCrLf
Response.Write "<div style='background-color: #eeeeee;' title='Click to Create Application for " & RS("lastName") & ", " & RS("firstName") & " : Record # " & RS("PrimaryKey") & "' class='w3-col s12 m12 l2 form-control'>" & vbCrLf
Response.Write "<span class='w3-text' >" & RS("lastName") & " , " & RS("firstName") & "</span></div></a></a>" & vbCrLf
Response.Write "<div class='w3-col s4 m4 l1'>" & vbCrLf
IF Request.QueryString("prgCode") <> "" Then
Response.Write "<input class='form-control' type='text' name='HomeSchool-" & strRowNum & "' value='" & RS("Home School") & "' disabled>" & vbCrLf
Else
Response.Write "<input class='form-control' type='text' name='APPLAB-" & strRowNum & "' value='" & RS("APPLAB") & "' disabled>" & vbCrLf
End If
Response.Write "</div>" & vbCrLf
Response.Write "<div class='w3-col s4 m4 l2'>" & vbCrLf
Response.Write "<input class='form-control' type='date' name='AppDate-" & strRowNum & "' value='" & RS("AppDate") & "' placeholder='' disabled>" & vbCrLf
Response.Write "</div>" & vbCrLf
IF RS("AppRecieved") = "True" Then
strAppRcvd = " checked='checked' "
else
strAppRcvd = ""
End If
Response.Write "<div class='w3-col s1 m1 l1 w3-center'>" & vbCrLf
IF Request.QueryString("prgCode") <> "" Then
Response.Write "<input class='form-control' type='text' name='ADMTot-" & strRowNum & "' value='" & RS("ADMTot") & "' disabled>" & vbCrLf
Else
Response.Write "<input class='w3-checkbox form-control' type='checkbox' name='AppRecieved-" & strRowNum & "' " & strAppRcvd & " disabled>" & vbCrLf
End If
Response.Write "</div>" & vbCrLf
IF RS("dinner") = 1 Then
strDWT = " checked='checked' "
else
strDWT = ""
End If
Response.Write "<div class='w3-col s1 m1 l1 w3-center'>" & vbCrLf
Response.Write "<input class='w3-checkbox form-control' type='checkbox' name='DWT-" & strRowNum & "' " & strDWT & " >" & vbCrLf
Response.Write "</div>" & vbCrLf
IF RS("oha") = "True" Then
strOH = " checked='checked' "
else
strOH = ""
End If
Response.Write "<div class='w3-col s1 m1 l1 w3-center'>" & vbCrLf
Response.Write "<input class='w3-checkbox form-control' type='checkbox' name='OH-" & strRowNum & "' " & strOH & " >" & vbCrLf
Response.Write "</div>" & vbCrLf
Response.Write "<div class='w3-col s4 m4 l1'>" & vbCrLf
Response.Write "<select class='form-control' name='AppStatus-" & strRowNum & "'><option value='" & RS("Status") & "' selected>" & RS("Status") & "</option>" & vbCrLf
Response.Write "<option value='ADMIT'>ADMIT</option>"
Response.Write "<option value='ADMW'>ADMW</option>"
Response.Write "<option value='ADMC'>ADMC</option>"
Response.Write "<option value='DECL'>DECL</option>"
Response.Write "</select></div>" & vbCrLf
Response.Write "<div class='w3-col s4 m4 l1'>" & vbCrLf
Response.Write "<input class='form-control' type='date' name='StatusDate-" & strRowNum & "' value='" & RS("StatusDate") & "'>" & vbCrLf
Response.Write "</div>" & vbCrLf
IF RS("Status") = "ADMC" OR RS("Status") = "ADMW" OR RS("Status") = "DECL" THEN
Response.Write "<div class='w3-col s12 m12 l2'><input class='form-control' type='text' name='Comments-" & strRowNum & "' value='" & RS("Comments") & " 2nd:" & RS("2nd Favorite") & "'></div>" & vbCrLf
ELSE
Response.Write "<div class='w3-col s12 m12 l2'><input class='form-control' type='text' name='Comments-" & strRowNum & "' value='" & RS("Comments") & "'></div>" & vbCrLf
END IF
Response.Write "</div>" & vbCrLf
RS.MoveNext
Wend
Response.Write "<button class='w3-btn w3-btn-alert' onClick='form.visit.Submit()' >Save</button>"
End If
Else ' Program Faculty
Dim strPrgFilter
If (NOT isNULL(Session("PRG"))) OR (Request.QueryString("test") = 2 AND Request.QueryString("prg") <> "") Then
IF Request.QueryString("test") = 2 AND NOT isEmpty(Request.QueryString("prg")) = True THEN
strPrgFilter = Request.QueryString("prg")
ELSE
strPrgFilter = Session("PRG")
END IF
SQL_Query = "Select hsName as [Home School], prgCode as [Program], ADMIT, ADMW, ADMC, DECL from Admissions.dbo.ADM_ApplicationsByPrg where prgCode = '" & strPrgFilter & "'"
Set RS2 = MyConn.Execute(SQL_Query)
Response.Write "<div class='table-responsive'>" & vbCrLf
Response.Write "<h2 style='font-size: 10pt'>" & strPrgFilter & "'s Visitation Counts</h2>" & vbCrLf
Response.Write "<p>" & Session("cn") & ",<br> The following information is derived "+_
" directly from the Admissions Database system, and should give you an overview of the visitations planned for your Program." & vbCrLf
Response.Write "<table class='table'><thead>" & vbCrLf
for each item in RS2.Fields
Response.Write "<th><b>" & item.Name & "</b></th>" & vbCrLf
Next
Response.Write "</thead>" & vbCrLf
While Not RS2.EOF
Response.Write "<tbody>" & vbCrLf
for each item in RS2.Fields
If IsNull(RS2(item.Name)) Then
Response.Write "<td>0</td>" & vbCrLf
Else
Response.Write "<td>" & RS2(item.Name) & "</td>" & vbCrLf
End If
Next
Response.Write "</tbody>" & vbCrLf
RS2.MoveNext
Wend
Response.Write "</table></div><br><br>" & vbCrLf
SQL_Query = "Select * from ADM_Applications where APPLab = '" & strPrgFilter & "'"
Set RS2 = MyConn.Execute(SQL_Query)
Response.Write "<div class='table-responsive'>" & vbCrLf
Response.Write "<h2 style='font-size: 10pt'>" & strPrgFilter & "'s Application List</h2>" & vbCrLf
Response.Write "<table class='table'><thead>" & vbCrLf
for each item in RS2.Fields
Response.Write "<th><b>" & item.Name & "</b></th>" & vbCrLf
Next
Response.Write "</thead>" & vbCrLf
While Not RS2.EOF
Response.Write "<tbody>" & vbCrLf
for each item in RS2.Fields
If IsNull(RS2(item.Name)) Then
Response.Write "<td>0</td>" & vbCrLf
Else
Response.Write "<td>" & RS2(item.Name) & "</td>" & vbCrLf
End If
Next
Response.Write "</tbody>" & vbCrLf
RS2.MoveNext
Wend
Response.Write "</table></div><br><br>" & vbCrLf
RS2.Close
Set RS2=Nothing
End If
End If
%>
</div>
<!-- #include virtual="/footer.inc" -->
</BODY>
</HTML>