Writing a Simple Guestbook Application--tutorial.jcwcn.com

After I published by tutorial notes on my Web site, I wanted to provide a guestbook tool to allow visitors to share their comments with other visitors and myself. So I started to put together a simple guestbook application in ASP with MS Access database. While doing this, I have collected many interesting notes that might be useful to you.

Let me first provide you some design highlights before going into any details.

Functional requirements:

hyBook should allow visitors to view all existing comments collected in the guestbook.
hyBook should allow visitors to enter new comments anonymously.
hyBook should organize comments by topics.
hyBook should allow Webmasters to manage topics and comments.

Technical highlights:

Two tables will be used in hyBook: "Topic" stores topics, and "Comment" stores comments. Both tables will be stored in a MS Access database file.
A single ASP page will be used for visitors to enter new comment and view existing comments.
Two ASP page will be used for Webmaster to manage topics and comments.
All ASP pages should be as secure as possible.
All ASP pages should be configurable to allow Webmaster to merge into existing Website layout and style.

Database Tables

1. "Topic" table will be named as "hyTopic" with the following fields:

ID - "Autonumber" type
Subject - "Text" type
Content - "Memo" type

2. "Comment" table will be named as "hyComment" with the following fields:

ID - "Autonumber" type
TopicID - "Number" type, foreign key pointing to "hyTopic" table.
Content - "Memo" type
Name - "Text" type
Email - "Text" type
Timestamp - "Date" type
IpAddress - "Text" type

Table names are prefixed with "hy" so that they can be merged easily with your existing databases if needed.

Configuration File

The easiest way to set up configuration files with ASP pages is to use include files. For hyBook, I used one include file, _config.inc, to maintain all configurable items:

<%
'  _config.inc 
'
'  Configuration file
'  hyBook version 2005.12.11
'  Copyright (c) 2005 by Dr. Herong Yang, http://www.herongyang.com/
  
'  Database connection
   Dim ogConn
   Set ogConn = Server.CreateObject("ADODB.Connection")

'  Number of submits per IP per day 
   Dim ngSubmitLimit
   ngSubmitLimit = 10

'  Default topic ID
   Dim ngDefaultTopicID
   ngDefaultTopicID = 14

'  Page title
   Dim sgPageTitle
   sgPageTitle = "hyBook Demo"

'  Debugging flag
   Dim bgDebug, ogDebug 
   bgDebug = False
   Set ogDebug = Nothing

Sub dbConnect
   ogConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      Server.MapPath("/cgi-bin/hyBook.mdb")
      
   If bgDebug Then
      Set oFileSys = Server.CreateObject("Scripting.FileSystemObject")
      Set ogDebug = oFileSys.OpenTextFile("\temp\hyBook.log", 8, True)
      ogDebug.WriteLine("Open data base connection.")
   End If
End Sub

Sub dbClose
   Set ogConn = Nothing

   If bgDebug Then
      ogDebug.WriteLine("Close data base connection.")
      ogDebug.Close
   End If
End Sub
%>

Note that:

All variables defined in _config.inc should be global variables so they become accessible in any functions in any ASP pages. A "Dim" statement is needed to make a variable global.
Database connection process should be a configuration item, because it is always different from one Web server to another.
I also included a debug flag in the configuration file to control printing debug information in a log file.

Page Layout Templates

In order for hyBook to be integrated into different into different Web site, where each of them may different page layout, I have designed hyBook page to be based on template include files. Here is how this design works.

1. Each ASP page will have a template include file inserted at the beginning.

2. The template include file provides code to control the page layout.

3. The page layout should have blank blocks where some ASP function calls will be placed.

4. Each ASP page should only implement those ASP functions called by the template.

For example, the following is a template include file in my hyBook demonstration package:

<%
   opening
%>
<?xml version="1.0"?>
<html><head>
<title>hyBook - Demo</title>
<meta name="description" content="Demonstration page of hyBook 
- A simple guest book script"/> 
<meta name="keywords" content="hyBook, Guestbook, ASP">
<link rel="stylesheet" type="text/css" href="hyBook.css"/>
</head><body>

<center>
<%
   outputHeader
%>
<!-- Other HTML or ASP codes -->
<%
   outputBody
%>
<!-- Other HTML or ASP codes -->
<%
   outputFooter
%>
<p>Powered by <a href=http://www.herongyang.com/hyBook/>hyBook</a></p>
</center>

</body></html>
<%
   closing
%>

Note that there are 5 ASP function calls placed in this template:

"opening" - Giving the ASP page a chance to do initialization work, like establishing database connection, and checking input data in the HTTP request.
"opening" - Giving the ASP page a chance to do closing up work, like closing database connection.
"outputHeader" - Allowing the ASP page to generate HTML code designed as the header block.
"outputBody" - Allowing the ASP page to generate HTML code designed as the body block.
"outputFooter" - Allowing the ASP page to generate HTML code designed as the footer block.

