Combining XML, ADO, and ASP

Want to see how you can combine XML, ADO, and ASP? This project, outlined in our Web Development Zone TechMail, uses all three to put together a phone list program.

Editor’s note: This article originally appeared in TechRepublic’s Web Development Zone TechMail. Subscribe, and you’ll receive information on Web-development-related projects and trends.

This week, we’ll use Extensible Markup Language (XML) and ActiveX Data Objects (ADO) with Active Server Pages (ASP). In the latest version of Internet Information Server (IIS 5) and ADO (2.6), we now have the ability to persist the data from the recordset object as XML and save it directly to the ASP response object.

This Phonelist.asp project was started by creating the underlying Access database. The idea was to develop a simple data set that would be useful for any company. Step one was to name the table contacts and add four fields: FirstName, LastName, Location, and Phone. Be aware that XML and Extensible Stylesheet Language (XSL) are case-sensitive, so you will want to be sure that your field names match the definitions in your XSL style sheet.

Third in a series
This article from TechRepublic’s Web Development Zone describes issues related to integrating XML and Active Server Pages (ASP). The first article discussed XML and XSL integration, while the second installment discussed displaying XML and XSL for Netscape or handheld users.

The ASP for this project serves only one primary function: to retrieve and return the data as XML. First, we need to specify the content type as text/xml. Second, add the XML header information and add the style sheet link information for formatting the XML data. Third, create an ADO recordset object for retrieving the data. This is where the simplicity of XML technology is so handy. Instead of looping throughout the recordset and having to intermix HTML with scripting code to format and display the data within the ASP, we simply open the recordset and save the data as XML to the response object.

As has been written in previous TechMails, the ease with which developers can format and display data with XSL is truly compelling. The simpler that you make a process, the less chance exists for problems to occur.

More information and the download for the 2.6 release of the ADO MDAC can be found at the Microsoft Universal Access Data Web site.

Here is the code for the Phonelist.asp program.
<%@ Language=VBScript %>
Response.ContentType = "text/xml"
Response.Write "<?xml version=""1.0"" ?>" & vbcrlf
Response.Write "<?xml-stylesheet type=""text/xsl"" " & _
"href="#"PhoneList.XSL"" ?>" & vbcrlf  & vbcrlf

set rsContacts = Server.CreateObject("ADODB.Recordset")
dataConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=Admin;Data Source=D:\Inetpub\wwwroot\ASP_Utils\PhoneList.mdb;" & _
"Mode=Share Deny None;Extended Properties="""";" & _
"Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";" & _
"Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;" & _
"Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;" & _
"Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";" & _
"Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;" & _
"Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:" & _
"Compact Without Replica Repair=False;Jet OLEDB:SFP=False;User Id=Admin;"
sql = "SELECT LastName + ', ' + FirstName AS Name, " & _
"Phone, Location FROM Contacts ORDER BY LastName"

rsContacts.ActiveConnection = dataConnStr
rsContacts.Source = sql
rsContacts.CursorLocation = 3'Use Client side cursor
rsContacts.ActiveConnection = nothing

rsContacts.Save response, 1 'adPersistXML
Set rsContacts = Nothing

The Phonelist.xsl file source:
<HTML xmlns:xsl="">
<TITLE>Company Phone List</TITLE>
.Table {background:black}
.TableHead {font:bold; color:white; background:blue}
.ColumnHeader {font:normal 'Verdana' bold; color:white; background:blue}
.TableRow {font:x-small 'Verdana'; color:black; background:#CCCCCC}
<DIV >
<TABLE CLASS="Table" ID="RecordTable">
<TR CLASS="TableHead">
<TH CLASS="ColumnHeader">Name</TH>
<TH CLASS="ColumnHeader">Location</TH>
<TH CLASS="ColumnHeader">Phone</TH>
<xsl:for-each select="xml/rs:data/z:row">
<TR CLASS="TableRow">
<TD><xsl:value-of select="@Name" /></TD>
TD align="center"><xsl:value-of select="@Location" /></TD>
<TD><xsl:value-of select="@Phone" /></TD>

For more information on XML, visit MSDN Online—XML Developer Center.

Editor's Picks