Automating Web Processes and Workflow with Dynamic Content

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Kevin Spencer
TAKempis Internet Programming

September 2002

Introduction

In my previous article, Automating Web Processes and Workflow with Databases, I discussed the advantages of using a database back end with Active Server Pages (ASP) and ActiveX® Data Objects (ADO) to automate some of the processes that tend to consume hours of time, such as updating content and minimizing the number of pages needed to maintain a Web site. A database has the capabilities for storing, updating, manipulating, and organizing data, which can be used in your Web site to provide dynamic content in your pages.

In this article, we want to go more deeply into the use of dynamic content, which is possibly the most powerful tool for automation of your Web site.

**Note   **For security information about working with Web sites and Web servers, see FrontPage Security Best Practices.

Contents

Overview of Dynamic Content
Global Content
Using Dynamic Content
Using FrontPage with ASP to Create Dynamic Content
Getting Started
Sample Application: Uncle Chutney's Journal
Functional Analysis
Database Tier—Database Design
Creating the Article Display Page
Creating the Article Creation Page
Future Enhancements
Conclusion

Overview of Dynamic Content

Dynamic content is simply HTML content that is generated at run time by a server-side technology such as ASP. Note that this is not limited to the content seen in the browser but includes any HTML that may be in the resulting document, including client-side scripting, headers, style sheets, Extensible Markup Language (XML), OBJECT tags, and so forth. Dynamic content has many uses that can save you time and expense in maintaining your Web site:

  • Automating Web site maintenance. If you can identify pieces of data that are used in multiple pages of your Web site, you can store this data in a database. When you need to change the data across all the pages, you simply change it in the database. Names, e-mail addresses, and phone numbers are some of the typical types of data for which this can be useful, although there are many other possibilities. For example, certain kinds of layout information that is used in many pages of your Web site (such as font types, sizes, and colors) can be stored in a database and used to change the actual layout of many pages through one source.
  • Reducing the number of files to maintain. Certain kinds of information lend themselves to a database back-end solution. For example, if you have an online product catalog, you might want to put the product information in a database and, rather than having a separate page for each product, use a single ASP page to display each product. Not only does this cut down on the amount of maintenance time that you must spend when making changes, but it also ensures consistency in the layout of the catalog pages.
  • Organizing databases. A database query is capable of returning a sorted result set, which is useful for organizing the data that you display in your Web pages. You also can build search engines that quickly and easily fetch the data that a user needs. A file system is not nearly as useful for organizing and working with your data.

For this discussion, we will divide the subject of dynamic content into two general areas:

  • Web page content. Dynamic content in individual pages. Some examples of this sort of dynamic content include product catalog pages, discussion forums, and pages that display information (such as articles like this one!).
  • Global (configuration) content. Dynamic content applied globally. A couple of intrinsic ASP objects are quite useful in this area: Application and Session objects (collections). The Application object is created when the first request for an ASP page is received by the Web server after it is started, and remains until the Web server is restarted. It is useful for storing data that doesn't change very often and is meant to be globally available to all users for all pages in the site. The Session object is created when a user logs onto the server by requesting a page. A unique Session object is created for each unique user session, and it remains until that user leaves that domain. The Session object is useful for storing data that is unique to the individual user but global to all pages in that domain that the user views.

Global Content

The Global.asa file, in the root of your Web site, can contain a number of Web Application event handler Subs including the Application_OnStart, Session_OnStart, and Session_OnEnd Subs, which are the most frequently used. You can initialize Application variables in the Application_OnStart Sub, initialize Session variables in the Session_OnStart Sub, and do any cleanup required in the Session_OnEnd Sub. However, you cannot do any database operations in the Session_OnEnd Sub.

For global layout and configuration settings, use the Application object. You can fetch stored layout and configuration settings from a database and store the values as variables in the Application Collection. Similarly, any configuration settings that are personalized for each user can be stored in a database and assigned to Session variables in the Session_OnStart Sub.

The following is an example of a Global.asa file that I copied from my Web site at www.takempis.com, which sets certain global values.

The two global variables—Application("totalvisitors") and Application("visitorsonline")—are used to keep track of users. Application("totalvisitors") keeps track of the total number of Web site visitors during the day. Application("visitorsonline") keeps track of the number of current user sessions.