What's goes into the header block, the body block, and the footer block is up to the author of the page. Of course, if you don't use any of those blocks, just implement an empty function in the ASP page.

Also note that a CSS file, hyBook.css, is designed in hyBook to allow you control the appearance of individual HTML elements.

Guestbook Main Page

Once we understand the database, configuration, and template, we are ready to look at the guestbook main page, default.asp. The main objectives of default.asp are:

1. Takes a topic id from the query string so that it can be invoked for any given topics defined in the database.

2. Displays then content of the given topic.

3. Displays all existing comments associated with the given topic.

4. Offers a blank form to allow visitors to enter their comment for the given topic.

5. Stores new comment into database, when a visitor submits the form.

Here is the source code of default.asp:

<!--#include file='_config.inc'-->
<%
'  comment_default.asp
'
'  Comment default page
'  hyBook version 2006.01.01
'  Copyright (c) 2006 by Dr. Herong Yang, http://www.herongyang.com/

   Dim bgShowTopic, bgShowCommentList, bgShowCommentNew, ngTopicID
   Dim sgError, sgNotice
   Dim sgName, sgEmail, sgContent
   bgShowTopic = True
   bgShowCommentList = True
   bgShowCommentNew = True
%>
<!--#include file='_template.inc'-->
<%
Sub opening
   dbConnect

'  Checking query string and form data
   sTopicID = myTrim(Request.Querystring("TopicID"),6)
   ngTopicID = Clng(sTopicID)

   If Request.Form("submit") = "Submit" Then
      sgName = myTrim(Request.Form("Name"),40)
      sgEmail = myTrim(Request.Form("Email"),40)
      sgContent = myTrim(Request.Form("Content"),2000)
      sTopicID = myTrim(Request.Form("TopicID"),6)
      ngTopicID = Clng(sTopicID)

      sgName = removeHTML(sgName)
      sgContent = removeHTML(sgContent)

      bOK = True

'     Checking submit limit
      If bOK Then
         bOK = validateSubmitLimit
      End If

'     Checking ngTopicID
      If bOK Then
         bOK = validateTopicID
      End If

'     Checking required values
      If bOK Then
         bOK = validateRequiredValue
      End If

'     Checking to stop re-post
      If bOK Then
         bOK = validateRepost
      End If

'     Submit data
      If bOK Then
         sName = Replace(sgName, "'", "''")
         sEmail = Replace(sgEmail, "'", "''")
         sContent = Replace(sgContent, "'", "''")
         sAddress = Request.ServerVariables("REMOTE_ADDR")
         sSQL = "INSERT INTO [hyComment] ([Name]," _
            & " [Email]," _
            & " [TopicID]," _
            & " [Content]," _ 
            & " [Timestamp]," _
            & " [IpAddress])" _
            & " VALUES ('" & sName & "'" _
            & ", '" & sEmail & "'" _ 
            & ", " & ngTopicID _ 
            & ", '" & sContent & "'" _ 
            & ", #" & date() & "#" _
            & ", '" & sAddress & "')"
         If bgDebug Then
            ogDebug.WriteLine("sSQL = " & sSQL)
         End If
         ogConn.Execute(sSQL)
         sgNotice = "Your comment has been added. Thank you!"
         sgName = ""
         sgEmail = ""
         sgContent = ""
      Else
         sgName = Server.HTMLEncode(sgName)
         sgEmail = Server.HTMLEncode(sgEmail)
         sgContent = Server.HTMLEncode(sgContent)
      End If
   End If

   If ngTopicID = 0 Then
      ngTopicID = ngDefaultTopicID
   End If

End Sub
Function myTrim(sText,nLen)
   myTrim = sText
   If myTrim <> "" Then
      myTrim = Trim(sText)
      If Len(myTrim) > nLen Then
         myTrim = Mid(myTrim, 1, nLen)
      End If
   End If
End Function

Function validateSubmitLimit
   sAddress = Request.ServerVariables("REMOTE_ADDR")
   dYesterday = DateAdd("D", -1, DATE())
   Set rSelect = Server.CreateObject("ADODB.Recordset")
   sSQL = "SELECT count(*) FROM [hyComment]" _
      & " WHERE [IpAddress] = '" & sAddress & "'" _ 
      & " AND [Timestamp] > #" & dYesterday & "#"
   rSelect.Open sSQL, ogConn
   If bgDebug Then
      ogDebug.WriteLine(sSQL)
      ogDebug.WriteLine("Count = " & rSelect.Fields(0))
   End If
   If rSelect.Fields(0) < ngSubmitLimit Then
      validateSubmitLimit = True
   Else
      sgError = "You have reached your submission limit." _
         & " Please submit your comment later."
      validateSubmitLimit = False
   End If
   set rSelect = Nothing
