Creating a Guestbook
This lesson requires that you have Access installed on your system and is not supported on 64-bit platforms until Access is developed for 64-bit platforms.
Guest books allow your Web site visitors to leave behind information, such as their names, e-mail addresses, and comments. In this lesson, you perform the following tasks after creating an Access database:
Example 1: Create an ASP page to connect to the database using only the ADO Connection object.
Example 2: Create an ASP page to connect to the database using the Connection object and the Command object together.
Example 3: Create an ASP page to display the guest book information from the database in a browser.
Create an Access Database
Create an Access database called GuestBook.mdb, and save it in x:\Inetpub\Wwwroot\Tutorial. Create a table in the database called GuestBook. Use the Create Table Using Design View option in Access to add the following fields and properties:
Field Name |
Data Type |
Field General Properties |
|---|---|---|
FID |
AutoNumber |
Field Size=Long Integer, New Values=Increment, Indexed=Yes(No Duplicates) |
FTB1 |
Text |
Field Size=255, Required=No, Allow Zero Length=Yes, Indexed=No |
FTB2 |
Text |
Field Size=255, Required=No, Allow Zero Length=Yes, Indexed=No |
FTB3 |
Text |
Field Size=255, Required=No, Allow Zero Length=Yes, Indexed=No |
FTB4 |
Text |
Field Size=255, Required=No, Allow Zero Length=Yes, Indexed=No |
FMB1 |
Memo |
Required=No, Allow Zero Length=Yes |
Create an ASP Page to Add Data to Your Access Database
Now that you have created a database, you can build an ASP page to connect to your database and read incoming data using ActiveX Data Objects (ADO). ADO is a collection of objects with methods and properties that can manipulate data in almost any type of database. (If you plan to use databases frequently, you should purchase a programmer's reference book for ADO. Only the most basic ADO code is illustrated in the following examples, enough to open, read in, and write to a database.)
The next two examples produce the same results; however, the first example uses only the Connection object, and the second example gives part of the job to the Command object, which is much more powerful. Compare both examples to see how the objects become connected together. After you are comfortable with the objects, use an ADO programmer's reference to experiment with more methods and properties.
To see an example of an ADO error in your ASP page, try browsing to the page after renaming your database, after entering a typing mistake in the connection string, or after making the database Read Only.
Example 1: Using Only the ADO Connection Object
Copy and paste the following code in your text editor, and save the file as GuestBook1.asp in the x:\Inetpub\Wwwroot\Tutorial directory. View the example with your browser by typing https://localhost/Tutorial/GuestBook1.asp in the address bar.
<%@ Language= "VBScript" %>
<html>
<head>
<title>Guest Book Using Connection Object Only</title>
</head>
<body>
<font face="MS Gothic">
<h2>Guest Book Using Connection Object Only</h2>
<%
If Not Server.HTMLEncode(Request.QueryString("Message")) = "True" Then
'No information has been input yet, so provide the form.
%>
<p>
<FORM NAME="GuestBook1" METHOD="GET" ACTION="guestbook1.asp">
<table>
<tr>
<td><font face="MS Gothic">From:</td><td><INPUT TYPE="TEXT" NAME="From"></td>
</tr><tr>
<td><font face="MS Gothic">E-mail Address:</td><td><INPUT TYPE="TEXT" NAME="EmailAdd"></td>
</tr><tr>
<td><font face="MS Gothic">CC:</td><td><INPUT TYPE="TEXT" NAME="CC"></td>
</tr><tr>
<td><font face="MS Gothic">Subject:</td><td><INPUT TYPE="TEXT" NAME="Subject"></td>
</tr>
</table>
Message:<br><TEXTAREA NAME="Memo" ROWS=6 COLS=70></TEXTAREA>
</p>
<p>
<INPUT TYPE="HIDDEN" NAME="Message" VALUE="True">
<INPUT TYPE="SUBMIT" VALUE="Submit Information">
</FORM>
</p>
<%
Else
'The HIDDEN button above sets the Message variable to True.
'We know now that form data has been entered.
'Get the data from the form. We will be inserting it into the database.
'Access doesn't like some characters, such as single-quotes, so encode the
' data using the HTMLEncode method of the ASP Server object.
dim strTB1, strTB2, strTB3, strTB4, strMB1, strCommand
strTB1 = Server.HTMLEncode(Request.QueryString("From"))
strTB2 = Server.HTMLEncode(Request.QueryString("EMailAdd"))
strTB3 = Server.HTMLEncode(Request.QueryString("CC"))
strTB4 = Server.HTMLEncode(Request.QueryString("Subject"))
strMB1 = Server.HTMLEncode(Request.QueryString("Memo"))
'This is a connection string. ADO uses it to connect to a database through the Access driver.
'It needs the provider name of the Access driver and the name of the Access database.
'Connection strings are slightly different, depending on the provider being used,
' but they all use semicolons to separate variables.
'If this line causes and error, search in your registry for
' Microsoft.JET to see if 4.0 is your version.
strProvider = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\InetPub\Wwwroot\Tutorial\guestbook.mdb;"
'This creates an instance of an ADO Connection object.
'There are 4 other ADO objects available to you, each with different methods and
'properties that allow you to do almost anything with database data.
Set objConn = server.createobject("ADODB.Connection")
'The Open method of the Connection object uses the connection string to
' create a connection to the database.
objConn.Open strProvider
'Define the query.
'There are many types of queries, allowing you to add, remove, or get data.
'This query will add your data into the database, using the INSERT INTO key words.
'Here, GuestBook is the name of the table.
'You need single-quotes around strings here.
strCommand = "INSERT INTO GuestBook (FTB1,FTB2,FTB3,FTB4,FMB1) VALUES ('"
strCommand = strCommand & strTB1 & "','" & strTB2 & "','" & strTB3 & "','" & strTB4 & "','" & strMB1
strCommand = strCommand & "')"
'Execute the query to add the data to the database.
objConn.Execute strCommand
Response.Write("Thank you! Your data has been added.")
End If
%>
</font>
</body>
</html>
In the browser, you should see the following:
Guest Book Using Connection Object Only
From: |
.gif "Text Box") |
E-mail Address: |
|
CC: |
|
Subject: |
|
Message:
Example 2: Using the Connection Object and the Command Object Together
Copy and paste the following code in your text editor, and save the file as GuestBook2.asp in the x:\Inetpub\Wwwroot\Tutorial directory. View the example with your browser by typing https://localhost/Tutorial/GuestBook2.asp in the address bar.
<%@ Language= "VBScript" %>
<html>
<head>
<title>Guest Book Using Connection Object and Command Object</title>
</head>
<body>
<font face="MS Gothic">
<h2>Guest Book Using Connection Object and Command Object</h2>
<%
If Not Server.HTMLEncode(Request.QueryString("Message")) = "True" Then
'No information has been input yet, so provide the form.
%>
<p>
<FORM NAME="GuestBook2" METHOD="GET" ACTION="guestbook2.asp">
<table>
<tr>
<td><font face="MS Gothic">From:</td><td><INPUT TYPE="TEXT" NAME="From"></td>
</tr><tr>
<td><font face="MS Gothic">E-mail Address:</td><td><INPUT TYPE="TEXT" NAME="EmailAdd"></td>
</tr><tr>
<td><font face="MS Gothic">CC:</td><td><INPUT TYPE="TEXT" NAME="CC"></td>
</tr><tr>
<td><font face="MS Gothic">Subject:</td><td><INPUT TYPE="TEXT" NAME="Subject"></td>
</tr>
</table>
Message:<br><TEXTAREA NAME="Memo" ROWS=6 COLS=70></TEXTAREA>
</p>
<p>
<INPUT TYPE="HIDDEN" NAME="Message" VALUE="True">
<INPUT TYPE="SUBMIT" VALUE="Submit Information">
</FORM>
</p>
<%
Else
'The HIDDEN button above sets the Message variable to True.
'We know now that form data has been entered.
'Get the data from the form. We will be inserting it into the database.
'Access doesn't like some characters, such as single-quotes, so encode the
' data using the HTMLEncode method of the ASP Server object.
dim strTB1, strTB2, strTB3, strTB4, strMB1
strTB1 = Server.HTMLEncode(Request.QueryString("From"))
strTB2 = Server.HTMLEncode(Request.QueryString("EMailAdd"))
strTB3 = Server.HTMLEncode(Request.QueryString("CC"))
strTB4 = Server.HTMLEncode(Request.QueryString("Subject"))
strMB1 = Server.HTMLEncode(Request.QueryString("Memo"))
'The Memo data type in the Access database allows you to set the field size.
If strMB1 = "" Then
iLenMB1 = 255
Else
iLenMB1 = Len(strMB1)
End If
'This is a connection string. ADO uses it to connect to a database through the Access driver.
'It needs the provider name of the Access driver and the name of the Access database.
'Connection strings are slightly different, depending on the provider being used,
' but they all use semicolons to separate variables.
'If this line causes and error, search in your registry for
' Microsoft.JET to see if 4.0 is your version.
strProvider = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\InetPub\Wwwroot\Tutorial\guestbook.mdb;"
'This creates an instance of an ADO Connection object.
'There are 4 other ADO objects available to you, each with different methods and
'properties that allow you to do almost anything with database data.
Set objConn = server.createobject("ADODB.Connection")
'The Open method of the Connection object uses the connection string to
' create a connection to the database.
objConn.Open strProvider
'This creates an instance of an ADO Command object.
'Although you could do most of your work with the Connection object,
' the Command object gives you more control.
Set cm = Server.CreateObject("ADODB.Command")
'The ActiveConnection property allows you to attach to an Open connection.
'This is how you link the Connection object above to the Command object.
cm.ActiveConnection = objConn
'Define the query.
'There are many types of queries, allowing you to add, remove, or get data.
'This query will add your data into the database, using the INSERT INTO keywords.
'Because we are using the Command object, we need to put our query into the
' CommandText property.
'Here, GuestBook is the name of the table.
cm.CommandText = "INSERT INTO GuestBook (FTB1,FTB2,FTB3,FTB4,FMB1) VALUES (?,?,?,?,?)"
'This is where you see the power of the Command object.
'By putting ? marks in the string above, we can use the Parameters collection
' to have ADO fill in the ? with the detailed parameters below.
'cm.CreateParameter formats the parameter for you.
'cm.Parameters.Append appends the parameter to the collection.
'Make sure they are in the same order as (TB1,TB2,TB3,TB4,MB1).
Set objparam = cm.CreateParameter(, 200, , 255, strTB1)
cm.Parameters.Append objparam
Set objparam = cm.CreateParameter(, 200, , 255, strTB2)
cm.Parameters.Append objparam
Set objparam = cm.CreateParameter(, 200, , 255, strTB3)
cm.Parameters.Append objparam
Set objparam = cm.CreateParameter(, 200, , 255, strTB4)
cm.Parameters.Append objparam
Set objparam = cm.CreateParameter(, 201, , iLenMB1, strMB1)
cm.Parameters.Append objparam
'Execute the query to add the data to the database.
'Here, the Execute method of the Command object is called instead of
' the Execute method of the Connection object.
cm.Execute
Response.Write("Thank you! Your data has been added.")
End If
%>
</font>
</body>
</html>
In the browser, you should see the same content as GuestBook1.asp, as follows:
Guest Book Using Connection Object Only
From: |
|
E-mail Address: |
|
CC: |
|
Subject: |
|
Message:
Example 3: Display the Database in a Browser
After information is entered in a database, you can use a Web page containing another script to view and edit the data. Not much changes in the ADO code, except the way you define your query. In the last two examples, you used the INSERT INTO query to add records to a database. In this example, you use the SELECT query to choose records from a database and display them in the browser. You also use the DELETE query to remove records from the database. The only query not used in these examples is the UPDATE query, whose syntax is like that of the INSERT INTO query. The UPDATE query allows you to change fields in a database.
Copy and paste the following code in your text editor, and save the file as ViewGB.asp in the x:\Inetpub\Wwwroot\Tutorial directory. View the example with your browser by typing https://localhost/Tutorial/ViewGB.asp in the address bar.
<%@ Language= "VBScript" %>
<html>
<head>
<title>View Guest Book</title>
</head>
<body>
<font face="MS Gothic">
<h2>View Guest Book</h2>
<%
'Read in any user input. Any of these can be empty.
'By doing this first, we can preserve the user input in the form.
dim strTB1, strTB2, strTB3, strTB4, strMB1, strSort, iDelete
strTB1 = Server.HTMLEncode(Request.QueryString("From"))
strTB2 = Server.HTMLEncode(Request.QueryString("EMailAdd"))
strTB3 = Server.HTMLEncode(Request.QueryString("CC"))
strTB4 = Server.HTMLEncode(Request.QueryString("Subject"))
strMB1 = Server.HTMLEncode(Request.QueryString("Memo"))
strSort = Server.HTMLEncode(Request.QueryString("sort"))
iDelete = CInt(Request.QueryString("Delete"))
'Because we use this variable, and it might not be set...
If "" = strSort Then
strSort = "FID"
End If
%>
<p>
<FORM NAME="ViewGuestBook" METHOD="GET" ACTION="viewgb.asp">
<table>
<tr>
<td><font face="MS Gothic">Sort by which column:</td>
<td><SELECT NAME="sort" SIZE="1">
<OPTION VALUE="FID">ID Number</OPTION>
<OPTION VALUE="FTB1">Name</OPTION>
<OPTION VALUE="FTB2">Email</OPTION>
<OPTION VALUE="FTB3">CC</OPTION>
<OPTION VALUE="FTB4">Subject</OPTION>
<OPTION VALUE="FMB1">Memo</OPTION>
</SELECT></td>
</tr><tr>
<td><font face="MS Gothic">Name Contains:</td>
<td><INPUT TYPE="TEXT" NAME="From" VALUE="<%=strTB1%>"></td>
</tr><tr>
<td><font face="MS Gothic">E-mail Address Contains:</td>
<td><INPUT TYPE="TEXT" NAME="EmailAdd" VALUE="<%=strTB2%>"></td>
</tr><tr>
<td><font face="MS Gothic">CC Contains:</td>
<td><INPUT TYPE="TEXT" NAME="CC" VALUE="<%=strTB3%>"></td>
</tr><tr>
<td><font face="MS Gothic">Subject Contains:</td>
<td><INPUT TYPE="TEXT" NAME="Subject" VALUE="<%=strTB4%>"></td>
</tr><tr>
<td><font face="MS Gothic">Memo Contains:</td>
<td><INPUT TYPE="TEXT" NAME="Memo" VALUE="<%=strMB1%>"></td>
</tr>
</table>
<INPUT TYPE="SUBMIT" VALUE="Submit Search Parameters">
</p>
<%
'Create your connection string, create an instance of the Connection object,
' and connect to the database.
strProvider = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\InetPub\Wwwroot\Tutorial\guestbook.mdb;"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strProvider
'Define the query.
If iDelete = 0 Then
'If the Delete variable is not set, the query is a SELECT query.
'* means all fields. ASC means ASCII. % is a wildcard character.
strQuery = "SELECT * FROM GuestBook"
strQuery = strQuery & " WHERE FTB1 LIKE '%" & strTB1 & "%'"
strQuery = strQuery & " AND FTB2 LIKE '%" & strTB2 & "%'"
strQuery = strQuery & " AND FTB3 LIKE '%" & strTB3 & "%'"
strQuery = strQuery & " AND FTB4 LIKE '%" & strTB4 & "%'"
strQuery = strQuery & " AND FMB1 LIKE '%" & strMB1 & "%'"
strQuery = strQuery & " ORDER BY " & StrSort & " ASC"
Else
'We want to delete a record.
strQuery = "DELETE FROM GuestBook WHERE FID=" & iDelete
End If
'Executing the SELECT query creates an ADO Recordset object.
'This holds the data you get from the database.
Set objRS = objConn.Execute(strQuery)
'Now that you have the database data stored in the Recordset object,
' show it in a table.
%>
<p>
<FORM NAME="EditGuestBook" METHOD="GET" ACTION="viewgb.asp">
<table border=1 cellpadding=4 >
<%
On Error Resume Next
If objRS.EOF Then
If iDelete = 0 Then
Response.Write "<tr><td><font face="MS Gothic">There are no entries in the database.</font></td></tr>"
Else
Response.Write "<tr><td><font face="MS Gothic">Record " & iDelete & " was deleted.</font></td></tr>"
End If
Else
'Print out the field names using some of the methods and properties
' of the Recordset object.
Response.Write "<tr>"
'For each column in the current row...
For i = 1 to (objRS.Fields.Count - 1)
' write out the field name.
Response.Write "<td><font face="MS Gothic"><B>" & objRS(i).Name & "</B></font></td>"
Next
Response.Write "<td><font face="MS Gothic"><B>Delete</B></font></td>"
Response.Write "</tr>"
'Print out the field data, using some other methods and properties
' of the Recordset object. When you see a pattern in how they are used,
' you can look up others and experiment.
'While not at the end of the records in the set...
While Not objRS.EOF
Response.Write "<tr>"
'For each column in the current row...
For i = 1 to (objRS.Fields.Count - 1)
' write out the data in the field.
Response.Write "<td><font face="MS Gothic">" & objRS(i) & "</font></td>"
Next
'Add a button that will pass in an ID number to delete a record.
%><td><INPUT TYPE="SUBMIT" NAME="Delete" VALUE="<%=objRS(0)%>"></td><%
Response.Write "</tr>"
'Move to the next row.
objRS.MoveNext
Wend
End If 'objRS.EOF
%>
</table>
</FORM>
<%
'Close the Connection.
objConn.Close
%>
</font>
</body>
</html>
In the browser, you should see the following:
View Guest Book
Sort by which column: |
.gif "Check Box") |
Name Contains: |
|
E-mail Address Contains: |
|
CC Contains: |
|
Subject Contains: |
|
Memo Contains: |
|
There are no entries in the database. |