AIHOST™ ASP Tutorial 10

ASP Tutorial

How do I format the data that I receive from the database?
Formatting the data received from a database query can be rather simple to do when you only have 5 or 10 records in a database. But, what happens when you have 10,000 seconds in a database? The select * from table query doesn't get the job done. Normally, your browser will die before all the data is given to it.

In this chapter, we'll discuss a way to use paging. This will only display a certain number of records at a time. It will eliminate the mess that can be associated with large databases. Please review the terms below. These terms will be used in this section.

 This line is actually an SSI (Server Side Include) statement. This statement includes a very commonly used ASP include file named ADOVBS.inc. This file is downloadable from this tutorial. To do so, click here.

request.querystring("fieldname") This ASP tag is similar to that of request.form. This option means the ASP code will strip out part of the URL. In the example below, the request.querystring would be fieldname: http://domain.com/script.asp?fieldname=1

CInt Without getting very complicated, we'll just explain this as an integer. It requires the variable to be an integer. Anything else will not be allowed to be stored as that variable. The more you use it, the more you will understand it.

To execute the following example script below, you will need to create either a MS Access, or MS Excel database, and create a DSN. Here is a short description of what the database should look like. In this example, we will use MS Access.
Table name: example

Field Data Type

ID AutoNumber

Name Text

Email Text

Phone Text

Input a few lines of data into the table, if you haven't already. We'll say that 5 or 6 entries are sufficient. If you've gone through each chapter, then you've probably already created this DSN, and database. Again, if you haven't already done so, create a DSN named 'example' using your Windows Control Panel. This script will display data from a database, one page at a time. The database records will be counted and displayed X number at a time. You can designate how many records will be displayed on each page. A good average number to use is 10, however in this example, we'll only use 2 at a time.

------------- Copy the following into display_data.asp -------------

<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>

<%
' Set how many records per page we want.
' You'll see that in this example we use 2 records per page.
Const NumberPerPage = 2

' Retrieve the current page
Dim CurrentPage
If request.querystring("CurrentPage") = "" then

' We are on the first page
CurrentPage = 1
Else

' We are not on the first page, so read the string from the URL
CurrentPage = request.querystring("CurrentPage")
End If
Dim conn

' Set your connection and the DSN
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=example"

' Create a record set
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")

' Set the cursor location. In ASP, a cursor is
' very important. It can be set almost anywhere
' in a databae. This will ensure we're starting from the beginning.
rs.CursorLocation = adUseClient

' Set the cache size = to the # of records/page
rs.CacheSize = NumberPerPage

' Set your SQL variable, and connect to the database
Dim SQLstmt
SQLstmt = "SELECT * FROM example"
rs.Open SQLstmt, Conn

' Ensure you're starting at the beginning of the database
rs.MoveFirst
rs.PageSize = NumberPerPage

' Find out how many pages are going to be used in the entire
' select statement. Basically, there's a mathematical division
' that will take place between the total number of records, and
' the number of records per page.

Dim TotalPages
TotalPages = rs.PageCount

' Set what page you're on. This is used for future execution of
' this script (in other words, it's not for the first time)

rs.AbsolutePage = CurrentPage
' Counting variable for our recordset

Dim count
%>
<HTML>
<HEAD>
<TITLE>Displaying data in a browser script</TITLE>
</HEAD>
<BODY>
<TABLE BORDER=1 WIDTH=600>
<TR>
<TH>NAME</TH>
<TH>EMAIL</TH>
<TH>PHONE</TH>
</TR>
<%
' Set Count equal to zero
Count = 0
Do While Not rs.eof And Count < rs.PageSize
%>
<TR>
<TD><%= rs("Name") %></TD>
<TD><%= rs("Email") %></TD>
<TD><%= rs("Phone") %></TD>
</TR>
<%
' Add one to the Count variable
Count = Count + 1

' Move the cursor to the next record, and then loop back to the
' Do statement
rs.MoveNext
Loop
' When all records that are allowed to be printed
' have been printed to the screen, spit out the
' current page # / total pages
response.write "</TABLE>"
response.write("Page " & CurrentPage & " of " & TotalPages)

' Display Next / Prev buttons, if needed
If CurrentPage > 1 then
' We are not at the beginning, show the prev button
response.write("<INPUT TYPE=BUTTON VALUE='<<PREVIOUS'")
response.write(" ONCLICK=""document.location.href='display_data.asp?CurrentPage=")
response.write( CurrentPage - 1 & "';"">")
End If

If CInt(CurrentPage) <> CInt(TotalPages) then

' Display Next / Prev buttons, if needed
If CurrentPage > 1 then
' We are not at the beginning, show the prev button

response.write("<INPUT TYPE=BUTTON VALUE='<<PREVIOUS'")
response.write(" ONCLICK=""document.location.href='display_data.asp?CurrentPage=")
response.write( CurrentPage - 1 & "';"">")
End If

If CInt(CurrentPage) <> CInt(TotalPages) then
' We are not at the end, show a next button

response.write("  <INPUT TYPE=BUTTON VALUE='NEXT>>'")
response.write(" ONCLICK=""document.location.href='display_data.asp?CurrentPage=")
response.write( CurrentPage + 1 & "';"">")
End If
%>
</BODY>
</HTML>