Sub Application_OnStart
   Dim strSQL, objRS, objConn

   ' ** Initialize Application stats variables
   Application("totalvisitors") = 0
   Application("visitorsonline") = 0

The Connection String is used by all ASP applications that use a certain Microsoft SQL Server™ database.

   Application("Connection_String") = 
      "Provider=sqloledb;Network=dbmssocn;" &_
      "Data Source=XXX.XXX.XXX.XXX;" &_
      "Initial Catalog=XXXXXXXX;" &_
      "User Id=XXXXXXXX;Password=XXXXX;"

The global variables are used throughout a number of different ASP pages.

   Application("company") = "XXXX XXXXXX XXXXX"
   Application("companyemail") = "XXXXXXXX@XXX.XXX" 
   Application("smtpserver") = "XXX.XXX.XXX.XXX"
   Application("contactname") = "XXXXXXX XXXXXXXX"
   Application("expiration") = 21
   Application("adminname") = "XXXXXXXX"
   Application("adminpassword") = "XXXXXXX

The following block of code fetches data used to populate a drop-down list box on a certain page and stores it in an application-level multidimensional array. This way, the drop-down list box page doesn't have to query the database for this data every time that the page is viewed.

   Set objConn = Server.CreateObject("ADODB.Connection")
   objConn.Open Application("Connection_String")
   strSQL = "SELECT company FROM jobs WHERE approved=1 " &_
      "AND deleted=0 GROUP BY company ORDER BY company"
   Set objRS = objConn.Execute(strSQL)
   if NOT objRS.EOF then
      Application("arycompany") = objRS.GetRows()
      Application("rows") = UBound(Application("arycompany"), 2)
   else
      Application("rows") = 0
   end if
   objRS.Close
   objConn.Close
   Set objRS = Nothing
   Set objConn = Nothing

The following code tracks user sessions. It increments Application("totalvisitors") and Application("visitorsonline") at the start of the session.

End Sub

Sub Session_OnStart

   ' ** Add to total visitors and visitors online
   Application.Lock
   Application("visitorsonline") = Application("visitorsonline") + 1
   Application("totalvisitors") = Application("totalvisitors") + 1
   Application.Unlock

The following code fires at the end of the user session, decrementing the Application("visitorsonline") variable.

   ' ** If this is the first Session of the day, reset all variables
   if Application("lastupdated") <> d then
      Application.Lock
      Application("lastupdated") = Date
      Application("totalvisitors") = 1
      Application("visitorsonline") = 1
      Application.Unlock
   end if
End Sub

Sub Session_OnEnd

   ' ** Remove this visitor from visitors online
   Application.Lock
   if Application("visitorsonline") > 1 then
      Application("visitorsonline") = Application("visitorsonline") – 1
   Application.Unlock
End Sub

Using Dynamic Content

The first thing to do is to identify how your Web site can benefit from the use of dynamic content. This can be accomplished by analyzing the functional requirements of your pages and identifying candidates for automation:

  • Pages that share functionality. These are pages that have similar functionality and differ only in content. Online catalog pages, news article pages, and banner ads would fall into this category.

  • Pages that share layout/configuration elements. If you use FrontPage, you can create Templates, Shared Borders, and other FrontPage components that can enable you to create a consistent look and feel to your entire Web site. However, in some cases, these components are not practical to use. For example, to make changes to the layout of the Web site, you would need the FrontPage client program to make changes to FrontPage components—but I have developed Web sites for customers who don't have FrontPage. By storing layout settings in a database, I built ASP pages that enabled customers to make layout changes to their site using a browser.

    Also, the FrontPage components are not as flexible as you might want them to be in terms of global layout settings. An alternative is to create a dynamic style sheet (ASP) for use in your pages, store the style settings in a database, include the style sheet in all of your pages with a server-side include, and fetch the style settings from the database when the page is loaded (or fetch them once in the Global.asa file and pull the data from the application object). You then can change the style settings for the entire Web site by changing the data in the database, even to the point of creating a Web interface for doing so.

  • Information that could be better organized. Let's say you have a product catalog, and all your products are in individual pages. How is the product information organized? Well, it really isn't. By putting the product information into a database and using a single ASP page to display the data, the product catalog can be sorted in any way you desire. You also can build a search engine to query the database and return a list of matching records, all ordered neatly in whatever fashion you choose. Products could be grouped together by category, date, or other meaningful arrangement, without having to make any changes to the page that displays them or to the data itself. And when you want to make a change to the layout or design of the catalog, you have to change only one page.

