When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, October 06, 1999

Hooray! - Creating a Customized homepage (using a database), Part 3
By Christopher Miller


This article is the third part in a 4 part series (read Part 1 | Part 2) . The series will be a complete tutorial of creating your own search engine and web portal called - Hooray! I will be discussing the following techniques:

- continued -

  1. Sorting and Searching Database Tables
  2. Adding, Deleting, and Updating Records in a Database
  3. Creating a Customized homepage (using a database to store preferences)
  4. Putting It All Together (combines all previous articles as well as others to create Hooray!)

Some of you may have read my article on creating a customized homepage. The article discusses how to create a homepage using cookies to store the user preferences. This article is along the same lines (and even uses the same look and feel) but with one exception, we will be using a database to store the user preferences. The only cookie we need is for storage of the unique user ID that we will be using to display the custom page. This will allow us to eliminate the need for a login screen.

This method is much easier to manage and will produce a much better end result. So let's get started...

Here is an example.

We will be using 7 ASP pages in all:

  • global.asa
  • default.asp
  • register.asp
  • my_hooray.asp
  • edit_globals.asp
  • edit_links.asp
  • edit_emails.asp
The database (hooray.mdb) will contain 4 tables with the format below:

tblUsers:
uID AutoNumber
uName Text
uEmail Text
 
tblFormatting:
fID AutoNumber
fTextColor Text
FLinkColor Text
fVLinkColor Text
fALinkColor Text
fBGColor Text
fUserID Text
fTableColor Text
 
tblLinks:
lID AutoNumber
lUserID Number
lLInk Text
lName Text
 
tblAddressBook:
aID AutoNumber
aUserID Number
aEmail Text
aName Text

Let's start with the global.asa file. I am simply using this to store the path of the database. This allows us to move the database to a different location without much modification.

<SCRIPT LANGUAGE="VBScript" RUNAT="Server">

SUB Session_OnStart

Session("DatabasePath") = "C:\databases\"

END SUB

</SCRIPT>

Next, the default.asp page simply checks for the existance of a cookie on the client’s machine. If there is no cookie stored, we send the user to a registration page (register.asp).

<%
'-- check for User Id

If Request.Cookies("UserID") = "" Then
Response.Redirect("register.asp")
Else
Session("UserID") = Request.Cookies("UserID")
Response.Redirect("my_hooray.asp")
End If
%>
Which brings us to the register.asp page. We will be using this page to create the user profile in the database. I am only going to be using 2 fields to create a user: name and email address. You can, of course, grab any data you wish from the users. The HTML is irrelevant, so I am going to concentrate on the ASP. We first need to add the user to the database when the 'register' button is clicked:

<%
If Request.Form("btnRegister") = "Register" Then

Dim DataConnection, cmdDC, RecordSet, SQL, varDatabaseName

varDatabaseName = "hooray.mdb"
SQL = "SELECT * FROM tblUsers;"
%>

<!--#include file="common/data_conn_open.asp"-->

<%
RecordSet.AddNew

RecordSet.Fields("uName") = Request.Form("txtName")
RecordSet.Fields("uEmail") = Request.Form("txtEmail")

RecordSet.Update
RecordSet.ReQuery

'-- get New User ID

If Not RecordSet.BOF Then
RecordSet.MoveFirst
End If

Do Until RecordSet.EOF

If RecordSet.Fields("uName") = Request.Form("txtName") Then
If RecordSet.Fields("uEmail") = Request.Form("txtEmail") Then

Session("UserID") = RecordSet.Fields("uID")

End If
End If

RecordSet.MoveNext
Loop
%>

<!--#include file="common/data_conn_close.asp"-->

You will notice that I am using includes for the database opening and closing codes. I got into this habit because it is much easier to manipulate the code that is used over and over. I then store the database name and SQL into strings located on each individual page so that I can customize the database connection as I wish.

Once we have the new user in the database, and we have their unique ID stored in a session variable, we can add them to the formatting database. I have set the default values right in the database rather than on the homepage itself to make the code a lot cleaner and easier to manage.

<%
'-- Add new user to the formatting database

SQL = "SELECT tblFormatting.fUserID FROM tblFormatting;"
%>

<!--#include file="common/data_conn_open.asp"-->

<%
RecordSet.AddNew

RecordSet.Fields("fUserID") = Session("UserID")

RecordSet.Update
%>

<!--#include file="common/data_conn_close.asp"-->

Once the user is aded into the formatting table, we need to set the cookies:

<%
'-- Set the user id cookie

Response.Cookies("UserID") = Session("UserID")
Response.Cookies("UserID").Expires = Date() + 365

'-- Send the user to their homepage when done

