Creating a Simple SQL-Driven Web Site

One of the most amazing things about Active Server Page (ASP) technology is the tight integration with Microsoft's SQL server. In just minutes, you can develop a web site that can add, delete, view, and search records from a database with an ease that would shock an old-time Internet programmer.

In this article, we're going to walk you through the development of a simple web site that contains just four web pages, yet provides the basic functionality for accessing and maintaining a store of data. We'll show you how to configure your SQL database and then how to prepare it for connection to your web site. We'll also show you how to access the database in different ways.

The Tips database

For purposes of this article, we're going to develop a web site that contains a collection of tips. In Figure A, you see the default page of the finished site.

Figure A: The Tips web site accomplishes the requirements in just four web pages.

[ Figure A ]

The requirements for this site are pretty simple: any user should be able to add a tip or search for a tip by author, journal, title, or text of the tip and a date range. In addition, only the site administrator should be able to delete a tip.

In thinking about the design of a site that meets these requirements, we decided to try and complete the project using as few HTML or ASP files as possible. One of the strongest features of the Active Server is the ability to mix server-side scripting and client-side code on the same page, easing maintenance headaches. After considering the options, we decided on implementing a design that had only four web pages: the default page that would act as our search page, an add page, a delete page, and a tip display page.

Our database design

Before you can even begin coding the web pages, you need to design and create the tables in your SQL database, and then set the appropriate permissions. You don't need a complex schema for the tips database, so we created a simple table containing six columns. The column names used were tipID, subDate, author, journal, title, and tip. Using the SQL statement in Listing A, you create a new table in your database.

Listing A: SQL statement used to create TipsTable


CREATE TABLE TipsTable (tipID INT IDENTITY,  
 subDate DATETIME DEFAULT GETDATE(), 
 author 
	VARCHAR(50), journal VARCHAR(3), 
 title VARCHAR(255), tip TEXT)
Next, you'll need to determine who will need to access your database. At the very minimum, you'll need two users. The first is the anonymous web account user. On most NT systems, this user is named IUSR_machine name. Since our server is named Jupiter, the anonymous web account user is IUSR_JUPITER. If you haven't already done so, you'll need to create a login for the IUSR account. After you create this login, make sure it has access to the database where you created your TipsTable.

You'll also need a tips administrator account. If you already have a SQL login for the user who will administer the tips, you only need to make sure they have the proper access to the database containing the TipsTable.

The last step is to set the rights for each user on the table itself. In our case, we modified the rights for the IUSR_JUPITER and TANDREWS users. We gave IUSR_JUPITER SELECT and INSERT rights, while TANDREWS, the tips administrator, has SELECT, INSERT, and DELETE rights. The permissions for each user are shown in Figure B.

Figure B: You must configure the IUSR_JUPITER user so the database can be accessed from the web server.

[ Figure B ]

Creating a data source

Before you can connect your web pages to your database, you need to create a data source. A data source contains information on how to connect to a data provider, in this case, a SQL database. There are a few types of data sources, but we'll use a file data source. The advantages of a file data source for web pages is that the information is stored in an actual file, which can be used by multiple users, and even moved to another web server if needed.

To create the data source, open the Windows NT Control Panel and launch the ODBC icon. Select the DNS tab and click Add to display the Create New Data Source wizard shown in Figure C.

Figure C: Select SQL Server as the source of your data.

[ Figure C ]

Select SQL Server and click Next. Now, you'll be asked to enter the name of the file you wish to create. Name your file Tips.dsn and click Next. In the next dialog box, click Finish to launch the Create A New Data Source To SQL wizard.

In the first dialog box of the wizard, as shown in Figure D, fill in the description, point to your SQL server, and click Next.

Figure D: Enter Tips.dsn as the name of your file data source, and select your SQL server.

[ Figure D ]

In the series of dialog boxes that follow, you'll configure different properties. You'll want to change the default database to point to the one in which you created your TipsTable, as we have in Figure E.

Figure E: Change the name of the default database to the one that has your TipsTable.

[ Figure E ]

Leave the other options at their defaults and click Next to continue through the wizard. When you finish, you should see your new data source, Tips.dsn, displayed on the OBDC Data Source Administrator dialog box, as shown in Figure F. Now, the real fun starts; it's time to connect our database to our web pages.

Figure F: When you complete the configuration, your data source will be displayed.

[ Figure F ]

Inserting a tip into our database

Now that we have our database ready, we need to put some data into it. The first web page you need is one that does just this. In Listing B, you'll see the code for add.asp. This simple ASP page contains all the code you need to collect and insert a tip into the Tips database. Let's take a look at this ASP page one section at a time.

