InformIT Visual Tutorial 5: Active Server Pages Scripts

Sep 2, 2001

␡

⎙ Print

< Back Page 8 of 8

The Retrieve Script

To understand the general structure of an ASP retrieve script, it's helpful to imagine what you would have to do if you had to manually list the records in your database table. What are the steps you would take? Assuming that you have a database and a table populated with information, you would follow this procedure:

Open the database.
Send a SQL SELECT query to the database to retrieve the information from the table.
View the results.
Close the table and exit the database when you're done.

Those are the same steps your script has to take to programmatically retrieve information from your database table. Let's look at each step in detail in the following sections.

Step 1: Open the Database

As mentioned earlier, every script you create to write into or read from your database starts with the same two lines of code to open up the database. Thus, since the ASP retrieve script reads information from a database, it needs these two lines of code:

<%
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("database_folder/database_name") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")

For an explanation, see step 1 for the ASP insert script, described earlier in this article. For our guest book example, Listing 25 shows the code.

Listing 25 `retrieve.asp`: Code to Open `visualtutorial.mdb`

<%
'
' Step 1: Open up the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")

Let's create the ASP retrieve script. Using your favorite editor, follow these steps:

Create a new file.
Add the lines in Listing 25 to your file.
Save the file as retrieve.asp.

With the database open, we can send SELECT queries to it.

Step 2: Send a SQL `SELECT` Query to the Database

Tutorial 3 covered several different SQL SELECT queries for reading information from a table. The general format of these queries was as follows:

SELECT fieldname(s) FROM table(s) WHERE condition(s)

The simplest SELECT query reads everything out of a single table:

SELECT * FROM table

Where table is the name of a table in the database. To read everything out of our GuestBook table, this is the SQL SELECT query:

SELECT * FROM GuestBook

To run this query manually, you would type it into the SQL query window and then click the Run button. To have your script send the query programmatically, you use conn.execute, passing the query as a string—that is, putting the query inside double quotes (""). The general format for programmatically sending a SQL SELECT query to the database is shown in Listing 26.

Listing 26 General Command for Sending a `SELECT` Query to the Database

conn.execute("SELECT fieldnames(s) FROM table(s) WHERE condition(s)")

Unlike a SQL INSERT query, the SQL SELECT query returns a result set—zero or more rows of data. The result set is also known as a record set. You store the result set in a results variable:

set result=conn.execute("SELECT fieldnames(s) FROM table(s)
 WHERE condition(s)")

Substituting our example SQL SELECT query yields the statement in Listing 27.

Listing 27 Command to Read the Entire `GuestBook` Table

set rs=conn.execute("SELECT * FROM GuestBook")

where rs is the name of our result set variable.

For practice, add the line above to read the entire GuestBook table. Then save your file. Your retrieve script should looks like Listing 28.

Listing 28 `retrieve.asp`: Code to Read the Entire `GuestBook` Table

<%
'
' Step 1: Open up the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Send a SQL SELECT query to the database
'
set rs=conn.execute("SELECT * FROM GuestBook")
%>

Unlike when we were manually running SQL SELECT queries and viewing the entire result set on the screen, your script (programmatically) can only "see" one row of the result set at a time. This detail will be relevant in the next section, when we display the query results in the user's browser.

Step 3: Display the Results in the User's Browser

When you execute a SQL SELECT query, it returns a blank row if no data in the specified table(s) matches the condition(s) specified in the query. If something matches, the query returns one or more rows of data. Initially, the results variable contains the first row of the result set. To read the other rows of data, you need to add code. Before we show you that code, let's look at the syntax for displaying the value of a particular field (see Listing 29).

Listing 29 General Command to Print a Field

<%=results("fieldname")%>

For example, if we want to display the user's email address (fieldname email) and our results variable is named rs, this is the code:

<%=rs("email")%>

The equal sign (=) after the begin-percent is the command to print the following value to the user's browser.

As an exercise, print all the values in the current results row, and save your file. Your retrieve script should look like Listing 30.

Listing 30 `retrieve.asp`: Code to Print All the Fields in the Current Record

<%
'
' Step 1: Open up the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Send a SQL SELECT query to the database
'
set rs=conn.execute("SELECT * FROM GuestBook")
%>
<%=rs("dateposted")%>
<%=rs("email"   )%>
<%=rs("name"   )%>
<%=rs("age"    )%>
<%=rs("gender"  )%>
<%=rs("comment"  )%>

Now upload retrieve.asp to your web server and test it by typing the URL:

http://your_domain_name/retrieve.asp

NOTE

Don't type our web address (http://localhost/informit/retrieve.asp) into your browser. You must upload retrieve.asp to your web browser and use your own address.

On our web server, retrieve.asp brings up the display shown in Figure 6.

Figure 6 Browser: Testing retrieve.asp.

Compare this display with the nicely formatted display shown earlier in Figure 5. Besides the obvious formatting differences, which we'll fix soon enough, it's plain that our script only printed out one row—Fred's record. Next, we look at printing out all records in the SQL SELECT results.

Printing All the Records in a Result

To print out all the records in the query result, you must "loop through" every record. Within the loop, you add code to process or print information in the current record. The best way to loop through every record is with a Visual Basic DO-WHILE-LOOP. After processing, you add code to move to the next record. The general syntax for such a loop is shown in Listing 31.

Listing 31 General Code to Loop and Print All Records in a Result Set

DO WHILE results.EOF=FALSE
... print stuff out ...
results.MoveNext
LOOP

The result set, results, has an EOF property whose value is FALSE, as there are still rows of information waiting to be processed. You move to the next row by invoking the result set's MoveNext method.

As an exercise, apply the above DO-WHILE-LOOP along with the MoveNext from Listing 31 to print out all records in the result set. Your retrieve script should look like Listing 32.

Listing 32 `retrieve.asp`: Code to Loop and Print All Records in a Result Set

<%
'
' Step 1: Open up the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Send a SQL SELECT query to the database
'
set rs=conn.execute("SELECT * FROM GuestBook")
%>
<% DO WHILE rs.EOF=FALSE %>
 <%=rs("dateposted")%>
 <%=rs("email"   )%>
 <%=rs("name"   )%>
 <%=rs("age"    )%>
 <%=rs("gender"  )%>
 <%=rs("comment"  )%>
<%
rs.MoveNext
LOOP
%>

Now upload retrieve.asp to your web server and test it by typing the URL (see Figure 7):

http://your_domain_name/retrieve.asp

Figure 7 Browser: Printing all records in the result set.

Now all the rows print, but unfortunately the output is unformatted and the information runs together. We need to format the output.

Simple Formatting

Remember that you can put both Visual Basic (VB) code and HTML code in a ASP script. The insert script only had VB code. However, the retrieve script needs both, especially if you want your user to see nicely formatted output. The simplest formatting we can do is to add a break tag (<BR>) at the end of every row.

Add a <BR> after the last field to get the results shown in Listing 33.

Listing 33 `retrieve.asp`: Code to Add a Line Break After Printing a Record

<%
'
' Step 1: Open up the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Send a SQL SELECT query to the database
'
set rs=conn.execute("SELECT * FROM GuestBook")
%>
<% DO WHILE rs.EOF=FALSE %>
 <%=rs("dateposted")%>
 <%=rs("email"   )%>
 <%=rs("name"   )%>
 <%=rs("age"    )%>
 <%=rs("gender"  )%>
 <%=rs("comment"  )%>
 <BR>
<%
rs.MoveNext
LOOP
%>

Now upload retrieve.asp to your web server and test it (see Figure 8).

Figure 8 Browser: Simple formatting.

That looks only slightly better. We'll apply some of the design rules from a previous tutorial to make our guest book look even better.

Table Alignment

We'll use tables to horizontally align the results. Each field will have its own cell in the table. As we covered the HTML for creating tables in the last tutorial, we won't go over these commands in detail. Here's the general procedure you should follow:

Put a <TABLE> before the DO-WHILE statement.
Put a <TR> after the DO-WHILE statement.
Surround each field with a <TD></TD> pair.
Put a </TR> before rs.MoveNext.
Put a </TABLE> after the LOOP.

Your code with HTML table tags added looks like Listing 34.

Listing 34 `retrieve.asp`: Code to Print Out Fields in a Table Format

<%
'
' Step 1: Open up the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Send a SQL SELECT query to the database
'
set rs=conn.execute("SELECT * FROM GuestBook")
%>
<TABLE CELLSPACING=0>
<% DO WHILE rs.EOF=FALSE %>
 <TR>
 <TD><%=rs("dateposted")%></TD>
 <TD><%=rs("email"   )%></TD>
 <TD><%=rs("name"   )%></TD>
 <TD><%=rs("age"    )%></TD>
 <TD><%=rs("gender"  )%></TD>
 <TD><%=rs("comment"  )%></TD>
 </TR>
<%
rs.MoveNext
LOOP
%>
</TABLE>

Upload retrieve.asp to your web server and test it (see Figure 9).

Figure 9 Browser: Using tables for proper horizontal alignment.

Our display still looks jagged because of the date/time information, which varies in length. If you aren't interested in the exact time of the entry, you can make the display look much nicer by using the Visual Basic FormatDateTime() function to remove the time part of the information. The syntax for this command is shown in Listing 35.

Listing 35 General Code to Print Out a Date Only

FormatDateTime(datetime_variable, VBShortDate)

Our datetime_variable is rs("dateposted"); applying the function to this variable yields the following:

FormatDateTime(rs("dateposted"), VBShortDate)

Replace the rs("dateposted") in your script with the line above (see Listing 36).

Listing 36 `retrieve.asp`: Code to Print Out Just the Date (No Time)

<%
'
' Step 1: Open up the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Send a SQL SELECT query to the database
'
set rs=conn.execute("SELECT * FROM GuestBook")
%>
<TABLE CELLSPACING=0>
<% DO WHILE rs.EOF=FALSE %>
 <TR>
 <TD><%=FormatDateTime(rs("dateposted"), VBShortDate)%></TD>
 <TD><%=rs("email"   )%></TD>
 <TD><%=rs("name"   )%></TD>
 <TD><%=rs("age"    )%></TD>
 <TD><%=rs("gender"  )%></TD>
 <TD><%=rs("comment"  )%></TD>
 </TR>
<%
rs.MoveNext
LOOP
%>
</TABLE>

Upload the script to your web server and test it (see Figure 10).

Figure 10 Browser: Time information removed.

Our display looks much nicer, but there's still plenty of work to be done to make it look good.

Font Changes

Let's switch the font from a serif font to a sans serif font. We need to change each value displayed, so surround each value with a pair of <FONT FACE="Arial" STYLE="font-size:9pt"> </FONT> tags (see Listing 37).

Listing 37 `retrieve.asp`: Code to Change All Fonts to Arial 9 Point

<%
'
' Step 1: Open up the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Send a SQL SELECT query to the database
'
set rs=conn.execute("SELECT * FROM GuestBook")
%>
<TABLE CELLSPACING=0>
<% DO WHILE rs.EOF=FALSE %>
 <TR>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=FormatDateTime(rs("dateposted"), VBShortDate)%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("email"   )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("name"   )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("age"    )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("gender"  )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("comment"  )%>
   </FONT>
 </TD>
 </TR>
<%
rs.MoveNext
LOOP
%>
</TABLE>

Upload and test the script (see Figure 11).

Figure 11 Browser: Font changed to Arial.

That's much better, but the display still looks cluttered and difficult to read. We can use color to de-clutter the display.

Color Changes

Whenever you display rows of information, it often helps to alternate the background color between white and gray. To do so, follow this procedure:

Create a variable called dogrey and set it to false, before the DO-WHILE loop.
Instead of having a simple <TR> after the DO-WHILE loop, add an IF-THEN statement to check whether dogrey =TRUE. If dogrey=TRUE, print <TR BGCOLOR=LIGHTGREY>; otherwise, just print a normal <TR>.

The procedure sounds more complicated than it really is. Your retrieve script should now have the code that's listed in bold in Listing 38.

Listing 38 `retrieve.asp`: Code to Alternate Background Row Color

<%
'
' Step 1: Open up the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Send a SQL SELECT query to the database
'
set rs=conn.execute("SELECT * FROM GuestBook")
%>
<TABLE CELLSPACING=0>
<% dogrey=false %>
<% DO WHILE rs.EOF=FALSE %>
<% if dogrey=TRUE then %>
 <TR BGCOLOR=LIGHTGREY>
<% else %>
 <TR>
<% end if %>
<% dogrey=NOT(dogrey)%>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=FormatDateTime(rs("dateposted"), VBShortDate)%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("email"   )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("name"   )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("age"    )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("gender"  )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("comment"  )%>
   </FONT>
 </TD>
 </TR>
<%
rs.MoveNext
LOOP
%>
</TABLE>

Upload this script to your web server and test it (see Figure 12).

Figure 12 Browser: Alternating white and gray background rows.

It's getting there! You have to admit this is a little easier to read. We need to add yet more color to make our display look nicer. Remember, we're assuming that the web site has a light color and a dark color. Our dark color is maroon and our light color is tan. Another way we can use color to improve our site is to add a header row whose background is the dark color and whose labels are in the light color. As an exercise, follow these steps:

Add a header row for your table.
Apply your site's dark color as the background color for this row.
Make the header labels a sans serif font with your site's light color.

Your retrieve script should look like Listing 39.

Listing 39 `retrieve.asp`: Header Row Added with a Dark Colored Background

<%
'
' Step 1: Open up the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Send a SQL SELECT query to the database
'
set rs=conn.execute("SELECT * FROM GuestBook")
%>
<TABLE CELLSPACING=0>
<TR BGCOLOR=MAROON>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Date</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Email</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Name</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Age</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Gender</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Comment</B>
 </FONT>
</TD>
</TR>
<% dogrey=false %>
<% DO WHILE rs.EOF=FALSE %>
<% if dogrey=TRUE then %>
 <TR BGCOLOR=LIGHTGREY>
<% else %>
 <TR>
<% end if %>
<% dogrey=NOT(dogrey)%>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=FormatDateTime(rs("dateposted"), VBShortDate)%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("email"   )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("name"   )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("age"    )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("gender"  )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("comment"  )%>
   </FONT>
 </TD>
 </TR>
<%
rs.MoveNext
LOOP
%>
</TABLE>

Upload and test it (see Figure 13).

Figure 13 Browser: Header row added.

Like the pretty forms we created in Tutorial 4, we can surround our output table with a single-line border whose color is the dark color. As an exercise, put the entire table inside a single-cell table with a dark border (see Listing 40).

Listing 40 `retrieve.asp`: Code to Add a Dark Border Around the Table

<%
'
' Step 1: Open up the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Send a SQL SELECT query to the database
'
set rs=conn.execute("SELECT * FROM GuestBook")
%>
<TABLE CELLSPACING=0 BORDER=1 BORDERCOLOR=MAROON><TR><TD>
<TABLE CELLSPACING=0>
<TR BGCOLOR=MAROON>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Date</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Email</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Name</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Age</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Gender</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Comment</B>
 </FONT>
</TD>
</TR>
<% dogrey=false %>
<% DO WHILE rs.EOF=FALSE %>
<% if dogrey=TRUE then %>
 <TR BGCOLOR=LIGHTGREY>
<% else %>
 <TR>
<% end if %>
<% dogrey=NOT(dogrey)%>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=FormatDateTime(rs("dateposted"), VBShortDate)%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("email"   )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("name"   )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("age"    )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("gender"  )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("comment"  )%>
   </FONT>
 </TD>
 </TR>
<%
rs.MoveNext
LOOP
%>
</TD></TR></TABLE>
</TABLE>

Upload and test (see Figure 14).

Figure 14 Browser: Dark border around the table.

Our display is almost done. To complete the display, follow these steps:

Add a title.
Center both title and form.

Your code should looks like Listing 41.

Listing 41 `retrieve.asp`: Code to Add a Title and Center Results

<%
'
' Step 1: Open up the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Send a SQL SELECT query to the database
'
set rs=conn.execute("SELECT * FROM GuestBook")
%>
<CENTER>
<FONT FACE="Arial" STYLE="font-size:18pt" COLOR=MAROON>
<B>Cool People Who<BR>Signed My Guest Book</B>
</FONT>
<TABLE CELLSPACING=0 BORDER=1 BORDERCOLOR=MAROON><TR><TD>
<TABLE CELLSPACING=0>
<TR BGCOLOR=MAROON>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Date</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Email</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Name</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Age</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Gender</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Comment</B>
 </FONT>
</TD>
</TR>
<% dogrey=false %>
<% DO WHILE rs.EOF=FALSE %>
<% if dogrey=TRUE then %>
 <TR BGCOLOR=LIGHTGREY>
<% else %>
 <TR>
<% end if %>
<% dogrey=NOT(dogrey)%>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=FormatDateTime(rs("dateposted"), VBShortDate)%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("email"   )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("name"   )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("age"    )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("gender"  )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("comment"  )%>
   </FONT>
 </TD>
 </TR>
<%
rs.MoveNext
LOOP
%>
</TD></TR></TABLE>
</TABLE>
</CENTER>

Upload and test (see Figure 15).

Figure 15 Browser: Output titled and centered.

We're done with the formatting of our output! Isn't it pretty now?

Step 4: Close the Table and Exit the Database

To finish up, you need to close the result set and then close the database connection. The commands are shown in Listing 42.

Listing 42 General Code to Close a Result Set

results.close
set results=Nothing

Where results is the name of your result set variable. In our example, the result set variable is named rs, so the code for closing this result set is as follows:

rs.close
set rs=Nothing

You close the database for the retrieve script the same way you closed it for the insert script:

conn.close
set conn=nothing

To complete the retrieve script, follow these steps:

Close the result set.
Close the database connection.

Your retrieve script should looks like Listing 43.

Listing 43 `retrieve.asp`: Code to Close the Result Set and Database Connection

<%
'
' Step 1: Open up the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Send a SQL SELECT query to the database
'
set rs=conn.execute("SELECT * FROM GuestBook")
%>
<CENTER>
<FONT FACE="Arial" STYLE="font-size:18pt" COLOR=MAROON>
<B>Cool People Who<BR>Signed My Guest Book</B>
</FONT>
<TABLE CELLSPACING=0 BORDER=1 BORDERCOLOR=MAROON><TR><TD>
<TABLE CELLSPACING=0>
<TR BGCOLOR=MAROON>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Date</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Email</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Name</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Age</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Gender</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Comment</B>
 </FONT>
</TD>
</TR>
<% dogrey=false %>
<% DO WHILE rs.EOF=FALSE %>
<% if dogrey=TRUE then %>
 <TR BGCOLOR=LIGHTGREY>
<% else %>
 <TR>
<% end if %>
<% dogrey=NOT(dogrey)%>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=FormatDateTime(rs("dateposted"), VBShortDate)%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("email"   )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("name"   )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("age"    )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("gender"  )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("comment"  )%>
   </FONT>
 </TD>
 </TR>
<%
rs.MoveNext
LOOP
%>
</TD></TR></TABLE>
</TABLE>
</CENTER>
<%
rs.close
set rs=nothing
conn.close
set conn=nothing
%>

For the sake of completeness, you should add <HTML>, <HEAD>, and <BODY> tags. To complete the retrieve script, these are the steps:

Add <HTML></HTML> tags.
Add a <HEAD></HEAD> section and put a <TITLE> in this section.
Add <BODY></BODY> tags.

Your final retrieve script should looks like Listing 44.

Listing 44 `retrieve.asp`: Final Listing with Complete HTML Tags

<%
'
' Step 1: Open up the database
'
set conn=server.createobject("adodb.connection")
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
     ";Driver={Microsoft Access Driver (*.mdb)};")