Response.Redirect("default.asp")

End If
%>

That is it for register.asp, let's move on to my_hooray.asp. This is the page that the user will be using as their personal homepage. The first thing is to grab the user's preferences from the database:

<%
Dim DataConnection, cmdDC, RecordSet, SQL, varDatabaseName
Dim varUserName, varUserEmail
Dim varfTextColor, varfLinkColor, varfvLinkColor
Dim varfALinkColor, varfBGColor, varfTableColor

varDatabaseName = "hooray.mdb"

'-- Default SQL to get name and formatting

SQL = "SELECT tblUsers.uName, tblFormatting.*, tblUsers.uID, tblUsers.uEmail "
SQL = SQL & "FROM tblFormatting INNER JOIN tblUsers ON tblFormatting.fUserID = tblUsers.uID "
SQL = SQL & "WHERE (((tblUsers.uID)=" & Session("UserID") & "));"
%>

<!--#include file="common/data_conn_open.asp"-->

<%
If Not RecordSet.BOF Then
RecordSet.MoveFirst

varUserEmail = RecordSet.Fields("uEmail")
varUserName = RecordSet.Fields("uName")
varfTextColor = RecordSet.Fields("fTextColor")
varfLinkColor = RecordSet.Fields("fLinkColor")
varfVLinkColor = RecordSet.Fields("fvLinkColor")
varfALinkColor = RecordSet.Fields("fALinkColor")
varfBGColor = RecordSet.Fields("fBGColor")
varfTableColor = RecordSet.Fields("fTableColor")

End If
%>

<!--#include file="common/data_conn_close.asp"-->

I am storing the 'general' preferences in style sheets rather than in a <body> tag:

<STYLE TYPE="text/css">
<--
BODY {
color : <%= varfTextColor %>;
background-color : <%= varfBGColor %>;
}
A:Active {
color : <%= varfALinkColor %>;
}
A:Visited {
color : <%= varfVLinkColor %>;
}
A:Link {
color : <%= varfLinkColor %>;
}
-->
</STYLE>

After we have our homepage set-up the way we want (again, I am not going to concentrate on the HTML, you can set up the page however you want), we can start inserting the links and the address book:

<%
'--Insert links

SQL = "SELECT tblLinks.lLink, tblLinks.lName "
SQL = SQL & "FROM tblLinks "
SQL = SQL & "WHERE (((tblLinks.lUserID)=" & Session("UserID") & ")) "
SQL = SQL & "ORDER BY tblLinks.lName;"
%>

<!--#include file="common/data_conn_open.asp"-->

<%
If Not RecordSet.BOF Then
RecordSet.MoveFirst
Do Until RecordSet.EOF
%>

<a href="<%= RecordSet.Fields("lLink") %>"><%= RecordSet.Fields("lName") %></a><br>

<%
RecordSet.MoveNext
Loop
End If

If RecordSet.recordCount = 0 Then
Response.Write("<i>You have no links saved...</i>")
End If
%>

<!--#include file="common/data_conn_close.asp"-->

<%
'--Insert Email addresses

SQL = "SELECT tblAddressBook.aEmail, tblAddressBook.aName "
SQL = SQL & "FROM tblAddressBook "
SQL = SQL & "WHERE (((tblAddressBook.aUserID)=" & Session("UserID") & ")) "
SQL = SQL & "ORDER BY tblAddressBook.aName;"
%>

<!--#include file="common/data_conn_open.asp"-->

<%
If Not RecordSet.BOF Then
RecordSet.MoveFirst
Do Until RecordSet.EOF
%>

<option value="<%= RecordSet.Fields("aEmail") %>"><%= RecordSet.Fields("aName") %></option>

<%
RecordSet.MoveNext
Loop
End If
%>

<!--#include file="common/data_conn_close.asp"-->

You will notice the <option> tag that I insert my email address into. That is because I have set-up the page to have an email form right on it (as with the previous customized homepage article). I prefer to use CDONTS to send my mail, but there are dozens of ASP mail components that you can use. I will let you decide for yourself.

Let's move onto edit_globals.asp, edit_emails.asp, and edit_links.asp. All three pages do essentially the same thing. I use a SQL statement to grab only the records that belong to a particular user, and then display and save new values. I am simply going to list all 3 pages below (including the HTML).

edit_globals.asp

<%
Dim DataConnection, cmdDC, RecordSet, SQL, varDatabaseName
Dim varUserName, varUserEmail
Dim varfTextColor, varfLinkColor, varfvLinkColor
Dim varfALinkColor, varfBGColor, varfTableColor
varDatabaseName = "hooray.mdb"

'-- Default SQL to get name and formatting

