<%@LANGUAGE="VBScript"%>
<%Option Explicit%>
<%
'-----------------------------------------------------------
'--- Declare all ADO 2.0 constants to be used
'-----------------------------------------------------------
Const adOpenStatic = 3
Const adUseClient = 3
'-----------------------------------------------------------
'--- Declare all variables to be used
'-----------------------------------------------------------
dim objConn '--- The ADO Connection to the database.
dim objRS '--- The ADO Recordset that stores the data.
dim intCurrentPage '--- The current page.
dim intPageSize '--- The amount of pages.
dim intRow '--- A loop counter variable.
dim intCol '--- A loop counter variable.
dim intPos '--- Position of a square bracket within a string.
dim intDisplayRows '--- Display this many rows on a page.
dim strSQL '--- The SQL used to generate the report.
dim strSort '--- The column to sort by.
dim strSortDir '--- The direction of the sort.
dim strLastSort '--- The column we sorted by last time.
dim strLastSortDir '--- The direction of the sort we used last time.
dim strConn '--- The connection string to talk to the database.
dim strColor '--- Used to store the color of the report rows.
'-----------------------------------------------------------
Figure 4 Create/Disconnect Recordset
'-----------------------------------------------------------
'--- Create the disconnected recordset, or retrieve it
'--- from the session.
'-----------------------------------------------------------
if isobject(session("Report")) then
'--- Retrieve he disconnected recordset.
set objRS = session("Report")
else
'--- Create the ADO objects
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
'--- Connect to the database using OLE DB
strConn = "Provider=SQLOLEDB.1;User ID=sa;Password=;"
strConn = strConn & "Initial Catalog=pubs;Data Source=papanotebook"
objConn.Open strConn
'--- Create SQL to retrieve list of authors, their books and the royalties.
strSQL = "SELECT a.au_fname as 'First Name', a.au_lname as 'Last Name', "
strSQL = strSQL & " t.Title, ta.royaltyper as 'Royalty' "
strSQL = strSQL & " FROM authors a INNER JOIN titleauthor ta "
strSQL = strSQL & " ON a.au_id = ta.au_id "
strSQL = strSQL & " INNER JOIN titles t ON ta.title_id = t.title_id"
strSQL = strSQL & " ORDER BY a.au_lname ASC"
'--- Use a client-side cursor so we can sort later
'--- and so we can create a disconnected recordset.
objRS.CursorLocation = adUseClient
objRS.Source = strSQL
'--- Use a static cursor so we can navigate freely.
objRS.CursorType = adOpenStatic
set objRS.ActiveConnection = objConn
'--- Open the report's recordset.
objRS.Open
'--- Disconnect the recordset from the connection.
set objRS.ActiveConnection = nothing
'--- Close the connection to the database.
objConn.Close
end if
'--- Retrieve the disconnected recordset from the session.
Set objRS = Session("Report")
'-----------------------------------------------------------
Figure 5 Sorting Records
'-----------------------------------------------------------
'--- Sorting section
'-----------------------------------------------------------
'--- If the user requested the recordset to be re-sorted...
If Trim(Request("SortBy")) <> "" and Trim(Request("ReSort")) <> "" Then
'--- Retrieve the field the user chose to sort by.
strSort = Request("SortBy")
'--- Get the field (and direction) we used to
'--- sort the recordset the last time.
'--- The column name is enclosed within brackets,
'--- in case it has multiple words.
intPos = Instr(2, objRS.Sort, "]")
If intPos > 0 Then
strLastSort = Left(objRS.Sort, intPos)
strLastSortDir = Trim(Mid(objRS.Sort, intPos + 2))
End If
'--- Check if the sorting field has changed.
If Trim(strSort) <> Trim(strLastSort) then
'--- The sorting field has changed,
'--- so we will sort in ascending order.
strSortDir = "asc"
Else
'--- The sorting field is the same.
'--- Now we determine which sort order direction
'--- we used last time (so we can switch it).
If strLastSortDir = "asc" Then
strSortDir = "desc"
Else
strSortDir = "asc"
End If
End If
objRS.Sort = strSort & " " & strSortDir
End If
'-----------------------------------------------------------
Figure 6 Paging and Navigation
'-----------------------------------------------------------
'--- Paging section
'-----------------------------------------------------------
'--- Set the default page size
intPageSize = 10
'--- Determine if the user entered a page size.
If Trim(request("txtPageSize")) <> "" then
'--- Set the page size to what the user chose.
intPageSize = request("txtPageSize")
End If
'--- Check if we are not showing all records (then we are paging).
If Trim(Request("lstPages")) <> "" and Trim(Request("AllRecs")) = "" then
'--- Set the current page to what the user chose.
intCurrentPage = Request("lstPages")
Else
'--- Since we are showing all records,
'--- set the current page to the first page.
intCurrentPage = 1
End If
'--- As long as there are records...
If Not (objRS.BOF and objRS.EOF) Then
'--- Set the page size
objRS.PageSize = intPageSize
'--- If the page we were on is no longer valid,
'--- set the current page to the last page available.
If CInt(intCurrentPage) > CInt(objRS.PageCount) Then
intCurrentPage = objRS.PageCount
End If
objRS.AbsolutePage = intCurrentPage
End If
'-----------------------------------------------------------
%>
Figure 7 Submitting the Report
<html>
<head>
<script language="javascript">
function Refresh()
// Refresh the recordset by submitting the form.
{
document.frmReport.submit();
}
function MoveToPage(PageNumber)
{
// Select the page number to go to, then submit the page.
if (PageNumber != -1)
{document.frmReport.lstPages[PageNumber].selected = true;}
else
{document.frmReport.lstPages[0].selected = true;}
Refresh();
}
function ShowAllRecs()
{
//Show all of the records on 1 page, then submit the page.
document.frmReport.txtPageSize.value = <%=objRS.RecordCount%>;
document.frmReport.AllRecs.value = "yes"
Refresh();
}
function ReSort(SortString)
{
//Sort the recordset, then submit the page.
document.frmReport.SortBy.value = SortString;
document.frmReport.ReSort.value = "yes";
Refresh();
}
</script>
</head>
Figure 8 Report Heading on the Web
<!-- Make all links bright yellow. -->
<body bgcolor="#ffffff" link="#ffff00" alink="#ffff00" vlink="#ffff00"">
<center>
<hr>
<table border="0" width="100%">
<tr>
<td align="left">
<font face="helvetica,verdana,arial" size="1" color="#000080">
<b><%=Now()%></b></font>
</td>
<td align="center">
<font face="helvetica,verdana,arial" size="4" color="#000080">
Authors Report</font>
</td>
<td align="right">
<%If Not (objRS.BOF and objRS.EOF) Then%>
<font face="helvetica,verdana,arial" size="1" color="#000060">
<b>(Page <%=intCurrentPage%> of <%=objRS.PageCount%>)</b>
</font>
<%End If%>
</td>
</tr>
</table>
<hr>
</center>
Figure 9 Hyperlinks to Refresh the Report
<form name="frmReport" method="post" action="report.asp">
<table cellspacing="2" cellpadding="2" border="0" width="100%">
<tr>
<!-- Display the menu options. -->
<td align="center" bgcolor="#000060" nowrap>
<font face="helvetica,verdana,arial" size="1" color="#99cccc">
<a href="javascript:Refresh()"
title="Apply new settings to the report"
onmouseover="window.status='Refresh Report'; return true"
onmouseout="window.status=''; return true">
Refresh Report</a></font>
</td>
<td nowrap>
</td>
<td align="center" bgcolor="#000060" nowrap>
<font face="helvetica,verdana,arial" size="1" color="#99cccc">
<a href="javascript:ShowAllRecs()"
title="Display all records in one screen."
onmouseover="window.status='Show All Records'; return true"
onmouseout="window.status=''; return true">
Show All Records</a></font>
</td>
<!-- Display the paging menu options. -->
<td align="right" valign="top" width="100%" nowrap>
<%If Not (objRS.BOF and objRS.EOF) Then%>
<font face="helvetica,verdana,arial" size="1" color="#000060">
<b>Records per page:</b></font>
<input type="text" size="3" name="txtPageSize"
value="<%=intPageSize%>">
<font face="helvetica,verdana,arial" size="1" color="#000060">
<%If objRS.PageCount > 1 Then%>
<b>Go to page:</b></font>
<select size="1" name="lstPages" onChange="Refresh();">
<%For intRow=1 To objRS.PageCount%>
<%If CInt(intCurrentPage) = CInt(intRow) Then%>
<option selected value="<%=intRow%>"><%=intRow%>
<%Else%>
<option value="<%=intRow%>"><%=intRow%>
<%End If%>
<%Next%>
</select>
<%End If%>
<%Else%>
<input type="hidden" name="txtPageSize" value="<%=intPageSize%>">
<%End If%>
</td>
</tr>
</table>
Figure 10 Displaying the Data Rows
<!-- Display the data rows. -->
<%intDisplayRows = objRS.AbsolutePosition + objRS.PageSize - 1%>
<%For intRow = objRS.AbsolutePosition to intDisplayRows%>
<tr>
<%If CBool( Instr(1, CStr(intRow / 2), ".") > 0) Then
strColor = "#d0ffd0"
else
strColor = "#ffffff"
End If%>
<%For intCol = 0 To objRS.Fields.count - 1%>
<td nowrap bgcolor="<%=strColor%>">
<font face="helvetica,verdana,arial" size="1">
<%=objRS.Fields(intCol).value%></font></td>
<%Next%>
</tr>
<%objRS.MoveNext%>
<%if objRS.EOF then exit for%>
<%Next%>
</table>
</td>
</tr>
</table>
Figure 11 Getting the Report Footer
<!-- Display the "Next" and/or "Previous" paging options. -->
<table border=0 cellspacing="2" cellpadding="2" align="left">
<tr>
<%'--- Determine if we should display a PREVIOUS page button.
If (intCurrentPage > 1) Then%>
<td align="center" bgcolor="#000060" width="55">
<font face="helvetica,verdana,arial" size="1" color="#99cccc">
<a href="javascript:MoveToPage(document.frmReport.lstPages.selectedIndex
- 1)"
onmouseover="window.status='Go to Previous Page';"
onmouseout="window.status='';"
title="Go to Previous Page">
Previous</a></font>
</td>
<%End If%>
<%'--- Determine if we should display a NEXT page button.
If CInt(intCurrentPage) < CInt(objRS.PageCount) Then%>
<td align="center" bgcolor="#000060" width="35">
<font face="helvetica,verdana,arial" size="1" color="#99cccc">
<a href="javascript:MoveToPage(document.frmReport.lstPages.selectedIndex
+ 1)"
onmouseover="window.status='Go to Next Page';"
onmouseout="window.status='';"
title="Go to Next Page">
Next</a></font>
</td>
<%End If%>
</tr>
</table>