Webmaster Administration Page

ASP Tutorials - Herong's Tutorial Examples

∟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:

All functions should be password protected.
User should be able to locate any record in the table.
User should be able to delete any record from the table.
User 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
%>
<!--#include file='_library.inc'-->
<%
%>

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.