[w]
[e]
[l]
[c]
[o]
[m]
[e]
[Written] Article
 
Home > Written > Displaying Recordsets and Viewing Records
Back

[Note: This was originally written for the [aspgenericdb] mailing list around June 2000. I no longer work with the organization referred to below. Fortunately, as of December 2002 all the examples at their site are intact.]

Okay, here's a lesson on displaying recordsets and viewing records without GenericDB. When I was learning to code I got the most from reading and figuring out other peoples' code. Therefore you will get the most from this if you spend some time comparing the code with what the page actually displays. I hope it helps.

First, why would you not use GenericDB? That's easy. GenericDB provides rapid development at a cost of overhead and massive scalability. In 98% of the on-line databases out there, the cost is entirely acceptable and GDB is a safe, stable solution. However, if you only need to display data to your surfers, you do not need to saddle yourself with the overhead GDB needs to handle edits, updates, deletes, and all the rest.

Also, you'll have something easier for the next person to read. GDB isn't always the best tool for the job, and someday you will move on [Heck, I moved on from the job I had when I wrote this]. How happy are you when you don't have to rewrite the last programmer's work from scratch? What if you were called in to update a simple list of offices, and found the last guy used GenericDB to display them. If you didn't already use GenericDB, would you spend half an hour to figure it out? Not always. For display routines, if users do not need to edit records, it can be faster to write from scratch.

You need two pages. One will be a list of available records which will let us select a single record. Selecting a record will lead to the next page, which will display detail on that single record. In GenericDB these are simply called the Lister and the Viewer. Simple is good.

Take a look at a live page I built, a list of the offices in an organisation (the link opens a new window):

http://www.ofifc.org/Centres/OfficeList.asp?Region='ON'

Notice that this page uses a parameter in the URL -- Region='ON'. Try replacing 'ON' with 'BC' and you'll see a list of centres in British Columbia. Leave the parameter off and you'll get a list of centres across Canada. Makes it easy to use the same code elsewhere (other provinces).

The source for OfficeList.asp:

<%
' Get parameters
' Example: http://.../FriendshipCentres.asp?Region='ON'
pRegion=Request.QueryString("Region")
pCountRegion=Request.QueryString("Region").Count

' The default ptype is FC (Friendship Centres). Other choices are NAFC or PTA.
pType="'FC'"

' Build query to load page
sql = "SELECT FCID, Region, Office, Community, WebSite FROM tblOffice WHERE (OfficeType = " & cstr(pType) & ")"
If pCountRegion > 0 Then sql = sql & " AND Region = " & cstr(pRegion)
sql = sql & " ORDER BY Region, Community "

' Open Connection
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "dsnOffices"
'Run query
set rsOfficeList = Server.CreateObject("ADODB.Recordset")
rsOfficeList.Open sql, Conn
%>

<HTML>
<HEAD>
<title>OFIFC - Friendship Centres</title>
</HEAD>

<body>
<!-- Include headers here -->

Canadian Friendship Centres
<!-- List national links -->

Ontario Friendship Centres
<!-- Link to view e-mail addresses -->

<!-- Now the main attraction -->
<TABLE border="3" width="100%">
<TR>
<TD><STRONG>Community</STRONG></TD>
<TD><STRONG>Friendship Centre</STRONG></TD>
<TD></TD>
</TR>
<%
' Process Row
On Error Resume Next
rsOfficeList.MoveFirst
x = 0
do while Not rsOfficeList.eof
%>
<TR>
<TD><%=rsOfficeList("Community")%>, <%=rsOfficeList("Region")%></TD>
<TD><a href="Office.asp?FCID=<%=rsOfficeList("FCID")%>">
<%=rsOfficeList("Office")%></TD></A>
</TR>
<%
rsOfficeList.MoveNext
x=x+1
loop
rs.Close
Set rs = Nothing
Conn.Close
Set Conn=Nothing
%>
</TABLE>

<EM><%= x %> listed.</EM>

<!-- Include the footer here -->
</body>
</HTML>


Simple enough? All the work is really after the "Process Row" comment -- and it's really no work at all. Ah, the decadence of the space age. It's simpler when you don't need a WHERE clause. I could have written:

sql = "SELECT * FROM tblOffice WHERE Region='ON'"

And it would work exactly the same as what's there, though the portability to other regions would be lost. But big deal, it would make the code easier to follow and in two years year this code hasn't been ported to other provices anyway. Simplify. Make easy.

