So, having talked about what ADO can do, let's see some code. In Chapter 1 we saw four simple examples of the ways that ASP can build dynamic content from a database. These included:
We'll look at the code for each one next.
The first simple example we looked at in Chapter 1 demonstrated the different ways that we can retrieve data from a database and insert it into a Web page using ADO and the ASP ActiveX Database Object component:
The code to create this page is simple enough. We use a SSI statement to insert the text file containing the database connection string, as discussed earlier. This simply sets an ASP variable named strConnect to the value of the complete connection string:
<%
strConnect = "SERVER=servername;DRIVER={SQL Server};" _
& "DATABASE=GlobalExamples;UID=examples;pwd=;"
%>
Here's the first part of the page, which creates the ADO Connection object named oConn and opens the database using the connection string defined in strConnect. We then create a SQL statement that will retrieve the book titles from the database. This statement is executed by the Connection object, which returns a recordset that we assign to the variable oRs:
<%@ LANGUAGE=VBSCRIPT %>
<HTML>
<HEAD><TITLE>Some of our books</TITLE></HEAD>
<BODY>
<H3>Some of our books</H3>
<!-- #include virtual="/common/connect.inc" -->
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open strConnect 'as set in include file connect.inc
strSQL = "SELECT tTitle FROM BookList ORDER BY kBookCode"
Set oRs = oConn.Execute(strSQL, lngRecs, 1) 'adCmdText
%>
...
Now we can loop through the recordset to build the <OL> ordered list, inserting the values from the records each time. After we've finished the list, we do the same for the <TABLE> and the two <SELECT> lists (using a different SIZE attribute to define the size—a value of 1 creates a drop-down list):
...
<OL> <!-- create the ordered list -->
<% Do While Not oRs.EOF %>
<LI><% = oRs.Fields("tTitle") %></LI>
<% oRs.MoveNext
Loop %>
</OL><P>
<table border = 1> <!-- create the table -->
<% oRs.MoveFirst
Do While Not oRs.EOF %>
<tr><td><% = oRs.Fields("tTitle") %></td></tr>
<% oRs.MoveNext
Loop %>
</table><P>
<select size=4> <!-- create list box -->
<% oRs.MoveFirst
Do While Not oRs.EOF %>
<option><% = oRs.Fields("tTitle") %>
<% oRs.MoveNext
Loop %>
</select><P>
<select size=1> <!-- create the drop-down list -->
<% oRs.MoveFirst
Do While Not oRs.EOF %>
<option><% = oRs.Fields("tTitle") %>
<% oRs.MoveNext
Loop %>
</select>
<%
Set oRs = Nothing
Set oConn = Nothing
%>
</body>
</html>
Notice how, each time we want to reuse the recordset, we have to move the current record pointer back to the start by calling the Recordset object's MoveFirst method. Once we're finished with the recordset and connection we destroy them by setting their reference variables to Nothing. In fact we don’t have to do this, because they are automatically destroyed, along with our other variables, when ASP finishes interpreting the page.
This example will return all the title entries in the table. Our screenshot shows only three, but in fact, if you run this example from our Web site, you'll see that there are more entries than this. To limit the number of entries that are displayed we could add a counter to the Do While loop, and simply exit from the loop when we've displayed the required number.
However, this is wasteful of resources, because we'll still retrieve all the entries from the database and build the recordset in ASP. Instead, we can use a stored procedure in the database to only return the required number of records. The procedure simply sets the ROWCOUNT internal variable to the number of records we want, executes the SELECT statement to get the recordset, and resets ROWCOUNT back to zero again:
SET ROWCOUNT 3
SELECT tTitle FROM BookList ORDER BY kBookCode
SET ROWCOUNT 0
This is far more efficient than assembling a recordset of all the records, although the ROWCOUNT variable may not work this way on systems other than SQL Server—check the documentation for your chosen data source.
In SQL Server 's Enterprise Manager utility we can create a stored procedure by typing it into the Query window (select SQL Query Tool from the Tools menu or click the SQL Query Tool button on the toolbar):
Now we can create the recordset object in ASP by calling the stored procedure rather than specifying the table name. The third parameter to the Execute method is a number that identifies what the first parameter actually refers to. If it's a text SQL statement we use 1, if it's the name of a table we use 2, and for a stored procedure we use 4:
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open strConnect
Set oRs = oConn.Execute("usp_getthreebooks", lngRecs, 4) 'adCmdStoredProc
%>Now, our recordset, and hence our lists, will only include the first three book entries in the table.
Note that we don’t have to specify the type of the first parameter, but it does speed up processing because SQL Server will attempt to execute the command against each type of resource in turn to see what the name we provide represents. There is a list of the parameters for the Execute command, and all the ADO constants, in the NT Option Pack documentation. Open the Microsoft ADO topic in the section Microsoft Data Access Components for a list of all the methods, and the constants for each one.
Earlier in this chapter, we saw a way of providing lists of links in a page using the content linking component. In Chapter 1, we showed you a page that contains links, but which draws the links from a database. It also demonstrates how we can build pages that use more than just simple text, even though it's extracted from a database. The example was the Links page on our Web Developer site:
This main body of the Links page is created from a database table that looks like this:
For each link, we've got its position in the list, the text to use for the hyperlink, the URL to open when a user clicks on a link, the file name of a graphic to display, and the comments that describe the site. To build the list of links, we use a very similar technique to the one you saw in the previous example.
The first step is to make our connection to the database, create the SQL string that will extract the records we want, and execute this query to return the recordset object in oRs. We determine the order that the links will be retrieved and displayed in using the nListOrder field, which simply contains the numerical 'position' in the order of the list for each link:
...
<!-- #include virtual="/common/connect.inc" -->
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open strConnect 'as set in include file connect.inc
strSQL = "SELECT tLinkHref,tLinkText,tLinkGraphic,tComments FROM Links " _
& "ORDER BY nListOrder;"
Set oRs = oConn.Execute(strSQL)
...Having got our recordset, we can loop through it and place the values into the page. Because each graphic can be of a different size, we build a separate table for each link rather than a single table containing them all. This has two advantages: it reduces the white-space around small graphics, and means that the browser will display the list progressively as it comes over the Web to the user's browser. If it was one big table, the browser would wait for it all to arrive before rendering any of it.
Some browsers can progressively render tables if you specify the absolute width of each column in the HTML. However, this takes away one of the advantages that browsers have over other display techniques, because the browser can no longer adjust the contents of the page to best fit the available window width.
You can see from the code below (we've highlighted the ASP parts) how we drop the values into the various tags. The trick is to remember to correctly pair up the double-quotes either side of the ASP variable tags; otherwise huge tracts of the page can just disappear when you view it. We're also checking to see if a graphic was specified by examining the value of the tLinkGraphic field first. This avoids the 'broken link' image appearing if not:
...
oRs.MoveFirst
Do While Not oRs.EOF
%>
<TABLE>
<TR>
<TD ALIGN="CENTER" VALIGN="TOP">
<% If Len(oRS.Fields("tLinkGraphic")) Then %>
<A HREF="<%=oRS.Fields("tLinkHref")%>" TARGET="_top">
<IMG SRC="link_images/<%=oRS.Fields("tLinkGraphic")%>"
HSPACE="5" VSPACE="5" BORDER=0></A>
<% End If %>
</TD>
<TD ALIGN="LEFT">
<FONT FACE="Arial" SIZE=2><B>
<A HREF="<%=oRS.Fields("tLinkHref")%>" TARGET="_top">
<%=oRS.Fields("tLinkText")%></A></B><BR></FONT>
<FONT FACE="Arial" SIZE=2><%=oRS.Fields("tComments")%></FONT>
</TD>
</TR>
</TABLE>
<%
oRs.MoveNext
Loop
%>
...The third page we described in Chapter 1 showed the covers of our Web Developer books; together with buttons that allow the viewer to open the samples list, get more information about the book, or order a copy. The last two of these 'button' links point to the main Wrox customer service Web site:
While the page doesn’t look like a list created from a database, it is. This is the table that it is created from—the first column is simply the last four characters of the ISBN for each book:
Note that ISBN codes (and hence our field values) are actually strings, not numbers. An ISBN can contain the character 'x' as the last checksum character in the string.
The code to create the page follows much the same lines as you've seen in the two previous examples, differing only in the way that it presents the information extracted from the table. However, now that you're familiar with creating connections and recordsets, we'll introduce a couple of other angles to think about, namely error checking and maintainability.
At the top of the page, as well as the SSI statement that inserts our now customary connection string, is a line that defines how many books will appear on a row in the page. When you come to build complex ASP pages, it's always worth thinking about what values you should 'hard-code' into the script, and whether you'll need to change them at some point in the future. In this case, we might decide to use smaller or larger images, and this could require more or fewer covers per row.
...
<% intBooksPerRow = 3 %>
...By defining a value for the number of rows as a variable at the top of the page, we only need to change it here when required, rather than having to search for all instances of it in the code at a later date—when we've probably forgotten how it works.
The main body of the code in the page creates our database connection and builds the recordset containing the book information. This time, we use a SQL statement that only selects books that have already been released, using the SQL syntax: WHERE dReleaseDate <= GETDATE(). The GETDATE() function is a built-in part of SQL Server's repertoire, and you may need to adjust it to suit if you use a different data source:
...
QUOT = Chr(34)
On Error Resume Next
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open strConnect 'value of strConnect defined in include file
strSQL = "SELECT kBookCode, dReleaseDate, tTitle FROM BookList " _
& "WHERE dReleaseDate <= GETDATE() ORDER BY dReleaseDate DESC"
Set oRs = oConn.Execute(strSQL, lngRecs, 1) 'adCmdText
If (oRs.EOF) Or (Err.Number > 0) Then
Response.Write "<B>Sorry, the database cannot be accessed at present." _
& "</B></FONT></BODY></HTML>"
Response.End
End If
...
Once we've got our recordset into oRS, we then check its EOF property, and check to see if the built-in VBScript Err object has flagged an error. If all is well, Err.Number will still be zero. If not, it will contain the non-zero error code. And if there are no records in the recordset, EOF will be True. In either case, we write a message to the page and then end execution of the script and send the current content to the user with the Response.End method.
If all is well, we can build our page. Again we loop through our recordset, but this time we also keep track of the current record number in a variable named intCell, incrementing it for each record we find. We also pull out the book code string from the current record and store it in a variable strBookCode:
...
oRs.MoveFirst
%>
<table WIDTH="100%" BORDER="0" CELLPADDING="10">
<tr>
<%
intCell = 0
Do While Not oRs.EOF
strBookCode = oRs.Fields("kBookCode")
intCell = intCell + 1
%>
...
For each record, we build the contents of a table cell that contains the book code string, the cover image (which is a hyperlink to the appropriate samples menu page), the book title, and under all this the three button images (which are also hyperlinks). You can see how the strBookCode variable is used repeatedly within the cell. The clever part is that for any book, we can navigate to the correct samples, information or order page from the book code. For example, the URL for the samples page for book code 0685 is /books/0685/0685.asp (a good indication of why you need to structure your site in a logical way from the beginning):
...
<td ALIGN="CENTER" VALIGN="TOP">
<font FACE="Arial" SIZE="1"><b>Code: <%=strBookCode%></b></font><br>
<a HREF="/books/<%=strBookCode%>/<%=strBookCode%>.asp">
<img SRC="/images/<%=strBookCode%>.gif" BORDER="0" VSPACE="5"></a><br>
<font FACE="Arial" SIZE="2"><b><%=oRs.Fields("tTitle")%></font><br>
<a HREF="/books/<%=strBookCode%>/<%=strBookCode%>.asp">
<img SRC="/images/samples.gif" BORDER="0" HSPACE="5" VSPACE="4"></a><br>
<a HREF="store.asp?Code=<%=strBookCode%>" TARGET="_top">
<img SRC="/images/moreinfo.gif" BORDER="0" HSPACE="4" VSPACE="2"></a><br>
<a HREF="order.asp?Code=<%=strBookCode%>" TARGET="_top">
<img SRC="/images/order.gif" BORDER="0" HSPACE="4" VSPACE="2"></a><br>
</td>
...
This code is repeated for each book, adding a cell to the current row of the table that we started earlier in the page. The variable intCell holds the current record (cell) number, so we need to check each time to see if we have reached what should be the end of a row. The value of the expression intCell Mod intBooksPerRow will be zero if the current record number is exactly divisible by the number of books per row, and if so we can add closing and opening table row tags to start a new row in the table:
...
<% If (intCell Mod intBooksPerRow) = 0 Then 'end of row %>
</tr><tr>
<% End If %>
...The remainder of the code then closes the loop so that execution moves to the next record. Once we've done all the books in the recordset, we just clear up by closing the recordset, ending the table row, and closing the table:
...
<%
oRs.MoveNext
Response.Flush
Loop
oRs.Close
%>
</tr>
</table>The final example we showed in Chapter 1 was the Motor Support pages. This uses a simple database table containing a list of support topics. For each one, there is the topic ID, title, classification, and the text of the topic:
Using this table, we can generate a 'contents' page, and a page for each topic:
To create the contents page, we can retrieve the records for all the topics and display them as hyperlinks—as we did in the previous examples. The major difference here is that we want to break them down into classifications, and display each list of topics within the appropriate classification.
We could do this as a two-stage process, fetching a list of classifications first and then the list of links for each one in turn. However, this would involve several accesses to the database, and the creation of several recordsets—in our case seven, but in 'real life' probably a lot more. This is not the most efficient way to use the server's resources, and would probably provide a relatively long delay before any of the page could be sent to the viewer.
Instead, we retrieve all the records and then create the classification headings as we go through the recordset. To be able to do this, we need to order (sort) the records by classification, then by topic title, with a SQL statement that looks like this:
SELECT TopicID, Classification, TopicTitle FROM MotorSupport
ORDER BY Classification, TopicTitleHere's the part of the page containing the code that creates our recordset:
...
<!-- #include virtual="/common/connect.inc" -->
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open strConnect 'as set in include file connect.inc
strSQL = "SELECT TopicID, Classification, TopicTitle FROM MotorSupport " _
& "ORDER BY Classification, TopicTitle"
Set oRs = oConn.Execute(strSQL, lngRecs, 1) 'adCmdText
%>
...
Now, we can create the list of topics, using nested <UL> lists. To create the classification headings, we store the current classification in a variable strCurrentClass and then check to see when it changes. When it does, in other words when we get to the first topic in the next classification, we insert the new classification heading. To make this process work, we start with strCurrentClass as an empty string so that it creates the first topic:
<H4>The following support topics are available:</H4>
<UL>
<%
QUOT = Chr(34) 'double quotation marks
CRLF = Chr(13) & Chr(10)
strCurrentClass = ""
Do While Not oRs.EOF
strNewClass = oRs.Fields("Classification")
strTopicTitle = oRs.Fields("TopicTitle")
strTopicID = oRs.Fields("TopicID")
If strNewClass <> strCurrentClass Then
If strCurrentClass <> "" Then Response.Write "</UL>" & CRLF
Response.Write "<LI><B>" & strNewClass & "</B></LI>" & CRLF & "<UL>"
strCurrentClass = strNewClass
End If
Response.Write "<LI><B><A HREF=" & QUOT & "_show_details.asp?topicid=" _
& strTopicID & QUOT & ">" & strTopicTitle & "</A></B></LI>" & CRLF
oRs.MoveNext
Loop
%>
</UL>
...
When the current and next topics are in different classifications, i.e. strNewClass <> strCurrentClass, we need to close the current nested <UL> list (except when this is the first classification), write the classification heading, and start a new nested <UL> list.
You may be wondering why we included carriage returns (using the CRLF variable we defined at the start of the code), when they don’t affect the contents of the rendered page in the browser. It's simply to make it easier to read and debug the code. The Response.Write method doesn’t put carriage returns into the page, so when we view the source in the browser it's one long string, and can be impossible to assimilate. By adding carriage returns in appropriate places, we make our dynamically-generated content look like a normal HTML page's source.
Now that we've got a contents page, all we need is an ASP page (named _show_details.asp) that will create the appropriate topic page dynamically when the user clicks on a link in the main contents page. Our contents page provides the topic ID in the query string of the hyperlink. For example, the hyperlink to the first topic is _show_details.asp?topicid=34, so we just select the record that has that topic ID and drop the values of its fields into the page:
...
strSQL = "SELECT * FROM MotorSupport WHERE TopicID=" _
& Request.QueryString("topicid")
Set oRs = oConn.Execute(strSQL, lngRecs, 1) 'adCmdText
%>
<H4>Motor Support Topic No.#<% = oRs.Fields("TopicID") %></H4>
<H3><% = oRs.Fields("TopicTitle") %></H3>
<P><% = oRs.Fields("TopicContent") %></P>
<A HREF="motor_support.asp"><B>Back to Contents</B></A>
...The Motor Support example also has a rather strange variation where each topic is shown in a separate frame:
We can create framesets like this easily enough from a database. What we need is a list of just the classifications, and this can be achieved using the DISTINCT keyword in our SQL query:
SELECT DISTINCT Classification FROM MotorSupport ORDER BY Classification
Having got a list of the classifications, we can write the complete frameset dynamically. To specify the number of rows (or columns if you orient the frames that way), we use an asterisk for each frame in the opening HTML <FRAMESET> tag. To get the syntax of the tag right, we start at the second record, write an asterisk and a comma for each frame, and finish with just an asterisk:
...
Response.Write "<FRAMESET ROWS=" & QUOT
oRs.MoveFirst
oRs.MoveNext
Do While Not oRs.EOF
Response.Write "*,"
oRs.MoveNext
Loop
Response.Write "*" & QUOT & ">"
...
Then we go back to the start of the recordset again and create each <FRAME> tag, putting the classification in the query string of the SRC attribute to identify it:
...
oRs.MoveFirst
Do While Not oRs.EOF
strClass = oRs.Fields("Classification")
%>
<FRAME SRC="_topic_frame.asp?class=<% = oRs.Fields("Classification") %>">
<%
oRs.MoveNext
Loop
%>
</FRAMESET>
...The resulting HTML code, when you select View | Source in the browser, looks like this:
...
<FRAMESET ROWS="*,*,*,*,*,*">
<FRAME SRC="_topic_frame.asp?class=Bodywork and Trim">
<FRAME SRC="_topic_frame.asp?class=Braking System">
<FRAME SRC="_topic_frame.asp?class=Electrical System">
<FRAME SRC="_topic_frame.asp?class=Fuel System">
<FRAME SRC="_topic_frame.asp?class=Ignition System">
<FRAME SRC="_topic_frame.asp?class=Wheels and Tires">
</FRAMESET>
...
The SRC source file for each frame is another ASP page, named _topic_frame.asp. This just has to use the classification passed in the query string to pull the relevant topics out of the database, and drop them into the page. This time, of course, each page will contain multiple topics—all the ones for that classification:
...
<% strClass = Request.QueryString("class") %>
Classification: <B><% = strClass %></B><P>
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open strConnect
strSQL = "SELECT * FROM MotorSupport WHERE Classification='" & strClass & "'"
Set oRs = oConn.Execute(strSQL, lngRecs, 1) 'adCmdText
Do While Not oRs.EOF
%>
<B>Topic: <% = oRs.Fields("TopicTitle") %></B><BR>
<% = oRs.Fields("TopicContent") %><P>
<%
oRs.MoveNext
Loop
%>
...A serious consideration is that this kind of page is hugely inefficient, hitting the database with seven queries in our simple example. For 100 topics, it would need 101 queries, and you need to take this kind of issue into account when designing database-driven Web pages.