CREATE TABLE GUESTBOOK (
NAME CHAR(8), /* who leaves this message */
DATE DATETIME, /* the date of the message */
SUBJECT VARCHAR(100), /* the subject of the message */
PREV_MESSAGEID int, /* which is the parent key of this message, used to construct threads */
MESSAGEID int primary key, /* this message ID */
MESSAGE TEXT /* content of message */
)
<%
If Session("UserName")="" Then
Session("redirectPage")=Request.ServerVariables("SCRIPT_NAME")
Response.redirect("password.asp")
END If
%>
<script language=jscript runat=server>
function DisplayItems(PrevID) {
var i = 1;
//Response.write(nRowsToReturn)
do {
if (Data(i, 4).valueOf() == PrevID) {
Response.write("<ul>")
Response.write(String.fromCharCode(13))
Response.write("<li>")
// Name
Response.write(Data(i, 1))
Response.write(" -- ")
// Subject
Response.write("<a href=detail.asp?MESSAGEID=" + Data(i, 5) + ">")
Response.write(Data(i, 3))
Response.write("</a>")
// Date
Response.write(" (")
Response.write(Data(i, 2))
Response.write(") ")
DisplayItems(Data(i, 5).valueOf())
Response.write("</ul>")
}
i++;
}
while (i <= nRowsToReturn);
}
</script>
<%
Dim oSQLServer
Set oSQLServer = CreateObject ("SQLOLE.SQLServer")
oSQLServer.Connect "db server IP address", "username", "password"
Dim thisDb
' database name
Set thisDb = oSQLServer.Databases("dbname")
%>
<HTML>
<HEAD>
<TITLE>Guestbook</TITLE>
<LINK REV="made" HREF="mailto:[email protected]">
<META NAME="author" content="Hardy Wang">
<META name="generator" content="My Hands">
<META name="keywords" content="Guestbook">
<META name="description" content="Guest Book">
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<%
' delete the records older than 60 days
' after old entries are deleted, make sure the left items cannot point to a non-exsisting
' entry (MESSAGEID), thus set it "-1"
Query = "select * from GUESTBOOK WHERE DATEDIFF(day, DATE, GetDate())>60 order by DATE"
set Check = thisDb.ExecuteWithResults(Query)
nRowsToReturn = Check.Rows
'Response.write(nRowsToReturn)
For i=1 to nRowsToReturn
Query = "update GUESTBOOK set PREV_MESSAGEID=-1 where PREV_MESSAGEID = "
Query = Query & trim(Check.GetColumnString(i, 5))
'Response.write(Query)
thisDb.ExecuteImmediate(Query)
Next
Query = "delete GUESTBOOK WHERE DATEDIFF(day, DATE, GetDate())>60"
thisDb.ExecuteImmediate(Query)
' Get all the data, save in an array
Dim Data
Query = "select * from GUESTBOOK"
set Check = thisDb.ExecuteWithResults(Query)
nRowsToReturn = Check.Rows
' Response.write(nRowsToReturn)
nColNumbers = 6
ReDim Data(nRowsToReturn, nColNumbers)
For i=1 to nRowsToReturn
For j=1 to nColNumbers
Data(i, j) = Check.GetColumnString(i,j)
Next
Next
' finding max Message ID
URL = "new.asp?ID="
ID = -1
For i=1 to nRowsToReturn
If (CInt(Data(i, 5)) > ID) Then
ID = CInt(Data(i, 5))
End If
Next
ID = ID + 1
URL = URL & ID
URL = URL & "&PREV_ID=-1"
%>
<h1 align=center>Guestbook</h1>
<table width=100% border="0">
<tr>
<td>
<%
If nRowsToReturn > 0 Then
Call DisplayItems(-1)
End If
%>
</td>
<td align=center valign=top>
<a href=<%=URL%>>
<img src="announce.gif" alt="New Topic" border=0><br>
<font face=verdana,arial size=-1>[New topics begin here]</font>
</a>
</td>
</tr>
</table>
<%
oSQLServer.disConnect
%>
</body></html>
<%
If Session("UserName")="" Then
Session("redirectPage")=Request.ServerVariables("SCRIPT_NAME")
Response.redirect("password.asp")
END If
%>
<%
Set dbConnection = Server.CreateObject("ADODB.Connection")
dbConnection.Open "odbc" ,"username","password"
%>
<%
Current = Month(Date())
Current = Current & "/"
Current = Current & Day(Date())
Current = Current & "/"
Current = Current & Year(Date())
Current = Current & " "
Current = Current & Hour(Time())
Current = Current & ":"
Current = Current & Minute(Time())
Current = Current & ":"
Current = Current & Second(Time())
Query = "insert into GUESTBOOK (NAME, DATE, SUBJECT, PREV_MESSAGEID, MESSAGEID, MESSAGE) values('"
Query = Query & Session("UserName")
Query = Query & "', '"
Query = Query & Current
Query = Query & "', '"
Query = Query & Replace(trim(Request("SUBJECT")), "'", "''")
Query = Query & "', "
Query = Query & trim(Request("PREV_ID"))
Query = Query & ", "
Query = Query & trim(Request("ID"))
Query = Query & ", '"
Query = Query & Replace(trim(Request("MESSAGE")), "'", "''")
Query = Query & "')"
'Response.Write(Query)
set Insert = dbConnection.Execute(Query)
dbConnection.Close
response.redirect("index.asp")
%>
<%
If Session("UserName")="" Then
Session("redirectPage")=Request.ServerVariables("SCRIPT_NAME")
Response.redirect("password.asp")
END If
%>
<%
Set dbConnection = Server.CreateObject("ADODB.Connection")
dbConnection.Open "odbc" ,"username","password"
%>
<HTML>
<HEAD>
<TITLE>New item</TITLE>
<LINK REV="made" HREF="mailto:[email protected]">
<META NAME="author" content="Hardy Wang">
<META name="generator" content="My Hands">
<META name="keywords" content="Guestbook, new item">
<META name="description" content="Guest Book, New Content">
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<h2>Leave your message here</h2>
<form action=register.asp method=post>
<input type=hidden name=PREV_ID value=<%=trim(Request("PREV_ID"))%>>
<input type=hidden name=ID value=<%=trim(Request("ID"))%>>
<%
If CInt(trim(Request("PREV_ID"))) <> -1 Then
Query = "select * from GUESTBOOK where MESSAGEID="
Query = Query & trim(Request("PREV_ID"))
'Response.Write(Query)
set Check = dbConnection.Execute(Query)
If InStr(trim(Check("SUBJECT")), "Re:") > 0 Then
s = ""
Else
S = "Re: "
End If
S = S & trim(Check("SUBJECT"))
Else
S = ""
End If
%>
Subject:<br>
<input type=text name=SUBJECT size=50 maxlength=100 value="<%=S%>"><br>
Content:<br>
<textarea name=MESSAGE cols=50 rows=10>
</textarea><br><br>
<input type=submit value=Say>
</form>
</BODY></HTML>
<%
If Session("UserName")="" Then
Session("redirectPage")=Request.ServerVariables("SCRIPT_NAME")
Response.redirect("password.asp")
END If
%>
<%
Set dbConnection = Server.CreateObject("ADODB.Connection")
dbConnection.Open "odbc" ,"username","password"
%>
<%
' NOTE: name2email is used to retrieve user's email address given his user name in my
' intranet, very simple function. so I don't display here.
' FormatDateTimeCn is a function used to display date in Chinese format. you can adjust
' this program with FormateDateTime function using corresponding parameters.
%>
<!-- #include virtual="manager/Common_Functions/name2email.vbs"-->
<!-- #include virtual="manager/Common_Functions/FormatDateTimeCn.vbs"-->
<HTML>
<HEAD>
<TITLE>Content of Guestbook</TITLE>
<LINK REV="made" HREF="mailto:[email protected]">
<META NAME="author" content="Hardy Wang">
<META name="generator" content="My Hands">
<META name="keywords" content="Content of Guestbook">
<META name="description" content="Content of Guest Book">
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<h2>Content of Guestbook</h2>
<%
Query = "select * from GUESTBOOK where MESSAGEID="
Query = Query & trim(Request("MESSAGEID"))
'Response.write(Query)
set Check = dbConnection.Execute(Query)
%>
<table width=100% border=1 cellspacing=0>
<tr>
<td><b>Speaker:</b><a href=mailto:<%=Email(trim(Check("NAME")))%>><%=trim(Check("NAME"))%></a></td>
<td><b>Message Time:</b><%=FormatDateTimeCn(trim(Check("DATE")), 1)%></td>
</tr>
<tr>
<td colspan=2><b>Subject:</b><%=trim(Check("SUBJECT"))%></td>
</tr>
<tr>
<td colspan=2>
<b>Content:</b><br>
<%=trim(Check("MESSAGE"))%>
</td>
</tr>
<tr>
<td colspan=2 align=center>
<%
Query = "select MESSAGEID from GUESTBOOK order by MESSAGEID DESC"
set Check = dbConnection.Execute(Query)
MaxID = CInt(Check("MESSAGEID")) + 1
URL = "new.asp?ID="
URL = URL & CStr(MaxID)
URL = URL & "&PREV_ID="
URL = URL & trim(Request("MESSAGEID"))
%>
<a href=<%=URL%>>Repley to the topic</a>
</td>
</table>
</BODY></HTML>