End Function

Function validateTopicID
   Set rSelect = Server.CreateObject("ADODB.Recordset")
   sSQL = "SELECT * FROM [hyTopic] WHERE [ID] = " & ngTopicID
   rSelect.Open sSQL, ogConn
   If NOT rSelect.EOF Then
      validateTopicID = True
   Else
      sgError = "Invalid topic ID. Please return to home page."
      validateTopicID = False
   End If
   set rSelect = Nothing
End Function

Function validateRequiredValue
   If sgName <> "" AND sgContent <> "" AND sTopicID = "" Then
      validateRequiredValue = True
   Else
      sgError = "Missing required values." _
         & " Please update the form and submit it again."
      validateRequiredValue = False
   End If
End Function

Function validateRepost
   sName = Replace(sgName, "'", "''")
   sEmail = Replace(sgEmail, "'", "''")
   sContent = Replace(sgContent, "'", "''")
   sAddress = Request.ServerVariables("REMOTE_ADDR")
   dYesterday = DateAdd("D", -1, DATE())
   Set rSelect = Server.CreateObject("ADODB.Recordset")
   sSQL = "SELECT * FROM [hyComment]" _
      & " WHERE [IpAddress] = '" & sAddress & "'" _
      & " AND [Timestamp] > #" & dYesterday & "#" _
      & " AND [Name] = '" & sName & "'" _
      & " AND [Email] = '" & sEmail & "'" _
      & " AND [Content] = '" & sContent & "'" _
      & " AND [TopicID] = " & ngTopicID 
   If bgDebug Then
      ogDebug.WriteLine(sSQL)
   End If

   rSelect.Open sSQL, ogConn
   If rSelect.EOF Then
      validateRepost = True
   Else
      sgError = "You are reposting exactly the same comment." _ 
         & " Replease review your comment and post it again."
      validateRepost = False
   End If
   set rSelect = Nothing
End Function

Sub outputHeader
   Response.Write("<p class=hy_title>")
   Response.Write(sgPageTitle) 
   Response.Write("</p>")
End Sub
Sub outputBody
   If sgError <> "" Then 
      htmlError(sgError)
      sgError = ""
   End If

   If sgNotice <> "" Then 
      htmlNotice(sgNotice)
      sgNotice = ""
   End If

   If bgShowTopic Then 
      htmlTopic(ngTopicID)
   End If
   
   If bgShowCommentList Then 
      htmlCommentList(ngTopicID)
   End If
   
   If bgShowCommentNew Then 
      htmlCommentNew(ngTopicID)
   End If   
End Sub

Sub outputFooter
   ' Do nothing
End Sub

Sub closing
   dbClose
End Sub

Function htmlTopic(ngTopicID)
   Set rsTopic = Server.CreateObject("ADODB.Recordset")
   sSQL = "SELECT * FROM hyTopic WHERE ID=" & ngTopicID
   If bgDebug Then
      ogDebug.WriteLine(sSQL)
   End If
   rsTopic.Open sSQL, ogConn
   If NOT rsTopic.EOF Then
      Response.Write("<table class=hy_topic cellspacing=0" _ 
         & " cellpadding=5><tr class=hy_topic_subject><td>")
      Response.Write(rsTopic("Subject"))
      Response.Write("</td></tr><tr class=hy_topic_content><td>")
      Response.Write(replace(rsTopic("Content"), vbcrlf, "<br>"))
      Response.Write("</td></tr></table>")
   Else
      htmlError("Invalid input data. Please return to home page.") 
      bgShowCommentList = False
      bgShowCommentNew = False
   End If
   set rsTopic = Nothing
End Function

