By Ryan S.
During the creation of the Dacula High's (my school) website, I wanted to offer the largest feature base, because it would be this general sample that I showed the administrators that could make or break a website.
Because of this, I wanted to offer the students and the administrators both cool features. I thought that it would be really cool to have a way for students to send messages over the webpage, with varying designs. This article deals with how to setup an intranet offering messaging between users using databases, and also how to offer "stationary"/"greeting card" looks. Considering that in high school, every note writer does something to spruce up the note, I had to make sure that it was also aesthetically pleasing.
First, creating the database is an utmost. I loaded up Access, and using my pre-existing database for the school, I created a new table called SentMessages. It was a relatively simpledatabase, and here is how I defined the fields (sorry, I don't use SQL)
| MessageID | The Message ID for the message(AutoNumber & Primary Key) |
| To | The studentID of the recepient (number) |
| From | The studentID of the sender (number) |
| Date | When the message was sent (Short date mm/dd/yyyy) |
| IsNew | A boolean expression if its new (number, though for SQL you can use BIT) (0=no, (1/-1)=yes) |
| Subject | The message subject (text, 255 max) |
| Message | The actual message (memo) |
| Read | A boolean expression whether the user can "read" this. I used this to create an AOL-like "unsend" (number or BIT) (0=cant view, (1/-1)=can read) If it is 0, it will be deleted periodically. But this way, you can "unsend" your unsend |
Once I created the table, then it became the task of creating the ASP pages. I opted for a multi-page approach, because I did not want to isolate all the code in one page using subroutines. Not that it would have been harder, but for this I will still use the multipage approach.
The first part is to create a message main page, that shows what messages are new, and lets the user quicklink to them. Here is the none spruced ASP for this page
<%
If IsNull(Session("StudentName")) or Session("StudentName")="" Then
Response.redirect "students_only.asp"
Else
%>
<html>
[ code from above ]
<head>
<title>My Messaging Station<title>
</head>
<body>
<center>
<h2>Welcome, <%=Session("StudentName")%></h2>
</center>
This code here just sets up the frame code, and displays the user a personalized greeting, based on a session variable I had read out of the student database.
[ code from aove ]
<table border="1" width="550">
<tr>
<td width="50">Date</td>
<td width="100">From</td>
<td width="400">Subject (Click to view message)</td>
</tr>
Next, I setup the first row to have the generic view. This will just show them the new messages, which is why I don't have a new marker.
[ code from above ]
<%
Set conntemp = Server.CreateObject("ADODB.Connection")
strSource = "DRIVER=Microsoft Access Driver " &
"(*.mdb);UID=admin;FIL=MS Access;DBQ=" &
Server.MapPath("myAccessDatabase.mdb")
conntemp.open strSource
strExec="SELECT * FROM SentMessages WHERE TO=" &
Session("DHSID") &
" AND READ=1 and IsNew=1"
set rstemp=conntemp.execute(strExec)
Next, let's check that the user actually has some messages, and if they don't have any, send a message saying "No New Messages", and skip to the bottom of the if-then statement.
[ code from above. Note, we are still in the script because there has
been no %> yet ]
If rsTemp.EOF = True Then %>
<tr>
<td colspan="3">
<p align="center">
<font color="#660000">
No New Messages
</font>
</td>
</tr>
[ code from above ]
<% Else
Do While Not RSTemp.EOF '(Begin the loop here)
%>
<tr>
<td>
<%=rstemp("Date")%>
</td>
[ code from above ]
<% set conntemp2 = Server.CreateObject("ADODB.Connection")
strSource2 = "DRIVER=Microsoft Access " &
"Driver (*.mdb);UID=admin;FIL=MS Access;DBQ=" &
Server.MapPath("myDatabase")
conntemp2.open strSource2
strExec2="Select * From Students WHERE DHSID=" &
rsTemp("From")
set rstemp2=conntemp2.execute(strExec2)
%>DHSID,
and checks the database. Next, error control ...
[ code from above ]
<% If NOT rsTemp2.EOF Then %>
<td>
<%=rstemp2("StudentName")%>
</td>
<% Else %>
<Td>Error!</td>
<% End If %>
DHSID is the primary key
for the other table, so I should only have one return at any given time
when I search it. Next, show the subject of the message and include a
link to the message viewing page.
[ code from above ]
<td>
<a href="viewmessage.asp?MID=<%=rstemp("MessageID")%>">
<%=rstemp("Subject")%>
</a>
</td>
</tr>
<%
rsTemp.MoveNext
Loop
End If %>
[ code from above ]
</table>
<html>
<%
End If
%>
This has been Part 1 of my messaging series. Here, I've gone over how to display a page that shows the user new messages that he/she might have.
In this series, I'll be discussing how I setup an intranet messaging system. I used no Microsoft components, or any components for that matter, except for the ADODB component.
I have planned for the next part to be on the ViewMessage.ASP source,
which is the actual message viewer. Then I will show you the general
"in-box" code, that displays all messages, new or old. After that, I
will finally get onto the actual message sending code. I figure I should
make you wait until the end so that you can start to understand how I
setup the database and the other information.
(See the article Data Modeling 101 for information
on why data modeling is so important. Visit Data Modeling 201
to learn some basic data modeling techniques!)
God Bless, and Happy Coding!
This article was written by
Ryan S.
Ryan has been a computer programmer
in the loosest sense since the age of 8. He has been working with ASP
since the age of 13, when it first came out (that he knows of), and is
somewhat advanced at it.
