ASP Version

In this version, the resources listed under the Tools tab of are stored in a mySQL database, rather than in an XML file. The ASP script establishes the connection with the database, checks the connection, performs the query, and returns the results to be combined with a static XML file, transformed by the XSL, and formatted with CSS to display the Web page.

The resulting Web page looks exactly like the Tools page on this Web site; the only difference being that the names, descriptions, and URLs of the listed resources come from a database.

' ******************************************************************************************************************
' Copyright 2006 Center for Technology in Government.
' The Center grants permission to use and modify this software code free of charge provided  
' this copyright notice is included. By using this code you agree to indemnify the Center from 
' any liability that might arise from its use. Selling the code is expressly forbidden.
' *******************************************************************************************************************

Dim strConnect, db_connect  ,rs, strSQL,refNode, sect1, ulink, para, url, value,title

' *******************************************************************************
'   Initial information for database connection and xml translating
' *******************************************************************************
strConnect = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=databasename; 
 UID=username;PASSWORD=password; OPTION=3"
strSQL = "SELECT * FROM tools"

' ******************************************************************************
'     Connect to database and send out the SQL query
' ******************************************************************************
 Set db_connect = Server.CreateObject("ADODB.Connection")
' Execute SQL statement
Set rs = db_connect.Execute(strSQL)

' ****************************************************************************
'  Open XML file, only has title info, no data
'  Get data from database 
'  Convert database data to XML format, 
'  Append new data to XML file, processed only in memory 
'  Generated xml data format must conform to existing XSL sheet
'  We can use existing XSL file, nothing needs to be changed
'  New xml data only exist in memory 
'  ******************************************************************************  
Set xmlDoc = Server.CreateObject("MSXML2.FreeThreadedDOMDocument.3.0")
xmlDoc.async = false
xmlDoc.load (Server.MapPath( "tools/tools_title.xml"))

' Get the insert pointer for new data
Set ElemList = xmlDoc.getElementsByTagName("chapter")
Set refNode = ElemList.item(0)

Do while not rs.eof   ' Do until it reaches end of db
    '  Create the new element
    Set sect1 = xmlDoc.createElement("sect1")
    '  Insert the new element
    Set sect1 = refNode.appendChild(sect1)    
    Set title = xmlDoc.createElement("title")
    Set title = sect1.appendChild(title)
        Set ulink = xmlDoc.createElement("ulink")
        Set ulink = title.appendChild(ulink)
        ulink.setAttribute "url", rs("url") 
             Set value = xmlDoc.createTextNode( rs("title") )
             Set value = ulink.appendChild(value)
    Set para = xmlDoc.createElement("para")
    Set para = sect1.appendChild(para)
         Set value = xmlDoc.createTextNode( rs("description") & "(" )
         Set value = para.appendChild(value)
         Set ulink = xmlDoc.createElement("ulink")
         Set ulink = para.appendChild(ulink)
         ulink.setAttribute "url", rs("url")  
             Set value = xmlDoc.createTextNode( rs("url") )
             Set value = ulink.appendChild(value)
         Set value = xmlDoc.createTextNode(")")
         Set value = para.appendChild(value)      
rs.MoveNext  ' Next record

' Close database connection
  Set rs = Nothing  'rsvariable released
  Set db_connect = Nothing

'   ******************************************************************
'  Translate xml data into html 
'  Send back to clent browser
'  Use existingXSL file 
'  Processed in Memory 
'  *******************************************************************
Set xslDoc = Server.CreateObject("MSXML2.FreeThreadedDOMDocument.3.0")
xslDoc.async = false
xslDoc.load (Server.MapPath("level1.xsl"))

Set template = Server.CreateObject("MSXML2.XSLTemplate")
template.stylesheet = xslDoc
set processor = template.createProcessor()
processor.input = xmlDoc
processor.addParameter "extension", ".asp"
processor.addParameter "name", "Tools"
Response.write (processor.output) 


Valid values would need to be inserted for your database connection. Otherwise, this script works much like the scripts in the Environment section of this Web site.