Function htmlCommentNew(ngTopicID)
   Response.Write("<table class=hy_comment cellspacing=0" _ 
      & " cellpadding=5>")
   Response.Write("<form action=" _ 
      & Request.ServerVariables("SCRIPT_NAME") & " method=post>")
   Response.Write("<input type=hidden name=TopicID" _
      & " value=""" & ngTopicID & """>")
   Response.Write("<tr><td class=hy_comment_label>Your Name:</td>" _ 
      & "<td><input type=text size=40 maxlength=40 name=Name" _
      & " value=""" & sgName & """>(Req.)</td></tr>")
   Response.Write("<tr><td class=hy_comment_label>Your E-mail:</td>" _
      & "<td><input type=text size=40 maxlength=40 name=Email" _
      & " value=""" & sgEmail & """>(Opt.)</td></tr>")
   Response.Write("<tr><td class=hy_comment_label>Comment:</td>" _
      & "<td><textarea name=Content cols=45 rows=10 wrap=virtual>" _
      & sgContent & "</textarea>(Req.)</td></tr>")
   Response.Write("<tr><td></td>" _
      & "<td><input name=submit value=Submit type=submit><br>" _
      & "Note that your email is only for Webmaster use only." _ 
      & " It will not be displayed.</td></tr>")
   Response.Write("</form>")
   Response.Write("</table>")
End Function

Function removeHTML(strText)
   Dim RegEx
   Set RegEx = New RegExp
   RegEx.Pattern = "<[^>]*>"
   RegEx.Global = True
   RemoveHTML = RegEx.Replace(strText, "")
End Function
Function htmlCommentList(ngTopicID)
   Set rsComment = Server.CreateObject("ADODB.Recordset")
   sSQL = "SELECT * FROM [hyComment] WHERE [TopicID] =" _ 
      & ngTopicID & " ORDER BY ID DESC"
   rsComment.Open sSQL, ogConn
   If rsComment.EOF Then
      htmlNotice("No comment has been submitted.") 
   Else 
      Response.Write("<table class=hy_list cellspacing=0" _ 
         & " cellpadding=5>")
      sClass="hy_list_item_lo"
      Do While NOT rsComment.EOF
         Response.Write("<tr class=" & sClass & "><td><b>" _ 
            & rsComment("Name") & "</b> wrote on " _ 
            & rsComment("Timestamp") & ": <br><br>")
         Response.Write(replace(rsComment("Content"), vbcrlf, "<br>")_
            & "</td></tr>")
         rsComment.MoveNext
         If sClass = "hy_list_item_lo" Then
            sClass = "hy_list_item_hi"
         Else
            sClass = "hy_list_item_lo"
         End If
      Loop
      Response.Write("</table>")
   End If
   set rsComment = Nothing
End Function

Function htmlNotice(sText)
   Response.Write("<table class=hy_notice cellspacing=0" _
      & " cellpadding=5>")
   Response.Write("<tr><td>" & sText & "</td></tr>")
   Response.Write("</table>")
End Function

Function htmlError(sText)
   Response.Write("<table class=hy_error cellspacing=0" _ 
      & " cellpadding=5>")
   Response.Write("<tr><td>" & sText & "</td></tr>")
   Response.Write("</table>")
End Function

Function myDump
   aKeys = hgRqParam.Keys()
   ogDebug.WriteLine("Values in hgRqParam:")
   For i=0 To hgRqParam.Count-1
      k = aKeys(i)
      ogDebug.WriteLine(k & " = (" & hgRqParam.Item(k) & ")")
   Next

   aKeys = hgPgParam.Keys()
   ogDebug.WriteLine("Values in hgPgParam:")
   For i=0 To hgRqParam.Count-1
      k = aKeys(i)
      ogDebug.WriteLine(k & " = (" & hgPgParam.Item(k) & ")")
   Next
End Function
%>
Interesting things to note here: 
"_congif.inc" is included at the very beginning of the page to make sure that all global variables and functions are available to the entire page. 
"_template.inc" is included after some page level variables are defined. 
"opening" is implemented to take care of input data from the query string and submitted form. 
"outputHeader" is implemented to display the page title. 
"outputBody" is implemented to display the topic, existing comments, and a blank form. 
"outputFooter" is implemented as an empty function. 
"closing" is implemented to just call database disconnection function. 
Data Submission Issues 
Whenever you connect your ASP pages to databases, there are some data submission issues to be considered. 
1. Truncating input values. You should always truncate all input values to make sure their lengths are not longer than some predefined limits. If you don't do this, users may enter large mount of text to crash your database file, or use up all your free space to bring your server down. 
Tuncating input values should started in the HTML form fields. Every <input> tag should have a "maxlength" option like this: "<input type=text maxlength=40 name=Email>". "maxlength" option will stop user enter more characters than the specified value. 
However, "maxlength" alone is not enough to protect your database. Users do not have to use your form to post values to your server. Any one can easily modify your form, use their own form, or write a simple client program to post values with any lengths to your server. So you have to use a truncation logic on your server side to protect your database. 
In hyBook, I wrote a simple function called myTrim() to trim white spaces on both ends of a given string, and truncate it to a given length. 
2. Setting submission limits for any single IP address. You should also consider setting up a submission limit for any single IP address. If you don't do this, users may keep submitting values to your server to use up all your free space to bring your server down. 
In hyBook, user's IP addresses are recorded in the database. If too many submissions from the same IP address are received, hyBook will reject the submission. 
3. Escaping (') in string values. You should check for single quotes (') in all string values to be used in SQL statements, because (') is used as the string quoting character in SQL statements. Any (') found in a string value must be escaped by replacing it with two single quotes (''). If you don do this, users may get an invalid SQL statement error, if (') is used in an input text field. 
For example, if you are writing a search page like this: 
...
   Set rSelect = Server.CreateObject("ADODB.Recordset")
   sSQL = "SELECT * FROM [myTable]" _
      & " WHERE [Content] LIKE '" & Request.Form("Key") & "'"
   rSelect.Open sSQL, oConn
...
When a user submits "Key = Today's paper", he/she will get invalid SQL statement error, because your page will try to run this SQL statement: 
   SELECT * FROM [myTable] WHERE [Content] LIKE 'Today's paper'
Things could get even worse if a user submits "Key = Today' OR [Content] <> 'Today". The SQL statement will be valid, but it will return all records in your table, because you page will actually run this SQL statement: 
   SELECT * FROM [myTable] WHERE [Content] LIKE 'Today' OR [Content] <> 'Today'
4. Removing (#) from date values. You should check for pond signs (#) in all date values to be used in SQL statements, because (#) is used as the date quoting character in SQL statements. Any (') found in a string value must be removed. If you don't do this, users may get an invalid SQL statement error, if (#) is used in an input date field. 
5. Removing HTML tags in string values. You should consider removing HTML tags from all user entered text. If you don't do this, when you display user-entered text back to a Web page, those HTML tags may corrupt your page design. 
Of course, there are many other issues related to database supported Web pages. You should do more researches in this area, if you concerned. 
Webmaster Administration Page 
Whenever you are taking user's input to a database, you have to write an administration page for yourself as the Webmaster to manage those data. The administration page should have the following features: 
All functions should be password protected. 
It should be able to locate any record in the table. 
It should be able to delete any record from the table. 
It should be able to correct any field in a record. 
In hyBook, I wrote the following simple admin page, 
<!--#include file='_config.inc'-->
<%
'  comment_admin.asp
'
'  Comment admin page
'  hyBook version 2006.01.01
'  Copyright (c) 2006 by Dr. Herong Yang, http://www.herongyang.com/

   Dim bgDoSubmit, hgRqParam, hgDbParam, hgPgParam
   bgDoSubmit = False
   Set hgRqParam = CreateObject("Scripting.Dictionary")
   Set hgDbParam = CreateObject("Scripting.Dictionary")
   Set hgPgParam = CreateObject("Scripting.Dictionary")

   Dim sgError, sgNotice
   sgError = ""
   sgNotice = ""

   Dim sgPass, sgAdminPass
   sgPass = ""
   sgAdminPass = "ssapnimda"

   Dim bgShowDetail, bgShowList
   bgShowDetail = False
   bgShowList = False

%>
<!--#include file='_template.inc'-->
<%
Sub opening
   dbConnect

'  Checking password
   sgPass = Request.Querystring("Pass")

   If Request.Form("Method") = "Post" Then
      sgPass = Request.Form("Pass")
   End If

   If sgPass = sgAdminPass Then
      bgShowDetail = True
      bgShowList = True
      bgDoSubmit = True
   Else
      sgError = "Invalid password."
   End If

'  Handling submit
   If  bgDoSubmit Then
      doSubmit
   End If
End Sub

Sub outputHeader
   Response.Write("<p class=hy_title>")
   Response.Write(sgPageTitle & " - Admin") 
   Response.Write("</p>")
   Response.Write("<form action=""" _
      & Request.ServerVariables("SCRIPT_NAME") & """ method=post>")
   Response.Write("<input type=hidden name=Method value=Post>")
   Response.Write("<input type=hidden name=Pass value=""" _
      & sgPass & """>")
End Sub

Sub outputBody
   If sgError <> "" Then 
      htmlError(sgError)
      sError = ""
   End If

   If sgNotice <> "" Then 
      htmlNotice(sgNotice)
      sNotice = ""
   End If

   If bgShowDetail Then 
      htmlDetail
   End If
   
   If bgShowList Then 
      htmlList
   End If
End Sub

Sub outputFooter
   Response.Write("</form>")
   ' Do nothing
End Sub

Sub closing
   dbClose
End Sub
Function doSubmit
'  Taking input values
   If Request.Form("Method") = "Post" Then
      hgRqParam.Add "Submit", myTrim(Request.Form("Submit"),10)
      hgRqParam.Add "ID", myTrim(Request.Form("ID"),6)
      hgRqParam.Add "TopicID", myTrim(Request.Form("TopicID"),6)
      hgRqParam.Add "Name", myTrim(Request.Form("Name"),40)
      hgRqParam.Add "Email", myTrim(Request.Form("Email"),40)
      hgRqParam.Add "Content", myTrim(Request.Form("Content"),2000)
      hgRqParam.Add "Timestamp", myTrim(Request.Form("Timestamp"),20)
      hgRqParam.Add "IpAddress", myTrim(Request.Form("IpAddress"),15)
      hgRqParam.Add "ItemID", myTrim(Request.Form("ItemID"),6)
   End If
   
'  Initial handling of submit
   If hgRqParam("Submit") = "Search" Then
      ' doNothing
   ElseIf hgRqParam("Submit") = "Clear" Then
      hgRqParam("ID") = ""
      hgRqParam("TopicID") = ""
      hgRqParam("Name") = ""
      hgRqParam("Email") = ""
      hgRqParam("Content") = ""
      hgRqParam("Timestamp") = ""
      hgRqParam("IpAddress") = ""
   ElseIf hgRqParam("Submit") = "Update" Then
      ' doNothing
   ElseIf hgRqParam("Submit") = "Insert" Then
      ' doNothing
   ElseIf hgRqParam("Submit") = "Select" Then
      doSelect
   ElseIf hgRqParam("Submit") = "Delete" Then
      doDelete
   Else 
      ' doNothing
   End If

'  Preparing values for HTML page
   aKeys = hgRqParam.Keys()
   For i=0 To hgRqParam.Count-1
      k = aKeys(i)
       hgPgParam.Add k, Server.HTMLEncode(hgRqParam(k))
   Next

'  Preparing values for Database
   aKeys = hgRqParam.Keys()
   For i=0 To hgRqParam.Count-1
      k = aKeys(i)
       hgDbParam.Add k, Replace(hgRqParam(k), "'", "''")
   Next

'  Final handling of submit
   If hgRqParam("Submit") = "Search" Then
      ' doNothing
   ElseIf hgRqParam("Submit") = "Clear" Then
      ' doNothing
   ElseIf hgRqParam("Submit") = "Update" Then
      doUpdate
   ElseIf hgRqParam("Submit") = "Insert" Then
      doInsert
   ElseIf hgRqParam("Submit") = "Select" Then
      ' doNothing
   ElseIf hgRqParam("Submit") = "Delete" Then
      ' doNothing
   Else 
      ' doNothing
   End If

   If bgDebug Then
      myDump
   End If
End Function

Function doSelect
   If hgRqParam("ItemID") = "" Then
      sgNotice = "No item selected."
   Else 
      Set rsComment = Server.CreateObject("ADODB.Recordset")
      sSQL = "SELECT * FROM hyComment WHERE ID=" & hgRqParam("ItemID")
      rsComment.Open sSQL, ogConn
      If rsComment.EOF Then
         sgNotice = "No record found."
      Else 
         hgRqParam("ID") = rsComment("ID")
         hgRqParam("TopicID") = rsComment("TopicID")
         hgRqParam("Content") = rsComment("Content")
         hgRqParam("Name") = rsComment("Name")
         hgRqParam("Email") = rsComment("Email")
         hgRqParam("Timestamp") = rsComment("Timestamp")
         hgRqParam("IpAddress") = rsComment("IpAddress")
      End If
   End If
End Function

Function doDelete
   If hgRqParam("ItemID") = "" Then
      sgNotice = "No item selected."
   Else 
      Set rsComment = Server.CreateObject("ADODB.Recordset")
      sSQL = "DELETE FROM hyComment WHERE ID=" & hgRqParam("ItemID")
      rsComment.Open sSQL, ogConn
      sgNotice = "Record deleted."
   End If
End Function
Function doUpdate
   If hgDbParam("ID") = "" Then
      sgError = "Missing record ID."
   Else 
      sSQL = "UPDATE hyComment" _
         & " SET TopicID = " & hgDbParam("TopicID") _
         & ", Content = '" & hgDbParam("Content") & "'" _
         & ", Name = '" & hgDbParam("Name") & "'" _
         & ", Email = '" & hgDbParam("Email") & "'" _
         & ", [Timestamp] = #" & hgDbParam("Timestamp") & "#" _
         & ", IpAddress = '" & hgDbParam("IpAddress") & "'" _
         & " WHERE ID = " & hgDbParam("ID")
         If bgDebug Then
            ogDebug.WriteLine("The update query::")
            ogDebug.WriteLine("sSQL = (" & sSQL & ")")
         End If
      Set rsComment = Server.CreateObject("ADODB.Recordset")
      rsComment.Open sSQL, ogConn
      sgNotice = "Record updated."
   End If
End Function

Function doInsert
   If hgDbParam("Content") = "" Then
      sgError = "Content required."
   Else 
      hgRqParam("ID") = ""
      hgDbParam("ID") = ""
      hgPgParam("ID") = ""
      sSQL = "INSERT INTO hyComment" _
         & " (TopicID, Content, Name, Email, [Timestamp], IpAddress)" _
         & " VALUES (" & hgDbParam("TopicID") _
         & ", '" & hgDbParam("Content") & "'" _
         & ", '" & hgDbParam("Name") & "'" _
         & ", '" & hgDbParam("Email") & "'" _
         & ", #" & hgDbParam("Timestamp") & "#" _
         & ", '" & hgDbParam("IpAddress") & "'" _
         & ")"
         If bgDebug Then
            ogDebug.WriteLine("The insert query::")
            ogDebug.WriteLine("sSQL = (" & sSQL & ")")
         End If
      Set rsComment = Server.CreateObject("ADODB.Recordset")
      rsComment.Open sSQL, ogConn
      sgNotice = "Record inserted."
   End If
End Function

Function htmlDetail
   Response.Write("<table class=hy_comment cellspacing=0" _ 
      & " cellpadding=3>")
   Response.Write("<tr><td align=right>ID:</td>" _
      & "<td><input type=text size=10 maxlength=10 name=ID" _
      & " value=""" & hgPgParam("ID") & """></td></tr>")
   Response.Write("<tr><td align=right>Topic ID:</td>" _
      & "<td><input type=text size=10 maxlength=10 name=TopicID" _
      & " value=""" & hgPgParam("TopicID") & """></td></tr>")
   Response.Write("<tr><td align=right>Content:</td>" _ 
      & "<td><textarea cols=45 rows=10 wrap=virtual name=Content>" _
      & hgPgParam("Content") & "</textarea></td></tr>")
   Response.Write("<tr><td align=right>Name:</td>" _
      & "<td><input type=text size=40 maxlength=40 name=Name" _
      & " value=""" & hgPgParam("Name") & """></td></tr>")
   Response.Write("<tr><td align=right>Email:</td>" _ 
      & "<td><input type=text size=40 maxlength=40 name=Email" _
      & " value=""" & hgPgParam("Email") & """></td></tr>")
   Response.Write("<tr><td align=right>Timestamp:</td>" _
      & "<td><input type=text size=16 maxlength=16 name=Timestamp" _
      & " value=""" & hgPgParam("Timestamp") & """></td></tr>")
   Response.Write("<tr><td align=right>IP Address:</td>" _
      & "<td><input type=text size=16 maxlength=16 name=IpAddress" _
      & " value=""" & hgPgParam("IpAddress") & """></td></tr>")
   Response.Write("<tr><td align=right>&nbsp;</td>" _ 
      & "<td><input type=submit name=Submit value=Search>" _
      & "<input type=submit name=Submit value=Update>" _ 
      & "<input type=submit name=Submit value=Insert>" _
      & "<input type=submit name=Submit value=Clear></td></tr>")
   Response.Write("</table>")
End Function
Function htmlList
   If bgDebug Then
      ogDebug.WriteLine("Dumping page variables in htmlList():")
      myDump
   End If

   If sgSubmit = "Search" Then
   ElseIf sgSubmit = "Update" Then
   ElseIf sgSubmit = "Insert" Then
   ElseIf sgSubmit = "Select" Then
   ElseIf sgSubmit = "Delete" Then
   Else 
   End If

'  Performing the search
   sCriteria = ""
   If hgDbParam("ID") <> "" Then
      sCriteria = sCriteria _ 
         & " AND ID = " & hgDbParam("ID")
   End If
   If hgDbParam("TopicID") <> "" Then
      sCriteria = sCriteria _ 
         & " AND TopicID = " & hgDbParam("TopicID")
   End If
   If hgDbParam("Name") <> "" Then
      sCriteria = sCriteria _ 
         & " AND Name LIKE '%" & hgDbParam("Name") & "%'"
   End If
   If hgDbParam("Email") <> "" Then
      sCriteria = sCriteria _ 
         & " AND Email LIKE '%" & hgDbParam("Email") & "%'"
   End If
   If hgDbParam("IpAddress") <> "" Then
      sCriteria = sCriteria _ 
         & " AND IpAddress LIKE '%" & hgDbParam("IpAddress") & "%'"
   End If
   If hgDbParam("Timestamp") <> "" Then
      sCriteria = sCriteria _ 
         & " AND Timestamp = #" & hgDbParam("Timestamp") & "#"
   End If
   sCriteria = Replace(sCriteria, " AND", "", 1, 1)
   Set rsComment = Server.CreateObject("ADODB.Recordset")
   sSQL = "SELECT * FROM hyComment"
   If sCriteria <> "" Then
      sSQL = sSQL & " WHERE " & sCriteria
   End If
   sSQL = sSQL & " ORDER BY ID DESC"
   If bgDebug Then
      ogDebug.WriteLine("The search query::")
      ogDebug.WriteLine("sSQL = (" & sSQL & ")")
   End If

   rsComment.Open sSQL, ogConn
   If bgDebug Then
      ogDebug.WriteLine("Count = (" & rsComment.RecordCount & ")")
   End If
   
   If rsComment.EOF Then
      htmlNotice("No record found.")
   Else 
      Response.Write("<table class=hy_list cellspacing=1" _
         & " cellpadding=3>")
      Response.Write("<tr class=hy_list_button><td colspan=7>" _ 
         & "<input type=submit name=Submit value=Select>" _ 
         & "<input type=submit name=Submit value=Delete></td></tr>")
      sClass="hy_list_item_lo"
      Do While NOT rsComment.EOF
'         If CStr(rsComment("ID")) = hgPgParam("ID") Then
         If CStr(rsComment("ID")) = hgPgParam.Item("ID") Then
            sCheck = " checked"
         Else
            sCheck = ""
         End If

         Response.Write("<tr class="& sClass & ">" _ 
            & "<td><input type=radio name=ItemID value=" _ 
            & rsComment("ID") & sCheck & ">" _
            & "</td><td>" & rsComment("ID") _
            & "</td><td>" & rsComment("TopicID") _
            & "</td><td>" & rsComment("Name") _
            & "</td><td>" & rsComment("Email") _
            & "</td><td>" & rsComment("Timestamp") _
            & "</td><td>" & rsComment("IpAddress") _
            & "</td></tr>")
         rsComment.MoveNext
         If sClass = "hy_list_item_lo" Then
            sClass = "hy_list_item_hi"
         Else
            sClass = "hy_list_item_lo"
         End If
      Loop
      Response.Write("<tr class=hy_list_button><td colspan=7>" _ 
         & "<input type=submit name=Submit value=Select>" _ 
         & "<input type=submit name=Submit value=Delete></td></tr>")
      Response.Write("</table>")
   End If
   set rsComment = Nothing
End Function

Function removeHTML(strText)
   Dim RegEx
   Set RegEx = New RegExp
   RegEx.Pattern = "<[^>]*>"
   RegEx.Global = True
   RemoveHTML = RegEx.Replace(strText, "")
End Function
Function htmlNotice(sText)
   Response.Write("<table class=hy_notice>")
   Response.Write("<tr><td>" & sText & "</td></tr>")
   Response.Write("</table>")
End Function

Function htmlError(sText)
   Response.Write("<table class=hy_error>")
   Response.Write("<tr><td>" & sText & "</td></tr>")
   Response.Write("</table>")
End Function

Function myTrim(sText,nLen)
   myTrim = sText
   If myTrim <> "" Then
      myTrim = Trim(sText)
      If Len(myTrim) > nLen Then
         myTrim = Mid(myTrim, 1, nLen)
      End If
      myTrim = removeHTML(myTrim)
   End If
End Function

Function myDump
   aKeys = hgRqParam.Keys()
   ogDebug.WriteLine("Values in hgRqParam:")
   For i=0 To hgRqParam.Count-1
      k = aKeys(i)
      ogDebug.WriteLine(k & " = (" & hgRqParam.Item(k) & ")")
   Next

   aKeys = hgPgParam.Keys()
   ogDebug.WriteLine("Values in hgPgParam:")
   For i=0 To hgRqParam.Count-1
      k = aKeys(i)
      ogDebug.WriteLine(k & " = (" & hgPgParam.Item(k) & ")")
   Next
End Function
%>
Some very interesting techniques used in this page: 
This page is also driven by the configuration file, _config.inc, and the template file, _template.inc. 
All functions in this page are grouped under three Boolean flags: bgShowDetail, bgShowList, and bgDoSubmit. 
A single password is used control the function flags. Of course, you should change this password, if you want to use my page. 
Three dictionary objects are used to maintain user values: hgRqParam, hgDbParam, and hgPgParam, where hgRqParam stores values received from the user; hgDbParam stores values processed from hgRqData and ready for database SQL statements; and hgPgParam stores values that are safe to be used in HTML output. 
doSubmit() function is designed to receive all user input values and call other handler functions for each type of user submission like: Search, Clear, Update, Insert, Select, or Delete. 
Search result is displayed as a list with radio button on each item for selection or deletion. 
Selected record is displayed in a form for viewing or updating. The same form can also be used to insert a new record. 
Conclusion 
hyBook is simple guestbook tool. You can use it directly on your Web site, or use it to learn various techniques for writing database applications. 
hyBook shows you how to make a Web application configurable with a configuration file, a template file, and a CSS file. 
When composing SQL statements, all values must checked for quoting characters. 
hyBook administration page shows you some interesting ASP coding techniques on managing table records. 
If you are interested in download and install hyBook, go to http://www.herongyang.com/hyBook/.

discuss this topic to forum

[Print][Close][Bookmark:digg+,reddit+,del.icio.us+,dzone+]

Writing a Simple Guestbook Application

relation tutorial

Category

New

Hot