Listing B: add.asp

<%
Function Encode(strToEncode)
 strToEncode = Server.HTMLEncode(strToEncode)
 strToEncode = REPLACE(strToEncode, vbNewLine, "<BR>")
 Encode = strToEncode
End Function


IF Request.Form("Title")="" THEN
 Caption = "We are ready to believe you!"
ELSE
 Set MyConn =  
=> Server.CreateObject("ADODB.Connection")
 MyConn.Open "FILEDSN=C:\Program Files\Common 
	=> Files\ODBC\Data Sources\Tips.dsn"

 Query = "INSERT Tips (author, journal, title,
	 => tip) VALUES ('" & 
=> Encode(Request.Form("Author")) & "','" & 
=> Encode(Request.Form("Journal")) & "','" & 
=> Encode(Request.Form("Title")) & "','" &
=> Encode(Request.Form("Tip")) & "')"

 MyConn.Execute(Query)
 MyConn.Close

 Caption = "Thank you sir, may I have another?"
END IF
%>

<HTML>
<HEAD>
<TITLE>Add a Tip to the Database</TITLE>
</HEAD>
<BODY>
<b><%= Caption %></b>


<FORM method=POST action="add.asp" id=Search 
 name=Search> 
<b>Add a tip:</b>
Author: <input type="text" id=text1 
 name=Author 
 maxlength=40 size=25>
Journal: <input type="text" id=text2 
 name=Journal 
 size=3 maxlength=3><BR>
Title: <input type="text" id=text3 name=Title 
 Size=30><BR>
Tip:<BR><textarea id=textarea2 name=Tip 
 cols=70 rows=10 wrap=physical></textarea>
<P>
<CENTER>
<input type="submit" value="Submit" id=submit1 
 name=submit1>
<input type="reset" value="Reset" id=reset1 
 name=reset1>
</CENTER>
</FORM>
</BODY>
</HTML>
The first section of code contains a function named Encode. This function takes a string and converts the HTML characters to HTML character codes. We'll see why this is important in a moment. This function also replaces new lines with <BR> tags. This is done so the hard returns specified by the author won't be lost when the tip is later displayed.

The next section of the page contains the code to first determine if a form has been submitted, and then to add the tip to the database if it has. We use a simple IF THEN ELSE logic block to test the Request object. If there's no information in the Request.Form("Title") variable, the logic sets the caption and drops to the HTML code. If the variable has a title in it, the script creates an ActiveX Data Object with the following line: 

Set MyConn =  
=> Server.CreateObject("ADODB.Connection")
On the next line, the script opens the MyConn object and associates it with the data source you created.

Note: Your data source will be located in whichever directory you specified when you created it. It may not be in the C:\Program Files\Common Files\ODBC\Data Sources, although this is the most common place for it.

Next, the script assembles a text variable named Query. This variable contains the SQL command we'll use to add a tip to the database. It's important to note that each item submitted from the form is first encoded before it's passed to the database. This is vital, for if someone enters a tip that contains special characters, or the apostrophe (`), the tip won't display correctly; or even worse, it may cause the SQL statement to become invalid.

Finally, the script executes the SQL statement with the Execute() method and closes the connection. The caption is set to show the user that the tip has been added.

The HTML code is identical if the user is hitting the page for the first time, or if they have submitted 10 tips in a row. The only difference is the words between the <b> tags. Now that we've created a way to add a tip, let's see how we can display and remove them.

Displaying and deleting tips

In Listing C, you'll find the code for delete.asp. This ASP page displays the contents of TipsTable and allows you to delete a tip by clicking on the title. Again, let's take a closer look at the code.

Listing C: delete.asp

<%
Set MyConn = 
=> Server.CreateObject("ADODB.Connection")
MyConn.Open "FILEDSN=C:\Program Files\Common 
	=> Files\ODBC\Data Sources\JupiterData.dsn"

IF Request.QueryString ="" THEN
 Caption = "A list of all of the tips 
=> available in the Tips database. Click 
=> on the tip title to delete the tip."
ELSE
 Caption = "The tip was deleted. Click on a 
=> tip title to delete the tip."
 Query = "DELETE Tips WHERE tipID='" &
=> Request.QueryString & "'"
 MyConn.Execute(Query)
END IF

Set RS=MyConn.Execute("SELECT * FROM Tips")
%>

<HTML>
<HEAD>
<TITLE>Tips Database</TITLE>
</HEAD>
<BODY>
<TABLE border=1>
<A Name=results>
<CAPTION><%=Caption%></CAPTION>
<TR>
<TH>Tip ID</TH><TH>Journal</TH> <
	TH>Author</TH><TH>Tip Title</TH>
</TR>

<%
WHILE NOT RS.EOF
 Response.Write(vbNewLine & "<TR><TD>" & 
=> RS("tipID") & "</TD><TD>" & 
	RS("journal") & 
=> "</TD><TD>" & RS("author"))
 Response.Write("</TD><TD><A Href=delete.asp?" 
=> & RS("tipID"))
 Response.Write(">"&RS("title")&"<
	/TD></TR>")
 RS.MoveNext
WEND
%>

</TABLE>
</BODY>
</HTML>

<%
RS.Close
MyConn.Close
%>
The first section of code on this page establishes the database connection. You use the same two lines to create the connection and point it to your data source. The next chunk of code is an IF THEN ELSE block, similar to the one we used in add.asp. Using this conditional logic, the script determines if a query string was attached to the URL that called the page. If there were no query string, the script sets the caption and drops out of the block. If there were a query string, the script sets the headline caption and then creates the following query: 
Query = "DELETE Tips WHERE tipID='" &
=> Request.QueryString & "'"
This line of code builds a SQL statement that will delete a tip from the database if it has a tidID that matches the query string. The next line of code executes the statement. The net effect of this logic is when we call this ASP page using the URL delete.asp?1, it will delete all records from the database with a tipID of 1. Since we created the tipID as an identity column, each tip will have a unique tipID.

The last line of the first section of code uses a SELECT statement to create a recordset containing all of the tips in our database. A recordset is an object that contains the results of our SQL query. You'll notice that we don't close our database connection just yet.

Following the first section of ASP script is a block of HTML code. This code formats the output of our page. Next is another block of script. This block produces the output of our tips in HTML format using a WHILE loop. Stepping through each record in our recordset, we print a formatted line of HTML to the client.

The last two blocks of code, the first an HTML block, the second, a final block of script, serve to close the HTML code and the two objects we created. This should be all we need to delete a tip, right? Not quite.

If you try to access delete.asp, you'll see the page just fine, but what happens if you try to delete a tip? You'll probably see a screen like the one shown in Figure G.

Figure G: Don't forget to disable anonymous access on your delete page, or you'll see this error.

[ Figure G ]

Remember, you didn't give the anonymous web user permission to delete from TipsTable. In order to force the web server to assume the identity of another user, in this case the administrator user, you must restrict anonymous access to the delete.asp page.

In order to do this, launch Internet Service Manager and find the delete.asp page. Right-click on the page node and select properties from the pop-up menu, as shown in Figure H, to launch the delete.asp Properties dialog box.

Figure H: Your delete.asp page won't be in the same location as ours, but you'll be able to find it under your default web site.

[ Figure H ]

Select the File Security tab and, in the Anonymous Access and Authentication Control section, click Edit. In the Authentication Methods dialog box, uncheck the Allow Anonymous Access check box, as shown in Figure I.

Figure I: You must disable anonymous access in order to force the web server to assume another user identity.

[ Figure I ]

Click OK to close the Authentication Methods dialog box, and then click OK to apply the changes to the page. Now, when you try to access the delete.asp page, it will first ask you to authenticate yourself. If you use the name and password of the user you designated the tips administrator, you'll be able to delete records from the database.

Searching the database

Perhaps the best reason to use a database as the source of your web data is the ease with which it can be searched. In Listing D, you'll see the code for default.asp, the main and search page for our tips site.

Listing D: default.asp

<%
StartDate = "01/01/98"
EndDate = DateAdd("d", 1, Date)

Set MyConn = 
=> Server.CreateObject("ADODB.Connection")
MyConn.Open "FILEDSN=C:\Program Files\Common 
=> Files\ODBC\Data Sources\Tips.dsn"

IF Request.Form("Field")="" THEN
 Caption = "A list of all of the tips 
=> available in the ZDJ Tips database:"
 Query = "SELECT * FROM Tips"
ELSE
 Caption = "The following tips meet your 
=> search criteria (<A Href=default.asp>view 
=> all tips</A>):"
 Text = Server.HTMLEncode(Request.Form("Text"))
 Query = "SELECT * FROM Tips WHERE " & 
=> Request.Form("Field") & " LIKE '%" & Text & 
=> "%' AND subDate>'" & Request.Form
	("Start") => & "' AND subdate<'" &
		 Request.Form("End") & => "'"
END IF

Set RS=MyConn.Execute(Query)
%>

<HTML>
<HEAD>
<TITLE>Tips Database</TITLE>
</HEAD>
<BODY>
<FORM method=POST action="default.asp#results"
	 id=Search name=Search> 
<b><A Href=add.asp>Add a tip</A> or 
<A Href=delete.asp>Delete a tip</A></b>
<b>Search for a tip:</b>
Search in: <INPUT Type=Radio Name=Field value=author
	 Checked> Author &nbsp;
 <INPUT Type=Radio Name=Field value=journal> Journal &nbsp;
 <INPUT Type=Radio Name=Field value=title> Title &nbsp;
 <INPUT Type=Radio Name=Field value=tip> Tip 
<BR>
Search for: <input type="Text" id=Text name=Text size=40>
<BR>
Search from: <input type="text" id=Start name= 
	Startvalue="<%= StartDate%>" size=10>
Search to: <input type="text" id=End name=End value=
	"<%= EndDate%>" size=10>
<BR>
<input type="submit" value="Submit" id=submit1 name=submit1>
<input type="reset" value="Reset" id=reset1 name=reset1>
</form>
<A Name=results>
<TABLE border=1>
<CAPTION><%=Caption%></CAPTION>
<TR><TH>Journal</TH><TH>Author</
	TH><TH>Tip Title</TH></TR>

<%
WHILE NOT RS.EOF
 Response.Write(vbNewLine & "<TR><TD>" & 
=> RS("journal") & "</TD><TD>" & RS("author") 
=> & "</TD><TD><A Href=tip.asp?" & 
=> RS("tipID"))
 Response.Write(">" & RS("title") & 
=> "</TD></TR>")
 RS.MoveNext
WEND
%>

</TABLE>
</BODY>
</HTML>

<%
RS.Close
MyConn.Close
%>
This page uses a lot of the same logic as the two pages we previously created. We create and open the ActiveX Data Object using the same two lines. We also use the IF THEN ELSE logical block to determine if the user has performed a search. There are some new things in this page, however.

The following line, used to retrieve the text of the search, has a very important purpose: 


Text = Server.HTMLEncode(Request.Form("Text"))
Not only does it pull the information from the form, but it passes it through the Server.HTMLEncode() function. This function, which we also used in add.asp, transforms the text of a user's query to match the changes the fields of the tips underwent when they were added to the database. It also safely converts characters that could cause the SQL statement to fail.

The other line of code worth discussing is the line used to build the SQL statement. It is as follows: 


Query = "SELECT * FROM Tips WHERE " & 
=> Request.Form("Field") & " LIKE 
=> '%" & Text & "%' AND subDate>'" 
=> & Request.Form("Start") => & "' 
=> AND subdate<'" & 
=> Request.Form("End") & "'"
This line of code uses the SQL SELECT statement in combination with the LIKE statement. The LIKE statement and the % wild card broaden the search to return matches on any fields that contain the text. The statement also contains date ranges to narrow our search by the dates entered.

You'll notice that when we step through the WHILE loop to display the tips, we link the title to an ASP page named tip.asp. This is the last page in our four page web site, and we'll deal with it next.

Displaying the tips

The last page, shown in Listing E, is the simplest of all the pages. It uses the same logic discussed in the other pages. Since we used the Encode() function on the text of the tips before we submitted them to the database, we can simply display the tips and they'll already be formatted in HTML.

Listing E: tip.asp

<%
Set MyConn = 
=> Server.CreateObject("ADODB.Connection")
MyConn.Open "FILEDSN=C:\Program Files\Common 
=> Files\ODBC\Data Sources\JupiterData.dsn"
Query = "SELECT * FROM Tips WHERE tipID='" 
	& => Request.QueryString & "'"
Set RS=MyConn.Execute(Query)
%>

<HTML>
<HEAD>
<TITLE>Tip</TITLE>
</HEAD>
<BODY>
<TABLE border=1>
<A Name=results>
<TR><TH>Tip ID</TH><TH>Date
	 Submitted</TH><TH>Journal</TH>
<TH>Author</TH></TR>

<%
Response.Write("<BR><TR><TD>" & 
	RS("tipID") & 
=> "</TD><TD>")
Response.Write(RS("subDate") & "</TD><TD>" & 
=> RS("journal"))
Response.Write("</TD><TD>" & RS("author") & 
=> "</TD></TR>")
%>

</TABLE>
<P>

<% 
Response.Write(RS("title")&"<P>"&RS("tip"))
%>

<P>
<BR>
<CENTER><A Href=default.asp>Return</
	A> to the tip main page.</CENTER>
</BODY>
</HTML>

<%
RS.Close
MyConn.Close
%>

Conclusion

Creating database-driven web sites is easier than it's ever been before. ASP technology allows you to integrate static and dynamic code on the same page, allowing you to produce a functional web site using less space and less to maintain.

Copyright © 1998, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.