SQL = "SELECT tblUsers.uName, tblFormatting.*, tblUsers.uID, tblUsers.uEmail "
SQL = SQL & "FROM tblFormatting INNER JOIN tblUsers ON tblFormatting.fUserID = tblUsers.uID "
SQL = SQL & "WHERE (((tblUsers.uID)=" & Session("UserID") & "));"
%>

<!--#include file="common/data_conn_open.asp"-->

<%
'-- Insert New values

If Request.Form("btnSave") = "Save" Then

If Not RecordSet.BOF Then
RecordSet.MoveFirst

RecordSet.Fields("uEmail") = Request.Form("txtEmail")
RecordSet.Fields("uName") = Request.Form("txtName")
RecordSet.Fields("fTextColor") = Request.Form("txtTextColor")
RecordSet.Fields("fLinkColor") = Request.Form("txtLColor")
RecordSet.Fields("fVLinkColor") = Request.Form("txtVColor")
RecordSet.Fields("fALinkColor") = Request.Form("txtAColor")
RecordSet.Fields("fBGColor") = Request.Form("txtBGColor")
RecordSet.Fields("fTableColor") = Request.Form("txtTableColor")

RecordSet.Update
RecordSet.ReQuery

End If
End If

'-- Get values

If Not RecordSet.BOF Then
RecordSet.MoveFirst

varUserEmail = RecordSet.Fields("uEmail")
varUserName = RecordSet.Fields("uName")
varfTextColor = RecordSet.Fields("fTextColor")
varfLinkColor = RecordSet.Fields("fLinkColor")
varfVLinkColor = RecordSet.Fields("fvLinkColor")
varfALinkColor = RecordSet.Fields("fALinkColor")
varfBGColor = RecordSet.Fields("fBGColor")
varfTableColor = RecordSet.Fields("fTableColor")

End If
%>

<!--#include file="common/data_conn_close.asp"-->

<html>
<body>

<center>

<font size="4">Editing Globals for <%= varUserName %></font><P>

<form action="edit_globals.asp" method="post">

<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td align="right">Name:</td>
<td><input type="text" name="txtName" value="<%= varUserName %>" size="20"></td>
</tr>
<tr>
<td align="right">Email:</td>
<td><input type="text" name="txtEmail" value="<%= varUserEmail %>" size="20"></td>
</tr>
<tr>
<td align="right">Text Color:</td>
<td><input type="text" name="txtTextColor" value="<%= varfTextColor %>" size="20"></td>
</tr>
<tr>
<td align="right">Background Color:</td>
<td><input type="text" name="txtBGColor" value="<%= varfBGColor %>" size="20"></td>
</tr>
<tr>
<td align="right">Table Color:</td>
<td><input type="text" name="txtTableColor" value="<%= varfTableColor %>" size="20"></td>
</tr>
<tr>
<td align="right">Link Color:</td>
<td><input type="text" name="txtLColor" value="<%= varfLinkColor %>" size="20"></td>
</tr>
<tr>
<td align="right">Visited Link Color:</td>
<td><input type="text" name="txtVColor" value="<%= varfVLinkColor %>" size="20"></td>
</tr>
<tr>
<td align="right">Active Link Color:</td>
<td><input type="text" name="txtAColor" value="<%= varfALinkColor %>" size="20"></td>
</tr>
<tr>
<td colspan="2" align="center"> </td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" name="btnSave" value="Save"></td>
</tr>
</table>

</form>

<p>

<a href="my_hooray.asp">Back Home</a>

</center>

</body>
</html>

edit_emails.asp

<%
Dim DataConnection, cmdDC, RecordSet, SQL, varDatabaseName

varDatabaseName = "hooray.mdb"

SQL = "SELECT tblAddressBook.*, tblAddressBook.aUserID "
SQL = SQL & "FROM tblAddressBook "
SQL = SQL & "WHERE (((tblAddressBook.aUserID)=" & Session("UserID") & "));"
%>

<!--#include file="common/data_conn_open.asp"-->

<%
If Request.QueryString("Action") = "Delete" Then

Dim RecordToDelete

RecordToDelete = CInt(Request.QueryString("ID"))

If Not RecordSet.BOF Then
RecordSet.MoveFirst
End If

Do Until RecordSet.Fields("aID") = RecordToDelete

RecordSet.MoveNext
Loop

RecordSet.Delete
RecordSet.Requery

End If

If Request.Form("btnSave") = "Save Email" Then

RecordSet.AddNew

RecordSet.Fields("aUserID") = Session("UserID")
RecordSet.Fields("aName") = Request.Form("txtName")
RecordSet.Fields("aEmail") = Request.Form("txtEmail")

