Webmaster Administration Page

This section describes the Webmaster administration page that allows you to search, modify, delete or insert records into tables.

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:

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
%>
<!--#include file='_library.inc'-->
<%
%>

Some very interesting techniques used in this page:

Table of Contents

 About This Book

 ASP (Active Server Pages) Introduction

 IIS (Internet Information Services) 5.0

 MS Script Debugger

 VBScript Language

 ASP Built-in Run-time Objects

 ASP Session

 Creating and Managing Cookies

 Managing Sessions with and without Cookies

 scrrun.dll - Scripting Runtime DLL

 Managing Response Header Lines

 Calculation Speed and Response Time

 ADO (ActiveX Data Object) DLL

 Working with MS Access Database

Guest Book Application Example

 Design Overview

 Database Tables

 Configuration File

 Page Layout Template File

 Guest book Main Script

 The Utility Script Library File

 Data Submission Issues

Webmaster Administration Page

 References

 Full Version in PDF/EPUB