'
' Step 2: Send a SQL SELECT query to the database
'
set rs=conn.execute("SELECT * FROM GuestBook")
%>
<HTML>
<HEAD>
<TITLE>My Guest Book</TITLE>
</HEAD>
<BODY>
<CENTER>
<FONT FACE="Arial" STYLE="font-size:18pt" COLOR=MAROON>
<B>Cool People Who<BR>Signed My Guest Book</B>
</FONT>
<TABLE CELLSPACING=0 BORDER=1 BORDERCOLOR=MAROON><TR><TD>
<TABLE CELLSPACING=0>
<TR BGCOLOR=MAROON>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Date</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Email</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Name</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Age</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Gender</B>
 </FONT>
</TD>
<TD ALIGN=CENTER>
 <FONT FACE="Arial" STYLE="font-size:9pt" COLOR=TAN>
 <B>Comment</B>
 </FONT>
</TD>
</TR>
<% dogrey=false %>
<% DO WHILE rs.EOF=FALSE %>
<% if dogrey=TRUE then %>
 <TR BGCOLOR=LIGHTGREY>
<% else %>
 <TR>
<% end if %>
<% dogrey=NOT(dogrey)%>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=FormatDateTime(rs("dateposted"), VBShortDate)%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("email"   )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("name"   )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("age"    )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("gender"  )%>
   </FONT>
 </TD>
 <TD>
   <FONT FACE="Arial" STYLE="font-size:9pt">
   <%=rs("comment"  )%>
   </FONT>
 </TD>
 </TR>
<%
rs.MoveNext
LOOP
%>
</TD></TR></TABLE>
</TABLE>
</CENTER>
</BODY>
</HTML>
<%
rs.close
set rs=nothing
conn.close
set conn=nothing
%>

Upload this script to your web server and test it (see Figure 16).

Figure 16 Browser: All HTML tags included.

That's all, folks! Congratulations on completing the ASP tutorial, as well as the earlier tutorials. You should now know how to implement all the basic components of a database-driven, interactive web application (see Figure 17). And with a little practice, you'll be able to generalize this knowledge to a wide variety of different interactive applications.

Figure 17 What you know.

If you have any questions, please mail them to ProfessorF@informit.com. We'll try to answer as many as we can. Good luck!

< Back Page 8 of 8

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address