Using FrontPage with ASP to Create Dynamic Content

FrontPage has gotten an undeserved bad rap as an ASP development toolkit, mostly as a result of poor ASP coding practices. I have found FrontPage to be in many ways the best toolkit for developing ASP, primarily because of its capability for developing HTML quickly and easily in its WYSIWYG Editor. This, combined with its unmatched functionality for managing Web sites, has made FrontPage my software of choice for ASP development ever since ASP came out.

Getting Started

In my previous article, Automating Web Processes and Workflow with Databases, I discussed briefly the principles you need to employ when using FrontPage to develop ASP. Because FrontPage uses the HTML object model to render HTML in WYSIWYG mode (Normal View), the HTML in an ASP page must be "correct" without the ASP code in order for FrontPage to be able to render the HTML in Normal View and not change any of your code.

As it turns out, this forces us to develop better coding practices for ASP with FrontPage, by separating server-side code from HTML content as much as possible. Separating code from content is one of the most important practices that you can master as an ASP developer, because it makes the code easier to maintain, extend, and modify.

Think of an ASP page as a three-tier application, with a database tier, a business logic tier (the ASP code), and an interface tier (the HTML). With most ASP pages, the scripting and HTML reside in the same page, but you can separate them to a large extent by putting most of your execution code, along with global variable definitions and subs/functions, above the top <html> tag in your page.

This resolves to a three-step approach to developing your ASP applications:

  1. Create your database and database objects (tables, stored procedures, and so on).
  2. Create your interface in FrontPage Editor as HTML, in Normal View.
  3. Add ASP scripting to your interface in FrontPage Editor, in HTML View.

Sample Application: Uncle Chutney's Journal

In looking for an example of a database-driven ASP application for this article, I immediately thought of my own Web site. About two years ago, I started writing an online journal, which I called "Uncle Chutney's Journal." Uncle Chutney is an alter ego that I created a couple of years ago, who now maintains my entire Web site. The idea of the journal was a venue for writing about anything and everything—whatever Uncle Chutney felt like writing about, whenever he felt like writing. Uncle Chutney has written about everything from Microsoft .NET development to the nature of the Universe in that journal.

At the time, I just wanted a venue for writing so I didn't plan too much. I created a page and added to it whenever Uncle Chutney wrote a new entry. When a page got too long, I renamed it, created a new page, and put a link to the previous one in the new one.

As time went by, the number of pages and lack of organization made it difficult to navigate through the journal and find articles. I decided that this journal would make an excellent candidate for a database-driven ASP application.

Functional Analysis

On examining the journal it seemed that the first functionality to add should be that of displaying the articles from a database. The second should be that of being able to create new articles easily and then add them to the database. Therefore, I planned on designing two pages:

  • An Article Display page
  • An Article Creation page

Database Tier—Database Design

For the database, I chose to use a Microsoft Access database that was already in my Web site, which I use for my discussion groups. All I would need to do is to add a new table to it. I named the new table "journal" and created four columns in it:

Aa140044.fp_autowithdynamiccontent_01(en-us,office.10).gif

Creating the Article Display Page

I decided that the Article Display page should display one article at a time, with a link to the next or previous article, in order of date posted (the DatePosted field in the table) for now. With this database back-end, I would have a lot more flexibility, including the ability to add search engine for enhanced searchability, modify the navigation, etc., if I chose. For the purpose of organization, I decided to put these pages into a separate folder in my Web, which I called "journal." I wanted the Article Display page to look the same as it did originally on the Web site, so I made a copy of one of the existing article pages, removed all the article text from it, and saved it as /journal/default.asp.

Aa140044.fp_autowithdynamiccontent_02(en-us,office.10).gif

Now I was ready to add the ASP code (business logic) to the top of the page (above the HTML interface). Because this page is requested through a query string, we want to turn off browser caching. Many browsers cache ASP pages that are not generated as a result of a form submission. If the content of the page changes, we want to make sure the browser gets the latest content, and not previously cached content.

