- Introduction
- Sample Database-Driven Web Application: Web Site Guest Book
- Outline for the Remaining Tutorials
Sample Database-Driven Web Application: Web Site Guest Book
To understand how HTML forms, databases, Active Server Pages, and SQL fit together, let's analyze the classic example of a database-driven Web page: a Web site guest book. You've probably signed a guest book on a Web site at some point while Web surfing. The typical Web site guest book usually asks for your name, email address, age, gender, and a comment (see Figure 1 for an example).
){kind=link}
Figure 1 Sample HTML guest book form.
When you fill out the form and click the submit button—usually labeled SIGN MY GUEST BOOK or some equivalent—you're typically brought to a page that thanks you for filling out the guest book, or one that shows your guest book entry along with those of all the other users who signed the guest book (see Figure 2).
){kind=link}
NOTE
You can experiment with the guest book above by visiting http://www.ProfessorF.com/informIT/guestbook.html)
Figure 2 Contents of the guest book, displayed after clicking the SIGN MY GUEST BOOK button.
The big question is what happens between the time you click the SIGN MY GUEST BOOK button and the time the guest book's content is displayed on the screen. If you know the answer to that question, you know how databases and SQL interact with HTML forms and Active Server Pages. If you don't know the answer, please read on.
Behind the Scenes: How HTML Forms, Active Server Pages Scripts, Structured Query Language (SQL), and Databases Interact
When a user fills out a form and clicks the submit button, the information that the user entered (for example, Barney Rubble, barney@roll.com, 34, male, I Love Betty, and so on) is sent to the Web server (such as http://www.ProfessorF.com) along with the name of an Active Server Pages file that the server should execute to process the user's data (for example, insert.asp). The actual name of the Active Server Pages file is hidden inside the HTML source file for the guest book. If your browser is Microsoft Explorer, you can find this name by going to your browser's View menu and selecting the Source menu item. A screen will then pop up. Scroll through this screen looking for a <FORM> tag. One of the parameters for the <FORM> tag is labeled ACTION, and its value is the name of the Active Server Pages file that the Web server should execute (in Listing 1, that's insert.asp):
Listing 1 Portion of the Source Code for the Guest Book Form (guestbook.html)
... <FORM METHOD=POST ACTION="insert.asp"> ... </FORM> ...
The Active Server Pages (ASP) file is simply a text file, just like the HTML file used to bring up the Guest Book form. One noticeable difference is that, instead of having a filename ending in .html, such as guestbook.html, an ASP file ends in .asp, such as insert.asp. You create an ASP file using your favorite text editor—we like using Notepad—and upload it to the Web server the same way you would create an HTML file and upload that to a server—for example, using FTP. The main difference is that an Active Server Pages file can contain both HTML and Visual Basic code. The latter is a subset of the full-blown Visual Basic language, and is known as Visual Basic Script, or VBScript for short. Because an ASP file can contain both HTML and programming code, it's often referred to simply as a script. We'll use the terms ASP file and ASP script interchangeably. Because your Web server and not the user's Web browser executes the script, ASP scripts are also known as server-side scripts. In contrast, the Web browser typically executes JavaScript, so JavaScript is often referred to as a client-side script.
Back to our example. The Web server passes the user's information to the ASP script (insert.asp, also known as the insert script), which first opens a database (see Listing 2, step 1), and then places the user's data into the database (step 2). To place the user's information into the database, the ASP code must communicate with the database in a special language known as Structured Query Language (SQL), as shown in the bold text in step 2. After the ASP script inserts the user's information into the database, the final step (step 3) is to call a retrieve script, which displays the database information; in this example, that's the guest book.
Listing 2 Portion of the ASP Insert Script (insert.asp)
...
'
'STEP 1. Open the database
'
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
";Driver={Microsoft Access Driver (*.mdb)};")
...
'
'STEP 2. Use SQL to communicate the user's information
' to the database.
'
conn.execute("insert into GuestBook " & _
"(name, email, hideEmail, age, gender, comment) " & _
"Values (" & _
"'" & v_name & "'," & _
"'" & v_email & "'," & _
" " & v_hideEmail & " ," & _
" " & v_age & " ," & _
"'" & v_gender & "'," & _
"'" & v_comment & "')")
conn.close
set conn=nothing
'
'STEP 3. Call another ASP script (retrieve.asp) to display guestbook
'
response.redirect("retrieve.asp")
...
TIP
We mentioned that an ASP script could contain both VBScript and HTML code. Note, however, that the insert script in Listing 2 only has VBScript—and it's also possible to have an ASP file containing just HTML.
The ASP retrieve script, retrieve.asp, combines VBScript and HTML. The script starts off by opening the database (see Listing 3, step 1) and then sends an SQL command to retrieve the entire contents of the guest book (step 2). The script then intersperses VBScript and HTML to format the contents of the guest book. You can tell the difference between the HTML and VBScript because the VBScript is contained within <% and %> symbols.
Listing 3 Portion of the ASP Retrieve Script (retrieve.asp)
...
'
' Step 1. Open up the database
'
conn.open("DBQ=" & server.mappath("visualtutorial.mdb") & _
";Driver={Microsoft Access Driver (*.mdb)};")
...
<FONT style="font-size:18pt">
<FONT COLOR=MAROON><B>Cool People Who Signed My Guest Book!</B></FONT>
</FONT>
...
'
' Step 2. Use SQL to retrieve all the entries from
' the database's GuestBook table
'
<% set rs=conn.execute("select * from GuestBook") %>
'
' Display the Guest Book using HTML to format the information
'
<TABLE BORDERCOLOR=MAROON CELLSPACING=0 BORDER=1><TR><TD>
<TABLE CELLSPACING=0>
<TR BGCOLOR=MAROON>
<TD ALIGN=CENTER><B><FONT COLOR=TAN>Date</FONT></B></TD>
<TD ALIGN=CENTER><B><FONT COLOR=TAN>Email</FONT></B></TD>
<TD ALIGN=CENTER><B><FONT COLOR=TAN>Name</FONT></B></TD>
<TD ALIGN=CENTER><B><FONT COLOR=TAN>Age</FONT></B></TD>
<TD ALIGN=CENTER><B><FONT COLOR=TAN>Gender</FONT></B></TD>
<TD ALIGN=CENTER><B><FONT COLOR=TAN>Comment</FONT></B></TD>
</TR>
<%
dogrey=false
do while not rs.eof
%>
<% if not(dogrey) then %>
<TR>
<% else %>
<TR bgcolor=lightgrey>
<% end if %>
<% dogrey=not(dogrey) %>
<TD>
<%=month(rs("dateposted"))%> /
<%=day(rs("dateposted"))%> /
<%=year(rs("dateposted"))%>
</TD>
<TD><%=rs("email")%> </TD>
<TD><%=rs("name")%> </TD>
<TD><%=rs("age")%> </TD>
<TD><%=rs("gender")%> </TD>
<TD><%=rs("comment")%> </TD>
</TR>
<%
rs.movenext
loop
rs.close
set rs=nothing
%>
</TABLE></TD></TR>
</TABLE>
...
In our example, after the retrieve.asp script executes, the user sees the contents of the guest book (refer to Figure 2).
How the Pieces Fit Together Graphically
The diagram in Figure 3 summarizes the preceding discussion. The HTML form sends the user's information to an ASP insert script, which uses SQL to insert the information into a database. An ASP retrieve script uses SQL to read data from the database, which it formats using HTML, and sends that HTML to the user's browser. If you try to view the source for this HTML, you won't see any VBScript at all, since all code is executed on the Web server and the code only sends HTML to the browser.
){kind=link}
Figure 3 Graphical depiction of how the HTML form, ASP insert (store) script, database, and ASP retrieve script fit together.
To summarize, when you create a database-driven Web application, you always develop four pieces:
A form in HTML to get information from a user (such as guestbook.html)
An insert script in Active Server Pages to put the user's information into the database (such as insert.asp)
A database to store the information (such as visualtutorial.mdb)
A retrieve script in Active Server Pages to get information out of the database and display it on the user's browser (such as retrieve.asp)
You upload all these pieces to your Web server, just as you would your HTML files. Our example put these pieces in separate files, but it's common to combine the pieces; for example, the HTML form can be put in the same file as the insert script.