RecordSet.Update
RecordSet.Requery

End If
%>

<html>
<body>

<table border="1" cellpadding="3" cellspacing="0" width="100%">
<tr>
<td align="center"><b>Action</b></td>
<td align="center"><b>Name</b></td>
<td align="center"><b>Eamil</b></td>
</tr>

<%
If Not RecordSet.BOF Then
RecordSet.MoveFirst
End If

Do Until RecordSet.EOF
%>

<tr>
<td><a href="edit_emails.asp?Action=Delete&ID=<%= RecordSet.Fields("aID") %>">Delete</a></td>
<td><%= RecordSet.Fields("aName") %></td>
<td><%= RecordSet.Fields("aEmail") %></td>
</tr>

<%
RecordSet.MoveNext

Loop
%>

</table>

<!--#include file="common/data_conn_close.asp"-->

<p align="center">

<form action="edit_emails.asp" method="post">

Name: <input type="text" name="txtName" size="30">  
Email: <input type="text" name="txtEmail" size="30">  
<input type="submit" name="btnSave" value="Save Email">

</form>

<p align="center">

<a href="my_hooray.asp">Back Home</a>

</body>
</html>

edit_links.asp

<%
Dim DataConnection, cmdDC, RecordSet, SQL, varDatabaseName

varDatabaseName = "hooray.mdb"

SQL = "SELECT tblLinks.*, tblLinks.lUserID "
SQL = SQL & "FROM tblLinks "
SQL = SQL & "WHERE (((tblLinks.lUserID)=" & Session("UserID") & "));"
%>

<!--#include file="common/data_conn_open.asp"-->

<%
If Request.QueryString("Action") = "Delete" Then

Dim RecordToDelete

RecordToDelete = Int(Request.QueryString("ID"))

If Not RecordSet.BOF Then
RecordSet.MoveFirst
End If

Do Until RecordSet.Fields("lID") = RecordToDelete

RecordSet.MoveNext
Loop

RecordSet.Delete

End If

If Request.Form("btnSave") = "Save Link" Then

RecordSet.AddNew

RecordSet.Fields("lUserID") = Session("UserID")
RecordSet.Fields("lName") = Request.Form("txtName")
RecordSet.Fields("lLink") = Request.Form("txtURL")

RecordSet.Update
RecordSet.Requery

End If
%>

<html>
<body>

<table border="1" cellpadding="3" cellspacing="0" width="100%">
<tr>
<td align="center"><b>Action</b></td>
<td align="center"><b>Name</b></td>
<td align="center"><b>URL</b></td>
</tr>

<%
If Not RecordSet.BOF Then
RecordSet.MoveFirst
End If

Do Until RecordSet.EOF
%>

<tr>
<td><a href="edit_links.asp?Action=Delete&ID=<%= RecordSet.Fields("lID") %>">Delete</a></td>
<td><%= RecordSet.Fields("lName") %></td>
<td><%= RecordSet.Fields("lLink") %></td>
</tr>

<%
RecordSet.MoveNext

Loop
%>

</table>

<!--#include file="common/data_conn_close.asp"-->

<p align="center">

<form action="edit_links.asp" method="post">

Name: <input type="text" name="txtName" size="30">  
URL: <input type="text" name="txtURL" size="30">  
<input type="submit" name="btnSave" value="Save Link">

</form>

<p align="center">

<a href="my_hooray.asp">Back Home</a>

</body>
</html>

That is pretty much all there is to it. I have left the code pretty wide open so you can customize it however you want. You may want to incorporate (and you will see it on the example) a 'start over' option. I added this so I could test and debug the code and it seemed to me like it may be a good feature to ad anyway. Here is the 'start over' code:

<%
Dim Item
Dim DataConnection, cmdDC, RecordSet, SQL

For Each Item In Request.Cookies
Response.Cookies(Item) = ""
Next

varDatabaseName = "hooray.mdb"

SQL = "SELECT tblUsers.uID, tblUsers.* "
SQL = SQL & "FROM tblUsers "
SQL = SQL & "WHERE (((tblUsers.uID)=" & Session("UserID") & "));"
%>

<!--#include file="common/data_conn_open.asp"-->

<% RecordSet.Delete %>

<!--#include file="common/data_conn_close.asp"-->

<%
Session.Abandon

Response.Redirect("default.asp")
%>

Keep your eyes open for my next arrticle that will put this (and all of my other articles) into one giant application called Hooray!. Enjoy!

  • By Christopher Miller...


    Related Articles

  • Hooray!, Part 1
  • Hooray!, Part 2


    Attachments:

  • Download the code in ZIP format

  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article