Now the part of "Process Row" that links this file with the Viewer is this:

<a href="Office.asp?FCID=<%=rsOfficeList("FCID")%>">
<%=rsOfficeList("Office")%></TD></A>

the first of these lines creates the link. The link will be to Office.asp in the form:

Office.asp?FCID=25

Or whatever the ID of the chosen centre actually is. Then next line displays the name of the centre (kept in the "Office" field) and closes the anchor tag.

That's the basics of creating a list. This particular list contains links to view individual records, which I'll discuss next.


DISPLAYING RECORDSETS AND VIEWING RECORDS (PART 2)

In the last message I explained how to write an ASP script to display a list of records and provided an example, which can be found at:

http://www.ofifc.org/Centres/OfficeList.asp?Region='ON'

Now for the code to display a single record.

First, the Lister gave us a parameter -- FCID. This is the unique ID of the record to view. With that FCID we will retrieve the record and display its contents. We'll be selective though; if certain fields are not filled in we won't display them. For example, there is no point displaying a logo if none is stored (our table stores the URL to an office's logo).

There's a nice trick in here too -- I display a link to MapQuest with all the parameters required to draw a map using the office's address. To tell you the truth I have no idea what half their parameters are for, but the construct works consistently on my site, at least as far as MapQuest has Canadian streets mapped. I figured it out by looking at the source of one of their pages when I was searching for a location. Not hard to figure out, and once it's done you can use it over and over. Thank-you MapQuest, what a great site. [Note: Mapquest has since changed their site and parameters, but you can figure out their current scheme by going to Mapquest and viewing the html source of their entry forms. The <FORM ACTION=""> tag tells you what URL to link to, and the form itself will provide the parameters to send this page. If anyone knows a consistent way to get maps from them I'd like to know about it.]

And so the source for Office.asp is:

' Get parameters from whatever linked to here
' Example link: http://.../Office.asp?FCID=25
pFCID=Request.QueryString("FCID")
pCount=Request.QueryString("FCID").Count

' if no ID specified then dump'em back home
If (pCount < 1) Then Response.Redirect "http://www.ofifc.org/"

' Run query
sqlstr = "SELECT * FROM tblOffice WHERE FCID=" & pFCID
' Open Connection
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "dsnOffices"
' Open recordset
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sqlstr, Conn

' Assign fields to variables
FCID=rs("FCID")
REGION=rs("REGION")
OFFICE=rs("OFFICE")
OFFICEtype=rs("OFFICETYPE")
ADDRESS=rs("ADDRESS")
COMMUNITY=rs("COMMUNITY")
POSTALCODE=rs("POSTALCODE")
MAILINGADDRESS=rs("MAILINGADDRESS")
MAILINGPOSTALCODE=rs("MAILINGPOSTALCODE")
TOLLFREE=rs("TOLLFREE")
PHONE=rs("PHONE")
FAX=rs("FAX")
WEBSITE=rs("WEBSITE")
EMAIL=rs("EMAIL")
LOGO=rs("LOGO")
INCORPDATE=rs("INCORPDATE")
INCORPNUMBER=rs("INCORPNUMBER")
COREFUNDEDSINCE=rs("COREFUNDEDSINCE")
HISTORY=rs("HISTORY")
OFFICEHOURS=rs("OFFICEHOURS")
OFFICEFACILITYRENTAL=rs("OFFICEFACILITYRENTAL")
%>

<HTML>
<HEAD>
<title>OFIFC - <%=OFFICE%></title>
</HEAD>

<body>

<!-- Include headers here -->

<!-- Display the name of the office we've loaded -->
<%=OFFICE%>

<TABLE width="100%"><TR>
<TD bgcolor="#FFFFCC" align="CENTER"><font face="Arial">
<a href="OfficeList.asp?Region='<%=REGION%>'">
Friendship Centre List</A>
</TD>
<TD bgcolor="#FFFFCC" align="CENTER"><font face="Arial">
<a href="Viewprogramlist.asp?FCID=<%=pFCID%>">
Programs</A>
</TD>
<TD bgcolor="#FFFFCC" align="CENTER"><font face="Arial">
<a href="OfficePositionList.asp?FCID=<%= pFCID %>">
Staff</A>
</TD>
</TR></TABLE>
<HR>

