Coding the First page - Page 3
July 9, 2001
<%@LANGUAGE="VBSCRIPT"%>
<%
' COMMENT. We start with the main recordset., KH07Top. It's
' collecting data from five columns in the table
' epscons_KH07_T, including DoH. Most of it was created using
' UltraDev, but there's also a bit of hand-coding. We want
' the medical specialty description (DoHCodeDesc) as well as
' the code, and the description is in another table called
' swanson_DoHDesc so that's been added as a second table in
' our SQL statement. In the WHERE part of the statement we marry
' the DoHDesc fields from the two tables. There's also a filter
' near the end to weed out any data entries that show zeros in
' all columns
set KH07Top = Server.CreateObject("ADODB.Recordset")
KH07Top.ActiveConnection = "dsn=swanson;"
KH07Top.Source =
"SELECT epscons_KH07_T.DoHCode, sum(M16W) as sumM16W,
sum(M16WO) as sumM16WO, sum(M17W) as sumM17W, sum(M17WO)
as sumM17WO, DoHCodeDesc FROM epscons_KH07_T,
swanson_DoHDesc WHERE epscons_KH07_T.DoHCode=
swanson_DoHDesc.DoHCode group by epscons_KH07_T.DoHCode,
DoHCodeDesc having sum(M16W+M16WO+M17W+M17WO) > 0 ORDER
BY epscons_KH07_T.DoHCode"
KH07Top.CursorType = 0
KH07Top.CursorLocation = 2
KH07Top.LockType = 3
KH07Top.Open
KH07Top_numRows = 0
'COMMENT. The last five lines are standard stuff that rarely
' varies. Now we create a similar recordset that will give us
' a totals row at the bottom of our data table. It's simpler
' than the one above.
set rswebtotals = Server.CreateObject("ADODB.Recordset")
rswebtotals.ActiveConnection = "dsn=swanson;"
rswebtotals.Source = "SELECT sum(M16W) as sumM16W, sum(M16WO)
as sumM16WO, sum(M17W) as sumM17W, sum(M17WO) as sumM17WO FROM
epscons_KH07_T"
rswebtotals.CursorType = 0
rswebtotals.CursorLocation = 2
rswebtotals.LockType = 3
rswebtotals.Open
rswebtotals_numRows = 0
%> <%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
KH07Top_numRows = KH07Top_numRows + Repeat1__numRows
' COMMENT. Those last few lines of code are added by UltraDev
' because the results we display will include multiple rows
' from the KH07Top recordset. Now follows a section of
' Macromedia subroutines that do clever things when we want to
' add information to a URL string. They include comments from
' Macromedia, all beginning with asterisks. Note that the
' Macromedia holds the copyright on these subroutines.
' *** Go To Record and Move To Record: create strings for
' maintaining URL and Form parameters
' *** create the list of parameters which should not
' be maintained
MM_removeList = "&index="
If (MM_paramName <> "") Then MM_removeList =
MM_removeList & "&" & MM_paramName & "="
MM_keepURL="":MM_keepForm="":MM_keepBoth="":MM_keepNone=""
' *** add the URL parameters to the MM_keepURL string
For Each Item In Request.QueryString
NextItem = "&" & Item & "="
If (InStr(1,MM_removeList,NextItem,1) = 0) Then
MM_keepURL = MM_keepURL & NextItem &
Server.URLencode(Request.QueryString(Item))
End If
Next
' *** add the Form variables to the MM_keepForm string
For Each Item In Request.Form
NextItem = "&" & Item & "="
If (InStr(1,MM_removeList,NextItem,1) = 0) Then
MM_keepForm = MM_keepForm & NextItem &
Server.URLencode(Request.Form(Item))
End If
Next
' *** create the Form + URL string and remove the intial
' '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm
if (MM_keepBoth <> "") Then MM_keepBoth =
Right(MM_keepBoth, Len(MM_keepBoth) - 1)
if (MM_keepURL <> "") Then MM_keepURL =
Right(MM_keepURL, Len(MM_keepURL) - 1)
if (MM_keepForm <> "") Then MM_keepForm =
Right(MM_keepForm, Len(MM_keepForm) - 1)
' *** a utility function used for adding additional
' parameters to these strings
Function MM_joinChar(firstItem)
If (firstItem <> "") Then
MM_joinChar = "&"
Else
MM_joinChar = ""
End If
End Function
%>
<html>
<!-- COMMENT. That's the initial VB over. From now on
it's mainly html, with just a few bits of VBScript -->
<head>
<title>Waiting List Indicators, by Specialty</title>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
<meta http-equiv="PRAGMA" content="NO-CACHE">
<!-- This page is for an intranet that has some caching
issues, even for ASP pages, so the cache-breaking meta tag
above has been included even though it's very unusual for
an ASP page -->
<style type="text/css">
<!--
td { font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 10pt}
-->
</style>
</head>
<body bgcolor="#FFFFFF" leftmargin="0" topmargin="0">
<table width="780" border="1" cellspacing="0"
cellpadding="2" align="center">
<tr bgcolor="#99CCFF">
<!-- COMMENT. Table heading section begins. Because of the
many rowspans and colspans, table cell widths are dealt with
later -->
<td rowspan="2" align="center"
bordercolor="#0063CE">DOH</td>
<td rowspan="2" align="center"
bordercolor="#0063CE">Specialty Description</td>
<td colspan="2" align="center"
bordercolor="#0063CE">Within target</td>
<td colspan="2" align="center" bordercolor="#0063CE">Out
of target</td>
</tr>
<tr bgcolor="#99CCFF">
<td bordercolor="#0063CE" align="center">Category 1</td>
<td bordercolor="#0063CE" align="center"> Category 2</td>
<td bordercolor="#0063CE" align="center"> Category 1</td>
<td bordercolor="#0063CE" align="center"> Category 2</td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT KH07Top.EOF))
%>
<!-- COMMENT: Above is the UltraDev code that gets a repeat
region underway, the row that follows will be repeated for each
DoH in the recordset, except those for which every column shows
a zero. -->
<tr bgcolor="#CCCCFF">
<td width="40" align="center" bordercolor="#CCCCFF">
<%=(KH07Top.Fields.Item("DoHCode").Value)%></td>
<td width="240" bordercolor="#CCCCFF">
<A HREF="indicators_level2.asp?<%= MM_keepNone &
MM_joinChar(MM_keepNone) & "DoH=" &
KH07Top.Fields.Item("DoHCode").Value%>">
<%=(KH07Top.Fields.Item("DoHCodeDesc").Value)%>
</A></td>
<!-- COMMENT. That's the cell with the most going on.
Clicking on it takes the user to a detail page, as
described earlier in this article. -->
<td width="60" align="right" bordercolor="#CCCCFF">
<%=(KH07Top.Fields.Item("sumM16W").Value)%>
</td>
<td width="60" align="right" bordercolor="#CCCCFF">
<font color="#800040">
<%=(KH07Top.Fields.Item("sumM16WO").Value)%>
</font> </td>
<td width="60" align="right" bordercolor="#CCCCFF">
<%=(KH07Top.Fields.Item("sumM17W").Value)%>
</td>
<td width="60" align="right" bordercolor="#CCCCFF">
<font color="#800040">
<%=(KH07Top.Fields.Item("sumM17WO").Value)%>
</font> </td>
</tr>
<!-- COMMENT. The row that follows is simply a spacer
for appearance -->
<tr bgcolor="#FFFFFF">
<td height="2"></td>
</tr>
<!-- COMMENT. Now UltraDev finishes the repeat region -->
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
KH07Top.MoveNext()
Wend
%>
<!-- COMMENT. Another spacer, separating the totals row
from the rows above. Then the totals -->
<tr bgcolor="#FFFFFF">
<td height="8"></td>
</tr>
<tr bgcolor="#CCCCFF">
<td bordercolor="#CCCCFF"> </td>
<td bordercolor="#CCCCFF">totals</td>
<td align="right" bordercolor="#CCCCFF">
<%=(rswebtotals.Fields.Item("sumM16W").Value)%>
</td>
<td align="right" bordercolor="#CCCCFF">
<font color="#800040">
<%=(rswebtotals.Fields.Item("sumM16WO").Value)%>
</font> </td>
<td align="right" bordercolor="#CCCCFF">
;<%=(rswebtotals.Fields.Item("sumM17W").Value)%>
</td>
<td align="right" bordercolor="#CCCCFF">
<font color="#800040">
<%=(rswebtotals.Fields.Item("sumM17WO").Value)%>
</font> </td>
</tr>
</table>
</body>
</html>
[The lines shown above in color are one line. They have been
split for formatting purposes.]
The SQL Select Statement - Page 2
More ASP using Dreamweaver UltraDev
Conclusion - Page 4
|
