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> </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.