<P>
<CENTER>
<%if LOGO <> "" Then %>
<TABLE width="80%" border="0" cellspacing="2"
cellpadding="0">
<TR>
<TD width="50%" height="117">
<img src="<%=LOGO%>" border=0 alt="Logo" align="RIGHT">
</TD>
<TD width="50%">
<%end if%>
<ADDRESS>
<STRONG><%=OFFICE%></STRONG><BR>
<%=ADDRESS%><BR>
<%=COMMUNITY%>, <%=REGION%><BR>
<%=POSTALCODE%><P>

<!-- Link to MapQuest with address -->
<form action="http://www.mapquest.com/cgi-bin/ia_find?screen=ia-map-form&link=ia-map-result&uid=uy05abz8020ckdqw" method="POST">
<input type="hidden" name="screen" value="ia-map-form">
<input type="hidden" name="link" value="ia-map-result">
<input type="hidden" name="uid" value="uy05abz8020ckdqw">
<input type="hidden" name="random" value="699">
<input type="hidden" name="event" value="find_search">
<input type="hidden" name="random" value="699">
<input type="hidden" name="zip" value="">
<% If Instr(1,ADDRESS,"Box") < 1 Then %>
<input type="hidden" name="address" value="<%=ADDRESS%>">
<% Else %>
<input type="hidden" name="address" value="">
<% End If %>
<input type="hidden" name="city" value="<%=COMMUNITY%>">
<input type="hidden" name="state" value="<%=REGION%>">
<input type="hidden" name="country" value="CANADA">
<input type="Submit" name="Find Map" value="Get Map from MapQuest">
<!-- End of link to MapQuest -->

<BR>
<P>
<%if TOLLFREE <> "" Then %>
Toll Free: <%=TOLLFREE%><BR>
<%end if%>
Phone: <%=PHONE%><BR>
Fax: <%=FAX%><P>
<%if WEBSITE <> "" Then %>
Web Site: <a href="<%=WEBSITE%>"><%=WEBSITE%></A><BR>
<%end if%>
<%if EMAIL <> "" Then %>
E-Mail: <a href="mailto:<%=EMAIL%>"><%=EMAIL%></A><P>
<%end if%>

</ADDRESS>
<%if LOGO <> "" Then %>
</TD>
</TR>
</TABLE>
<%end if%>
</CENTER>

<%if OFFICEHOURS <> "" Then %>
<P>
<font size=+1>
<CENTER>- Hours -
<font size=+0>
<BR>
<%=OFFICEHOURS%>
</CENTER>
<%end if%>

<%if HISTORY <> "" Then %>
<P>
<font size=+1>
<CENTER>- History -</CENTER>
<font size=+0>
<font face="Georgina,Times New Roman,Times Roman">
<BR>
<%=HISTORY%>
</font>
<%end if%>

<%
rs.Close
Set rs = Nothing
Conn.Close
Set Conn=Nothing
%>

<!-- Include footer here -->
</body>
</HTML>


Once again the business part of this page is pretty simple.

1) Get the ID of the record to load:

pFCID=Request.QueryString("FCID")
pCount=Request.QueryString("FCID").Count

' if no ID specified then dump'em back home
If (pCount < 1) Then Response.Redirect "http://www.ofifc.org/"

' Run query
sqlstr = "SELECT * FROM tblOffice WHERE FCID=" & pFCID
' Open Connection
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "dsnOffice"
' Open recordset
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sqlstr, Conn

2) I like to assign the fields to local variables, it makes them cleaner to work with and avoids an obscure quirk with memo fields that crops up if the fields aren't accessed in order.

ADDRESS=rs("ADDRESS")
TOLLFREE=rs("TOLLFREE")

3) Display the data. If you want to get fancy, make sure there's something to display before displaying it:

<%=ADDRESS%><BR>

<%if TOLLFREE <> " " Then %>
Toll Free: <%=TOLLFREE%><BR>
<%end if%>

4) Close up shop.

rs.Close
Set rs = Nothing
Conn.Close
Set Conn=Nothing


And that's how to build your own Lister and Viewer for any given recordset. We've saved a ton of overhead by not relying on GenericDB and all the Session vars that come with it. You can get a bit more speed by explicitly declaring:

<%@ ENABLESESSIONSTATE = FALSE %>

as the first line of your asp file. We also built something we can add toys to, like the link directly to Mapquest.

GenericDB is great and it's saved me a thousand hours too, but don't use it as a crutch when there are better ways to get the job done. Enjoy.

Take care,
Eli.


Copyright © 2001-2006 Eli Robillard, All Rights Reserved