home / articles / frontpage

FrontPage 98 with ASP and ADO

Last week I wrote an article which began with a simplified explanation of ASP/ADO (yes, I know it didn't seem simplified, but take my word for it, it was!). I finished the article with a "Simple ASP ADO SQL Query, which I've reproduced below for your review. Today, I'm going to talk to you about specifically using FrontPage 98 to create database-connective web pages.

A Simple ASP ADO SQL Query (in review)

'** Create a RecordSet Object
Set rs = Server.CreateObject("ADODB.RecordSet")
'** Get your input parameters, and optionally, place them in variables
param = Request.Form("lastname")
'** Create a query string by concatenating the parameters into an SQL statement
q = "SELECT * FROM personnel WHERE lastname LIKE '" & param & "'"
'** Open the RecordSet
rs.Open q, "DSN=mydsn;"

'** Make sure the RecordSet isn't empty
if NOT rs.EOF then
'** loop through the RecorSet
     while NOT rs.EOF
'** Write your output to the page
          Response.Write rs("firstname") & " " & rs("lastname") & "<BR>"
          rs.MoveNext
     wend
end if

Now, this example doesn't use any HTML (hardly), but with FrontPage 98, you can combine HTML and server-side scripting tags to create a huge variety of effects which can be dynamically generated according to database values.

Server-Side Scripts Mixed with HTML

One of the coolest things about these server-side scripts is the ability to place them around HTML code. For example, you can do the above routine with a table, instead of simply writing the database column values in successive lines of text. Here's a real-life example from http://www.sandiegojobs.com, a web which I am continually working on:

What you see is a table with server-side scripting tags in the cells. These scripting tags have script in them wihc reads, for example: "=rs("lastupdated")," in the "Date Last Updated" cell. You could also use the code "Response.Write rs("lastupdated"), which is necessary in some cases, but in many cases, this "shorthand" code is fine.

What you don't see is the server-side scripting tags which surround the table row. These tags are similar to the ones in my first example above, which loop through the RecordSet ("rs"), and execute everything between them each time they loop through. Here's what it looks like in a browser:

Finally, here's the code which shows the ASP scripting tags mixed in with the HTML code. I've highlighted the scripting tags. Note the "while NOT rs.EOF," "rs.MoveNext," and "wend" scripts which surround the "second" table row:

<table border="2" cellspacing="0" width="100%" bordercolordark="#000080"
bordercolorlight="#8B86F7">
<tr>
<td align="center" valign="bottom" width="5%">Job #</td>
<td align="center" valign="bottom" width="50%">Category /<br>
Job Title</td>
<td align="center" valign="bottom" width="15%">Date Last Updated</td>
<td align="center" valign="bottom" width="10%">Days Before Expiration</td>
<td align="center" valign="bottom" width="10%">Number of Extensions</td>
<td align="center" valign="bottom" width="10%">Extend for 30 Days</td>
</tr>
<%
while NOT rs.EOF
%>
<tr>
<td width="5%"><%Response.Write "<a href='jobpost.asp?number=" & rs("number") & "'>" & rs("number") & "</a>"%>
</td>
<td width="50%"><%Response.Write "<a href='jobpost.asp?number=" & rs("number") & "'>" & rs("category")
& "<BR>" & rs("title") & "</a>"%>
</td>
<td align="center" width="15%"><%=rs("lastupdated")%>
</td>
<td align="center" width="10%"><%=rs("daysold")%>
</td>
<td align="center" width="10%"><%=rs("extended")
%>
</td>
<td align="center" width="10%"><form method="POST">
<input type="hidden" name="number" value="<%=rs("number")%>"><input type="hidden" name="password" value="<%=rs("password")%>"><p><br>
<input type="checkbox" name="C1" value="ON"> </p>
</form>
</td>
</tr>
<%
rs.MoveNext
wend
%>
</table>

But the point which I want to emphasize most is that 90% of these tags were created in the WYSIWYG editor, by using the Insert|Advanced|Script command (or the "Insert Script" icon on the toolbar).

You can also use ASP/ADO to dynamically change the contents of your page, depending upon the results of your queries. For example, if there are no records returned, you might not want to display the table at all, but a simple message stating that no records were returned. You can do this by using if/then/else/end if scripting tags. Here's an example from the same page:

The first server-side script tag you see contains the query code, and the second contains a simple if/then statement: "if rs.EOF then." EOF is the property of being at the "End Of File," or the end of the RecordSet. this tag is followed by the message informing the user that there were no records returned. The second tag contains the simgle code "else," which signifies that if there are records returned, whatever comes between that tag and the final "end if" tag should be executed/written to the browser. The final tag is way at the bottom of the page.

There are any number of things which you can do to dynamically generate your pages based upon the data returned (or not). Use your imagination to think of all the possibilities!

FrontPage ASP/ADO Step By Step

So, let me review the steps which make creating ADO ASP pages simple with Frontpage 98:

1. Create your basic page with the FrontPage Editor. Lay it out in anticipation of the interaction it will take on with the database results. Make sure that you have planned for every possible circumstance (such as no records returned). Put in as much of the HTML as you can think of, making your layout visually.
2. Create your "query" script(s). Usually these are at the top of the page. They can even be at the very top of the page, before the HTML code. This is the code which creates your ADO objects, and executes whatever functions you initially want to execute. Note: You can execute as many queries in a single ASP page as you need to. For example, I have used pages which dynamically populate a drop-down list box with data from a field of the database, using one query, and populate the rest of the page from another (or more than one other) query.
3. Add in your if/else conditional scripts and your "database column value" scripts, loops etc. In most cases, these can be done using the Insert Script commands. In some cases you may have to edit the HTML directly.
4. Finally, test, test, test! Debug, debug, debug! Make your revisions, and fine-tune your page until it runs like a Mercedes.

This, of course, has been a rather simple introduction to ASP and ADO with FrontPage 98, but it contains the essential information you need to get started.

So, get started!

Author: Kevin Spencer
Date: 01/02/98

More articles about Microsoft FrontPage
More articles by Kevin Spencer
Author Biography