<%Response.Expires = 0

Variable definitions:

Dim intID, dteDatePosted, strTitle, strBody, blnNext, blnPrev
Dim strSQL, objRS, objConn, ConnectionString

Variable initializations—intID, dteDatePosted, strBody, and strTitle—are used to store the data from the record that we want to display. By putting this data into global variables, we can execute our ADO operations and close our ADO objects prior to executing the HTML in the page. All we need to put into the HTML are the values of the global variables.

' ** initialize variables to hold RecordSet values
intID = -1
dteDatePosted = Now
strBody = ""
strTitle = ""

The blnNext and blnPrev variables are used to determine whether to include a Next and a Previous hyperlink on the page.

blnNext = False
blnPrev = False
ConnectionString = "Provider= Microsoft.Jet.OLEDB.4.0; Data Source=" &_
   Server.MapPath("../_private/discussion.mdb")
' ** Open Connection
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open ConnectionString

We check Request.QueryString("ID") to see if this is the initial display of the page. If so, we want to display the first record, which would be the latest (the article with the greatest DatePosted value).

' ** Initial Load of page - display last article posted
if Request.QueryString("ID") = "" then

To determine whether there are any previous records, we select two records. If we get only one back, we know that this is the earliest record in the database.

   strSQL = "SELECT TOP 2 id, dateposted, title, body " &_
"FROM journal ORDER BY dateposted DESC"
   Set objRS = objConn.Execute(strSQL)
   if Not objRS.EOF then
      intID = objRS("id").Value
      dteDatePosted = objRS("dateposted").Value
      strTitle = objRS("title").Value
      strBody = objRS("body").Value
   end if
   ' ** this is the first request, therefore there is no next
   blnNext = False

   ' ** If there is a second record, there is a previous
   objRS.MoveNext
   if Not objRS.EOF then blnPrev = True

If this is not the initial page, Request.QueryString("ID") will not be blank. Therefore, we need to check Request.QueryString("mode") to find out whether we are navigating forward or backward from the current record.

' ** Get appropriate article (either next or previous)
else
   if Request.QueryString("mode") = "prev" then
      ' ** since the mode was "prev" we know that there is a next
      blnNext = True

If we are looking for the previous record, we order our result set in descending order of date. That puts the most recent record first. The second record is only used to determine whether there are any previous to the first.

      strSQL = "SELECT TOP 2 id, dateposted, title, body " &_
"FROM journal WHERE dateposted < " &_
"(SELECT dateposted FROM journal WHERE id = " & Request.QueryString("id") & ") " &_
         "ORDER BY dateposted DESC"
      Set objRS = objConn.Execute(strSQL)

      intID = objRS("id").Value
      dteDatePosted = objRS("dateposted").Value
      strTitle = objRS("title").Value
      strBody = objRS("body").Value

      ' ** If there is a second record, there is a previous
      objRS.MoveNext
      if Not objRS.EOF then blnPrev = True

   else

If the mode is "next," we order our results in ascending order, getting the two records just after this one and making the newer of the two records returned the first record in the result set. The first record is used to determine whether there are any more after the second. The second record is the one we want to look at.

Tip   When you use the SQL TOP operator with a sorted query, the TOP records will be the topmost records after sorting. In this case, we are ordering chronologically from the latest to the oldest, and the topmost two records will be the oldest of the ordered set, the first two records chronologically after the current record.

      ' ** the mode was "next", we know that there is a previous
      blnPrev = True

      strSQL = "SELECT TOP 2 id, dateposted, title, body " &_
"FROM journal WHERE dateposted > " &_
"(SELECT dateposted FROM journal WHERE id = " & Request.QueryString("id") & ") " &_
         "ORDER BY dateposted DESC"
      Set objRS = objConn.Execute(strSQL)

      ' ** We get the values from the first record,
' ** in case there isn't a second
      intID = objRS("id").Value
      dteDatePosted = objRS("dateposted").Value
      strTitle = objRS("title").Value
      strBody = objRS("body").Value

      ' ** The second record will be our record, if there are 2
      objRS.MoveNext
      if Not objRS.EOF then
         intID = objRS("id").Value
         dteDatePosted = objRS("dateposted").Value
         strTitle = objRS("title").Value
         strBody = objRS("body").Value
         blnNext = True
      else
         blnNext = False
      end if
   end if
