Home > Articles > Programming

  • Print
  • + Share This

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:

  1. Open the database.

  2. Send a SQL SELECT query to the database to retrieve the information from the table.

  3. View the results.

  4. 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:

  1. Create a new file.

  2. Add the lines in Listing 25 to your file.

  3. 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:

  1. Put a <TABLE> before the DO-WHILE statement.

  2. Put a <TR> after the DO-WHILE statement.

  3. Surround each field with a <TD></TD> pair.

  4. Put a </TR> before rs.MoveNext.

  5. 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:

  1. Create a variable called dogrey and set it to false, before the DO-WHILE loop.

  2. 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:

  1. Add a header row for your table.

  2. Apply your site's dark color as the background color for this row.

  3. 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:

  1. Add a title.

  2. 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:

  1. Close the result set.

  2. 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:

  1. Add <HTML></HTML> tags.

  2. Add a <HEAD></HEAD> section and put a <TITLE> in this section.

  3. 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!

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.