end if
' ** Close Connection
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

Now we're ready to add the "bits and pieces" of code to the HTML. We want the introductory text to appear only in the first viewing of the page. Therefore, we add a bit of ASP logic—<%if Request.QueryString("ID") = "" then%>—to test Request.QueryString("ID") in order to determine whether or not to display the introduction:

<table border="0">
<%if Request.QueryString("ID") = "" then%>
  <tr>
    <td colspan="2" width="781"><p align="center">
    <a href="javascript:alert('All your code are belong to us!!!');">
    <img src="../images/chutney(large).gif" alt="All Your Base are belong to us!" align="left" border="0"></a><a name="top"><strong><font face="Arial" size="3">I'm Chutney. Welcome to my Journal.</font></strong></a></p>
<p><font face="Arial" size="2">You may have read about me in the mysterious ancient translation of the only known document to have survived that cataclysm of which I am one of only a few known survivors, that nearly-untranslatable document that was recorded by Kevin Spencer, now known only as &quot;Angela's Humpback Cloister.&quot;</font></p>
<p><font face="Arial" size="2">I do not ever wish to speak of that even myself. It was entirely too traumatic for me. Anyway, I have in the recent past years been occupying myself with the advancement of science, perhaps sometimes a bit too... shall we say &quot;tenaciously?&quot; At any rate, it seems to have affected my entire appearance.</font></p>
<p><font face="Arial" size="2">It seems I have been given the opportunity to share my thoughts with the public in this forum, which Kevin has so generously donated. This used to be the &quot;Tips and Tricks&quot; page, but Kevin got so busy with his other projects and endeavors that he never had tome to write for it. So, he approached me. Of course, I demanded absolute license. After all, a man of science should be allowed to speak his mind and ideas freely, without fear of recrimination, should he not?</font></p>
<p><font face="Arial" size="2">I shall endeavor to add to this journal as often as possible. I hope you enjoy it.</font></p></td>
</tr>
 <%end if%>

Our navigation links appear in a table row by themselves. I added the same link code to the top and bottom of the article display table.

      <tr>
        <td width="100%"><font face="Arial" 
        size="2"><%if blnPrev then%><a 
        href="default.asp?mode=prev&id=<%=intID%>">
        &lt;&lt;Previous</a> |<%end if
          if blnNext then%><span lang="en-us"> </span><a 
        href="default.asp?mode=next&id=<%=intID%>">
        Next&gt;&gt;</a><%end if%></font></td>
      </tr>

Finally, we have a table row to display the article itself.

      <tr>
        <td width="100%"><font face="Arial" size="2"><span 
        lang="en-us"><b>
        Title: <%=strTitle%></b><br>
        Date Posted: 
        <%=dteDatePosted%></span></font><p><%=strBody%></p></td>
      </tr>

Creating the Article Creation Page

The Article Creation page was a bit more challenging. I wanted to be able to write the articles in HTML so that I could format the text, graphics, and so forth. The trick was to come up with a way to use FrontPage to write the article in HTML and then create a script to insert the article into the database.

The solution I came up with involved using two pages. One is a template page for developing the articles in FrontPage. The template page is simply a blank Web page, with nothing between the <body> and </body> tags. I saved the page as Template.htm in the same folder with the other journal pages (/journal).

The other page is a form for inserting the article. The template page has the body of the article in it, so the form needed only two fields: one for the date posted, and one for the title. When this page is submitted, it uses the Scripting.FileSystemObject object to open the template file and strip out the HTML from the <body> portion of the file to insert into the database. Here is the code:

<%

Variable definitions:

Dim objConn, strSQL, ConnectionString
Dim strTitle, dteDatePosted, strBody, strMessage
Dim objFSO, objStream, aryLines, strLine, intCt

Only one function is necessary here, for escaping single quotes in the HTML that we are inserting into the database. A single quote is a string delimiter in SQL, and we can escape it by doubling it in our string. Function definitions:

Function SQLEscape(str)
   SQLEscape = Replace(str, "'", "''")
End Function

Execution code begins here.

ConnectionString = "Provider= Microsoft.Jet.OLEDB.4.0; Data Source=" &_
   Server.MapPath("../_private/discussion.mdb")
strMessage = ""
strBody = ""

Here is where we check for a form submission. This page contains a form that submits back to this page, so we need to check whether a form was submitted or whether we are initially loading the page. I did this by checking a required field in the form ("title"). If it is blank, no form was submitted.

' ** title is required. If not blank, the form was submitted
if Request.Form("title") <> "" then
   dteDatePosted = Request.Form("dateposted")
   strTitle = Request.Form("title")
   if Not IsDate(dteDatePosted) then
      strMessage = """" & dteDatePosted &_
         """ is not a valid Date"
   else

Here's where we open and read the file. I used the Split() function to split the file into an array of strings, using the Visual Basic® Scripting Edition (VBScript) constant vbCrLf (Carriage Return/Line Feed) as the delimiter. Since the <body> and </body> strings occur in lines by themselves, we can use the Instr() function to check for these two lines. Any lines between them are added to the resulting "body" string.

      Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
      Set objStream = objFSO.OpenTextFile(Server.MapPath("template.htm"), 1, False)
      aryLines = Split(Trim(objStream.ReadAll), vbCrLf)
      if Not(IsEmpty(aryLines)) then
         For intCt = 0 to UBound(aryLines)
            if InStr(LCase(aryLines(intCt)), "<body>") > 0 then
               intCt = intCt + 1
               If intCt < UBound(aryLines) then
                  Do
                     strBody = strBody &_
                     aryLines(intCt) & vbCrLf
                     if InStr(LCase(aryLines(intCt)), "</body>") > 0 then Exit Do
                     intCt = intCt + 1
                  Loop while intCt <= UBound(aryLines)
               End If
               Exit For
            end if
         Next
      end if
      objStream.Close
      Set objStream = Nothing
      Set objFSO = Nothing

Now we insert the new record.

      strSQL = "INSERT INTO journal (dateposted, title, body) VALUES (#" &_
         dteDatePosted & "#, '" & SQLEscape(strTitle) & "', '" & SQLEscape(strBody) & "')"
      Set objConn = Server.CreateObject("ADODB.Connection")
      objConn.Open ConnectionString
      objConn.Execute(strSQL)
      objConn.Close
      Set objConn = Nothing
      strMessage = "Your article has been added."
   end if
else
   strMessage = "Edit template.htm to create your article. " &_
      "Then enter a Date and Title and submit this form to add it to the Database."
   dteDatePosted = Date
end if
%>

All that is added to the HTML in the form are a few tags to display the data just entered.

Aa140044.fp_autowithdynamiccontent_03(en-us,office.10).gif

Future Enhancements

This application is actually quite simple in scope. We can navigate through the articles one at a time, but we have no mechanism for searching for articles or listing them. A search engine would be a nice addition to this application.

Also, as the articles span myriad topics, adding a "category" column to the database would enhance our ability to organize the articles and perform searches for articles in specific categories.

Remember that no software is "final." You can always add features to an application to improve it.

Conclusion

Dynamic content is a broad term that can encompass many design elements of your Web site, including global layout and configuration and layout variables, dynamic content in pages, and even personalization of pages on a per-user basis.

Intelligent use of dynamic content in your Web site can curtail the number of hours used in Web site maintenance and updating, and reduce the number and size of files in your Web's file system. In addition, it can make certain kinds of pages more user friendly, by automating the organization and functionality of those pages.

We discussed how to identify candidates for database automation by analyzing globally shared content in pages, functionality of different pages, and the need to organize data displayed on the Web site.

As an example, the article demonstrated the automation of an online journal. The original Web site was all hard-coded HTML, difficult to navigate, and required an indeterminate number of pages to maintain. We were able to place the journal entries—which were previously located in a large number of HTML pages—and display them in a single ASP page. Then we created an interface for easy creation of new articles, using only two ASP pages for this process.

We used FrontPage 2002 for all of our ASP development, demonstrating in the process that FrontPage is an excellent toolkit for ASP development. And we discussed briefly some "best practices" for developing ASP applications using FrontPage.