When you hear about using the Web for business, it usually involves database access. Traditional client/server systems that businesses use today are designed and built around databases. When those existing systems are moved to, or new ones are created on the Web, they are still databases. The Web is becoming the ultimate extension of client/server technology. The methods may have changed, but the purpose is the same. You want to maintain your data and application in one place (the server) and allow access from many other locations (the clients). In many ways, this has regressed to the days of the dumb terminal connected to a mainframe, but with better graphics.
Once you know you need a Web-based interface to your data, you need only figure out how to build it. This can be done in an almost infinite number of ways, but here you learn only two: ASP and VB. You cover the topics taking you from the planning stage to the implementation of a complete sample site.
To illustrate the design, planning, and implementation of a data-driven Web site, this chapter follows an example site through each of those stages. In Chapter 37, "Creating Active Server Pages," the primary example used is a technical training company that wants to put its catalog on the Web. This Web site, although small, is also a good example for using ASP with a back-end database, and thus, this example is continued in this chapter.
See "When to Build Dynamic Sites," Chapter 37
The first step in implementing this site is to determine its purpose. Your client, the owner of the technical training company, wants to set up an Internet site to serve two purposes. The primary purpose is to inform potential students about the company's course offerings. The client wants to use the Internet to reach a broader base of the public and wants to have the capability to easily update the courses and schedules when they change, some- thing they couldn't do with the traditional brochure. Therefore, she wants the site to be an Internet equivalent of the school's current training brochure. This brochure has lists of available courses, which are grouped into categories, as well as information about when and where the courses are being offered.
The secondary purpose for the site is to establish interactive student registration, which will reduce administrative costs and make the registration process more efficient. The client wants the site set up so that a person can register directly online for any course in the brochure, instead of filling out paper forms that have to be manually entered into the company's computer system.
Further discussion with the client identified all the various tables and fields needed on the site, thus enabling you to build a data model (see Figure 39.1). A data model developed for a Web-based application is no different from one developed for use in a traditional system--all the same rules apply.
FIG. 39.1
Sample Data Model for Training Catalog showing all of the tables and the relationships
between them.
The actual database was constructed from this model, in this case, by using Microsoft Access. The size of data involved, as well as the client's budget, make Access the appropriate choice for this system, although SQL Server, Oracle, or other databases just as easily could have been used. The tables are fairly simple and may need to be expanded at a later date, but they are sufficient to make the first version of the site.
There is a Course table to hold information about each course, several look-up tables (Category, Instructor, and Location), and a Schedule table that links a course with a date, and an instructor with a location. There also is a Registration table, which could have been linked to an entry in the Schedule table, but instead is being made generic enough to handle registration for other types of events the training company may offer, such as seminars, product launches, and so forth.
It is essential always to start with a data model and to make sure it as complete as possible before you start working on any site. Large changes in your data model, in any type of application, can require rewriting much of your code.
In addition to the preceding main points, the client has some other requirements:
After looking at these requirements, and at the existing catalog, it is established that the following set of pages needs to be built:
Only four .asp files need to be built, each of which will be built independently, starting with the course listing. The four files will be tied together later.
To create even the simplest page of the training site, access is needed to the database. This is accomplished through ActiveX Data Objects, or ADO. ADO is the latest in a series of data access layers from Microsoft, which include DAO (Data Access Objects) and RDO (Remote Data Objects). A full discussion of this technology appears in Chapter 33, "Database Access with ActiveX Data Objects (ADO)," but the basics are covered here for purposes of building the Internet site.
ADO is designed to simplify programming, relative to the other methods of database objects. It still shares some objects with the previous models, most notably the Recordset object, so if you are familiar with either DAO or RDO, you shouldn't have any trouble working with ADO. In general, most tasks can be accomplished with less code than is required with DAO and, because of ADO's smaller memory requirements, should perform better as well.
ADO works with the Open Database Connectivity (ODBC) standards. When you want to connect to a database, you do so through its ODBC Data Source Name (DSN). All access goes through ODBC, unlike DAO, which goes directly to Microsoft Jet and other ISAM (file-based) databases. Because of this, you must set up an ODBC DSN pointing at your database. The DSN needs to be a system DSN (which is one of the tabs in the ODBC control panel), and needs to be on the server, not the client, because all processing and data access will be occurring before the page is returned on the Web server.
See "Setting Up an ODBC Data Source," Chapter 32
For the present example, a DSN named "Training" will be created. The first step is to open the ODBC Data Source Administrator by choosing Start, Settings, Control Panel, and then the ODBC 32 icon. To create a connection, you have to select the Administrator's "System DSN" tab (see Figure 39.2) and then click the Add button. This brings up the Create New Data Source dialog box (see Figure 39.3), where you can pick the driver used for this connection. In this example, the driver is the Microsoft Access driver. Click Finish after you have selected the proper driver. The Setup dialog box that appears (see Figure 39.4) enables you to configure your DSN, set its name (to "Training"), and then press the Select button to find your database.
FIG. 39.2
The ODBC Data Source Administrator.
FIG. 39.3
Picking a database driver.
FIG. 39.4
Configuring the Microsoft Access ODBC driver.
NOTE: You must have the database on a drive that is accessible to the Web server, and where the anonymous Web user will have read/write permissions to it. The anonymous user also must have "Add" rights to the directory or volume containing the database because Jet creates a .ldb file when the database is opened.
After you have located the database, click the OK button. You now have a DSN that can be used in the rest of the example. To confirm that you have set up your DSN correctly, and to illustrate the basic syntax of using ADO, you can create a quick test page, which is shown in Listing 39.1.
<%@ LANGUAGE="VBSCRIPT" %> <HTML> <HEAD> <TITLE>Sample</TITLE> </HEAD> <BODY> <% Set conn = Server.CreateObject("ADODB.Connection") Conn.Open "DSN=Training" SQL = "SELECT Course.* FROM Course" Set RSCourses = Conn.Execute(SQL) RSCourses.MoveFirst Do While Not RSCourses.EOF %> <P><%=RSCourses("Title")%></P> <% RSCourses.MoveNext Loop RSCourses.Close %> </BODY> </HTML>
The code in Listing 39.1 creates an instance of the ADO connection object, opens a connection to the "Training" DSN, and then executes a SQL statement. The Execute method returns the results of the SQL statement as a Recordset. Because this is an object, you have to use the Set command to assign it to the RSCourses variable. After you have this Recordset, you can work with it in the same manner as the DAO or RDO Recordset object. The rest of the page just does a simple Do While...Loop, using the Recordset.EOF property, and the MoveNext method to cycle through all the records returned from the SQL statement.
This example produces a plain page containing all the course titles from the database, as shown in Figure 39.5. If this page works, then your DSN, database, ADO, and ASP are probably all set up correctly. If you get any errors at this point, you should attempt to fix them before moving on to any of the other examples. A common error that you may get is Data Source Name not found, meaning the DSN you specified in line 10 of the example, "Training," doesn't exist. Check to see (in the ODBC 32 control panel) that it does exist and that it is a System DSN; then, if it does exist, check the spelling of its name and the name specified in your code.
FIG. 39.5
Output of ADO example.
The nature of databases is that they contain lists of information. Whether that information is course titles, bank transactions, or items in a company's inventory, they are all lists. Not all of the information is displayed in the same format; when you look at a printed catalog; for instance, it usually doesn't look like a list. You don't want your client's pages to look like lists either; you want them to look like the catalog. This is possible when working with ASP--you can use all the functions of HTML to format your lists any way you want.
For the client's training catalog, you need a list of the available courses. For each course, the course number and description is needed, which you can get by using a simple SQL statement such as the one in Listing 39.1. To properly format your client's page, a standard HTML page will first be created, and will be designed to look exactly as you want one of the list items to look. This enables you to do all your tweaking and formatting work by using FrontPage or another HTML editing tool, without worrying about the scripting code or the data. After you have the static page completed (see Figure 39.6), you can take its HTML source and use it as a template for your real page (see Listing 39.2). This listing uses [ and ] to mark places where fields will be substituted from the database, but you can use real data in those places to give you a better idea how the finished page will look.
FIG. 39.6
Static HTML Template showing the position and formatting of all the important
information.
NOTE: The template page covers two courses, just to help visualize the end result. When you make the actual Active Server Page your loop will only have to include one.
<HTML> <HEAD> <TITLE>Courses</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <P> <FONT COLOR="#FF0000" SIZE="4" FACE="Verdana"> <STRONG>[Number]</STRONG> </FONT> <FONT COLOR="#000000" SIZE="4" FACE="Verdana"> <STRONG>[Course Title]</STRONG> </FONT> </P> <P>[Description]</P> <P> </P> <P> <FONT COLOR="#FF0000" SIZE="4" FACE="Verdana"> <STRONG>[Number]</STRONG> </FONT> <FONT COLOR="#000000" SIZE="4" FACE="Verdana"> <STRONG>[Course Title]</STRONG> </FONT> </P> <P>[Description]</P> <P> </P> </BODY> </HTML>
Now, save that HTML into a new file, course.asp, remove the second course example, and begin adding the scripting code. The first thing you have to do is create an instance of the ADODB.Connection object by using the line
Set conn = Server.CreateObject("ADODB.Connection")
This connection object represents your ODBC link to a database. Its open method is used to connect directly to your data source, specifying the ODBC connection string as a parameter. The Connection object can also take a series of parameters, which can be set to the appropriate values, as opposed to including them all as part of one connection string. The two code samples in Listings 39.3 and 39.4 both accomplish the exact same effect, opening the database pointed to by the DSN SampleDB, with the Userid of jdoe and the password jane.
<% Set conn = Server.CreateObject("ADODB.Connection") Conn.Open "DSN=SampleDB;UID=jdoe;PWD=jane" %>
<% Set conn = Server.CreateObject("ADODB.Connection") Conn.Open "SampleDB","jdoe","jane" %>
Maintaining Security in ASP
In most of these examples, a password is not being used, but in a production system, you probably will be using passwords. If you are using a password system, it is important to realize that the password and userid are completely exposed in the source of your Active Server Page. Under normal circumstances, this source should never be seen. However, if you have installed FrontPage Extensions (part of Visual Interdev and FrontPage 97) onto your server and the anonymous user has rights over the entire wwwroot directory, then anyone in the world can connect to your site with FrontPage and make changes. This security risk, and others, means that it is very important to make your pages secure. Microsoft recommends that you give users accounts (including the anonymous one) that have Execute access only to .asp files, not Read access. The users do not require Read access, and this gives you an additional level of protection.In a previous version of ASP, there was a bug that created exactly the kind of exposure you don't want. If you appended a "." to the end of a http request for an ASP (for example, "http://www.online-can.com/default.asp."), the source was returned--unexecuted. It suddenly was possible (only for a few days, a patch was made available within 24 hours) to view the private passwords and other information contained in a company's server-side code. Even this bug, which caught almost everyone, wouldn't have exposed your pages if they didn't have read access turned on.
It doesn't matter which syntax you use. If you already are familiar with ODBC Connection strings from previous experience, then you may want to continue using that style. If, on the other hand, you haven't worked with ODBC before, setting the individual properties may be easier to use and to understand later, and thus may be a better choice.
After you have the Connection object and you have opened the database, you can create and execute your SQL statement against it. The information required for this page is very simple--the course number, title, and description (all the things in the HTML Template shown in Figure 39.6)--so the SQL statement is as follows:
SQL = "Select Course.Number, Course.Title, " & _ "Course.Description From Course Order by Course.Title"
You would normally store the statement into a variable and then pass that variable to the Execute method, but it isn't necessary. (For an introduction to SQL, see "SQL Primer," which is on the companion CD-ROM.)
At this point, your code should look like Listing 39.5. Even though you are not finished, you can run the file (view it in a browser) as is to see if any errors occur. Although it may seem like there can't be any problems yet, you already have performed several tasks during which errors can occur--you haven't yet displayed any data, but you have created the ADODB object, opened a database connection, and executed a SQL statement.
<%@ LANGUAGE="VBSCRIPT" %> <HTML> <HEAD> <TITLE>Courses</TITLE> </HEAD> <% Set conn = Server.CreateObject("ADODB.Connection") Conn.Open "DSN=Training" SQL = "Select Course.Number, Course.Title, " & _ "Course.Description From Course Order by Course.Title" Set RSCourse = Conn.Execute(SQL) %> <BODY BGCOLOR="#FFFFFF"> <P> <FONT COLOR="#FF0000" SIZE="4" FACE="Verdana"> <STRONG>[Number]</STRONG> </FONT> <FONT COLOR="#000000" SIZE="4" FACE="Verdana"> <STRONG>[Course Title]</STRONG> </FONT> </P> <P>[Description]</P> <P> </P> </BODY> </HTML>
After you have executed the SQL statement and have stored the result into a variable using the Set command, you then have your data. Now you just have to set up a loop around the list item section of your sample HTML and substitute fields from the database, where needed. The loop is easy; just place a Do While Not RSCourses.EOF before the first part of the list item, right after the <BODY> tag ("body tag" has kind of a scary sound to it when you say it out loud, doesn't it?). The Loop statement has to go after the list item, but before the </BODY> tag, so the page doesn't end until the loop completes. The most common mistake is to forget the MoveNext command or put it outside the loop. If you seem to have an infinite loop, containing the same course information over and over again, make sure that the MoveNext line is there.
Proper programming practice calls for closing the Recordset once you are done with it, even though both VB and ASP clean up objects for you, so that will be placed as the last line of code. At this point, the page is complete and it should do what you want--display a list of courses. The complete code for the page appears in Listing 39.6, and its output is shown in Figure 39.7.
FIG. 39.7
Output of Course.asp.
<%@ LANGUAGE="VBSCRIPT" %> <HTML> <HEAD> <TITLE>Courses</TITLE> </HEAD> <% Set conn = Server.CreateObject("ADODB.Connection") Conn.Open "DSN=Training" SQL = "Select Course.Number, Course.Title, " & _ "Course.Description From Course Order by Course.Title" Set RSCourses = Conn.Execute(SQL) %> <body bgcolor="#FFFFFF"> <% Do While Not RSCourses.EOF %> <P> <FONT COLOR="#FF0000" SIZE="4" FACE="Verdana"> <STRONG><%=RSCourses("Number")%></STRONG> </FONT> <FONT COLOR="#000000" SIZE="4" FACE="Verdana"> <STRONG><%=RSCourses("Title")%></STRONG> </FONT> </P> <P><%=RSCourses("Description")%></P> <P> </P> <% RSCourses.MoveNext Loop RSCourses.Close %> </BODY> </HTML>
Upon looking at the page as it stands, you realize that you forgot one thing, the categories. In the data model, each course is assigned to a category, allowing them to be grouped by topic. These groups should appear on the list.
This means some changes need to be made to properly group the courses, and display some sort of appropriate heading at the start of each new category grouping. First to your SQL, you need to get the category for each course. Your query can be modified to select Category.Name (refer to the original data model, shown in Figure 39.1, if you are unsure how the tables link together) by joining Category and Course on the Course.Category_Id field. When you change the Order By clause, all the courses under one category will appear together in the results. You could easily add a line to your page that displays the category name as part of the list item, but then the same name would be displayed many times for each category. It is better to show the category name only when it changes. Returning to your HTML editor, add in a category heading to create the look shown in Figure 39.8. You now could start again, as before, with the HTML produced by your editor, but you don't need to. You can just copy the appropriate lines from this new HTML page, (marked with asterisks in Listing 39.7) and place them into your .asp file.
FIG. 39.8
Course Listing template, with categories.
<HTML> <HEAD> <TITLE>Courses</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> *<TABLE WIDTH="100%" BGCOLOR="#000000"> * <TR> * <TD> * <FONT COLOR="#FFFFFF" SIZE="5" FACE="Verdana"> * <STRONG>[Category Name]</STRONG> * </FONT> * </TD> * </TR> *</TABLE> <P> <FONT COLOR="#FF0000" SIZE="4" FACE="Verdana"> <STRONG>[Number]</strong> </FONT> <font color="#000000" size="4" face="Verdana"> <strong>[Course Title]</STRONG> </FONT> </P> <P>[Description]</P> <P> </P> <P> <FONT COLOR="#FF0000" SIZE="4" FACE="Verdana"> <STRONG>[Number]</STRONG> </FONT> <FONT COLOR="#000000" SIZE="4" FACE="Verdana"> <STRONG>[Course Title]</STRONG> </FONT> </P> <P>[Description]</P> <P> </P> </BODY> </HTML>
NOTE: This template uses a one-row table to show the Category name, which makes the template dependent on the user having a browser that supports tables, but that isn't all that is browser-dependent. The table's background color is specified as black, but only one browser, Internet Explorer, supports assigning a background to a table. To make this page browser independent you would have to either remove the reversed type altogether, or use conditional ASP code to only display it reversed in Internet Explorer.
Okay, you have the data, you know what you want that data to look like, now how do you determine when the category name changes? If you use a variable to store the category of the last list item, you can compare it against the value of each successive item, putting in the category header anytime the values are different. The modified .asp file is shown in Listing 39.8, the results of which appear in Figure 39.9.
<%@ LANGUAGE="VBSCRIPT" %> <HTML> <HEAD> <TITLE>Courses</TITLE> </HEAD> <% Set conn = Server.CreateObject("ADODB.Connection") Conn.Open "DSN=Training" SQL = "Select Course.Number, Course.Title, " SQL = SQL & "Course.Description, Category.Name as CourseGroup " SQL = SQL & "From Course,Category " SQL = SQL & "Where Course.Category_ID = Category.ID " SQL = SQL & "Order by Category.Name,Course.Title" Set RSCourses = Conn.Execute(SQL) CourseGroup = "" %> <BODY BGCOLOR="#FFFFFF"> <% Do While Not RSCourses.EOF %> <% If CourseGroup <> RSCourses("CourseGroup") Then CourseGroup = RSCourses("CourseGroup") %> <TABLE WIDTH="100%" BGCOLOR="#000000"> <TR> <TD> <FONT COLOR="#FFFFFF" SIZE="5" FACE="Verdana"> <STRONG><%=RSCourses("CourseGroup")%></STRONG> </FONT> </TD> </TR> </TABLE> <%End If%> <P> <FONT COLOR="#FF0000" SIZE="4" FACE="Verdana"> <STRONG><%=RSCourses("Number")%></STRONG> </FONT> <FONT COLOR="#000000" SIZE="4" FACE="Verdana"> <STRONG><%=RSCourses("Title")%></STRONG> </FONT> </P> <P><%=RSCourses("Description")%></P> <P> </P> <% RSCourses.MoveNext Loop RSCourses.Close %> </BODY> </HTML>
Your client is impressed; you have most of the information from her catalog up on the Web by using just one page. The real goal is to allow visitors to the site to see an up-to-date list of when the courses are offered, so your next task is to build the schedule page.
FIG. 39.9
Results of the modified page.
The client wants to show the course number, title, and cost, along with the dates when the course is offered (there could be more than one set of dates), and still group the courses by category. The client also wants to keep the page as short as possible, providing an "at a glance" view. To handle all this, you decide to use tables and create your HTML template page accordingly (see Figure 39.10). One of your client's requirements is that the site be completely browser-independent. Because some browsers don't support tables (hard to believe, but true), a second template is created that doesn't use tables (see Figure 39.11). The source code for both versions of the page is provided in Listings 39.9 and 39.10, respectively.
FIG. 39.10
Template for the Schedule page using HTML tables.
FIG. 39.11
Use indenting and other formatting to produce a similar Schedule page, without
using tables. This allows for more browser independence.
<HTML> <HEAD> <TITLE>Course Schedule</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <TABLE BORDER="0" WIDTH="100%" BGCOLOR="#000000"> <TR> <TD> <FONT COLOR="#FFFFFF" SIZE="3" FACE="Verdana"> <STRONG>[Category]</STRONG> </FONT> <TABLE BORDER="0" WIDTH="100%" BGCOLOR="#FFFFFF"> <TR> <TD VALIGN="TOP" WIDTH="55%"> <FONT COLOR="#FF0000" SIZE="2" FACE="Verdana"> <STRONG>[Number]</STRONG> </FONT> <FONT SIZE="2" FACE="Verdana"> <STRONG>[Title]</STRONG> </FONT> </TD> <TD VALIGN="TOP" WIDTH="35%"> <FONT SIZE="2" FACE="Verdana"> [Date Range]<BR> </FONT> </TD> <TD VALIGN="TOP" WIDTH="10%"> <FONT SIZE="2" FACE="Verdana"> [Cost] </FONT> </TD> </TR> <TR> <TD VALIGN="TOP" WIDTH="55%"> <FONT COLOR="#FF0000" SIZE="2" FACE="Verdana"> <STRONG>[Number]</STRONG> </FONT> <FONT SIZE="2" FACE="Verdana"> <STRONG>[Title]</STRONG> </FONT> </TD> <TD VALIGN="TOP" WIDTH="35%"> <FONT SIZE="2" FACE="Verdana"> [Date Range]<BR> [Date Range]<BR> </FONT> </TD> <TD VALIGN="TOP" WIDTH="10%"> <FONT SIZE="2" FACE="Verdana"> [Cost] </FONT> </TD> </TR> </TABLE> </TD> </TR> </TABLE> </BODY> </HTML>
<HTML> <HEAD> <TITLE>Course Schedule</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <H1>[Category]</H1> <H3><FONT COLOR="#FF0000">[Number]</FONT> [Title] ([Cost])</h3> <BLOCKQUOTE> <H4>[Date Range]<BR> [Date Range]</H4> </BLOCKQUOTE> <H3><FONT COLOR="#FF0000">[Number]</FONT> [Title] ([Cost])</H3> <BLOCKQUOTE> <H4>[Date Range]<br> [Date Range]</H4> </BLOCKQUOTE> <H1>[Category]</H1> <H3><FONT COLOR="#FF0000">[Number]</FONT> [Title] ([Cost])</H3> <BLOCKQUOTE> <H4>[Date Range]<BR> [Date Range]</H4> </BLOCKQUOTE> </BODY> </HTML>
With your template finished, you can begin to create the ASP version in a new file named Schedule.asp. As in the course listing page, the first thing to do is open your database and execute a SQL query against it. The code to do that is almost exactly the same as in the previous set of examples, only the SQL itself has changed. For this page you need to pull the records out of the Schedule table and join them with the Course table, which is itself joined with the Category table. You should retrieve only the courses that haven't taken place already. Not as simple as the last page (the list of courses), but this page doesn't have to be too difficult. If you use the Access query builder, you can quickly drag-and-drop field names to create joins between the appropriate tables, then copy the SQL back into your page. Regardless of how you create it, your query should look like one of the two versions shown in Listing 39.11, either version of which will work fine.
****Query 1 - Using the "Join" Syntax**** SELECT Category.Name, Course.Number, Course.Title, IIf([Cost]>0,Format([Cost],"Currency"),"-") AS CourseCost, Format([Start_Date],"mmm d"", ""yy") & IIf([End_Date]<>[Start_Date]," to " & Format([End_Date],"mmm d"", ""yy"),"") AS DateRange FROM (((Schedule INNER JOIN Course ON Schedule.Course_Id = Course.Id) INNER JOIN Instructor ON Schedule.Instructor = Instructor.Id) INNER JOIN Location ON Schedule.Location = Location.Id) INNER JOIN Category ON Course.Category = Category.Id WHERE (((Schedule.Start_Date)>Now())) ORDER BY Category.Name, Course.Number; ****Query 2 - Using Where Clauses to perform Joins **** SELECT Category.Name, Course.Number, Course.Title, IIf([Cost]>0,Format([Cost],"Currency"),"-") AS CourseCost, Format([Start_Date],"mmm d"", ""yy") & IIf([End_Date]<>[Start_Date], " to " & Format([End_Date],"mmm d"", ""yy"),"") AS DateRange FROM Schedule, Course, Instructor, Location, Category WHERE (Schedule.Course_ID = Course.ID) AND (Schedule.Instructor = Instructor.Id) AND (Schedule.Location = Location.Id) AND (Course.Category = Category.Id) AND (Schedule.Start_Date > Now()) ORDER BY Category.Name, Course.Number
The opening code is done, you have your data, and now you have to code the loop that will take you through all the records that were returned. There are two levels of grouping in this page--all the courses for a category, and all the dates for a course--which makes it more complex. The grouping is handled in a similar manner as in the course listing (refer to the earlier section, "The Course Listing"), using two variables to detect when either the course number or the category changes. The addition of tables also increases the difficulty of this page; there are a lot more beginning and ending tags (Tables, Rows, and Columns) to worry about. The finished code, shown in Listing 39.12, should be studied thoroughly. Both the scripting code and the HTML have been indented to make them more readable, which is a good habit to get into because it enables you to quickly match up opening and closing tags, If...End If blocks, and other constructs as you create the file.
<%@ LANGUAGE="VBSCRIPT" %><HTML> <HEAD> <TITLE>Course Schedule</TITLE> <% Set conn = Server.CreateObject("ADODB.Connection") Conn.Open "DSN=Training" SQL = "SELECT Category.Name, Course.Number, Course.Title, " SQL = SQL & "IIf([Cost]>0,Format([Cost],'Currency'),'-') " SQL = SQL & "AS CourseCost, " SQL = SQL & "Format([Start_Date],'mmm d, yy') & " SQL = SQL & "IIf([End_Date]<>[Start_Date], " SQL = SQL & "` to ` & Format([End_Date],'mmm d, yy'),'') " SQL = SQL & "AS DateRange " SQL = SQL & "FROM Schedule, Course, Instructor, " SQL = SQL & "Location, Category " SQL = SQL & "WHERE (Schedule.Course_ID = Course.ID) " SQL = SQL & "AND (Schedule.Instructor = Instructor.Id) " SQL = SQL & "AND (Schedule.Location = Location.Id) " SQL = SQL & "AND (Course.Category = Category.Id) " SQL = SQL & "AND (Schedule.Start_Date > Now()) " SQL = SQL & "ORDER BY Category.Name, Course.Number" Set RSSchedule = Conn.Execute(SQL) GroupName = RSSchedule("Name") CourseNumber = RSSchedule("Number") %> </HEAD> <BODY BGCOLOR="#FFFFFF"> <TABLE BORDER="0" WIDTH="100%" BGCOLOR="#000000"> <TR> <TD> <FONT COLOR="#FFFFFF" SIZE="3" FACE="Verdana"> <STRONG><%=RSSchedule("Name")%></STRONG> </FONT> <TABLE BORDER="0" WIDTH="100%" BGCOLOR="#FFFFFF"> <TR> <TD VALIGN="TOP" WIDTH="55%"> <FONT COLOR="#FF0000" SIZE="2" FACE="Verdana"> <STRONG><%=RSSchedule("Number")%></STRONG> </FONT> <FONT SIZE="2" FACE="Verdana"> <STRONG><%=RSSchedule("Title")%></STRONG> </FONT> </TD> <TD VALIGN="TOP"> <% Do While Not RSSchedule.EOF If GroupName <> RSSchedule("Name") then GroupName = RSSchedule("Name") CourseNumber = RSSchedule("Number") %> </TD> <TD VALIGN="TOP" WIDTH="10%"> <FONT SIZE="2" FACE="Verdana"> <%=Cost%> </FONT> </TD> </TR> </TABLE> </TD> </TR> <TR> <TD> <FONT COLOR="#FFFFFF" SIZE="3" FACE="Verdana"> <STRONG><%=RSSchedule("Name")%></STRONG> </FONT> <TABLE BORDER="0" WIDTH="100%" BGCOLOR="#FFFFFF"> <TR> <TD VALIGN="TOP" WIDTH="55%"> <FONT COLOR="#FF0000" SIZE="2" FACE="Verdana"> <STRONG><%=RSSchedule("Number")%></STRONG> </FONT> <FONT SIZE="2" FACE="Verdana"> <STRONG><%=RSSchedule("Title")%></STRONG> </FONT> </TD> <TD VALIGN="TOP" WIDTH="35%"> <FONT SIZE="2" FACE="Verdana"> <%=RSSchedule("DateRange")%><BR> <% else If CourseNumber <> RSSchedule("Number") then CourseNumber = RSSchedule("Number") %> </FONT> </TD> <TD VALIGN="TOP" WIDTH="10%"> <FONT SIZE="2" FACE="Verdana"> <%=Cost%> </FONT> </TD> </TR> <TR> <TD VALIGN="TOP" WIDTH="55%"> <FONT COLOR="#FF0000" SIZE="2" FACE="Verdana"> <STRONG><%=RSSchedule("Number")%></STRONG> </FONT> <FONT SIZE="2" FACE="Verdana"> <STRONG><%=RSSchedule("Title")%></STRONG> </FONT> </TD> <TD VALIGN="TOP" WIDTH="35%"> <FONT SIZE="2" FACE="Verdana"> <%=RSSchedule("DateRange")%><BR> <%else%> <FONT SIZE="2" FACE="Verdana"> <%=RSSchedule("DateRange")%> </FONT><BR> <%end if%> <%end if%> <% Cost = RSSchedule("CourseCost") RSSchedule.MoveNext Loop %> </FONT> </TD> <TD VALIGN="TOP" WIDTH="10%"> <FONT SIZE="2" FACE="Verdana"> <%=Cost%> </FONT> </TD> </TR> </TABLE> </TD> </TR> </TABLE> <%RSSchedule.Close%> </BODY> </HTML>
You can further modify this page to support the no-tables browsers, discussed earlier, by using the Browser Capabilities object, a large If statement, and the HTML templates created in Listing 39.11. Some of the source for the browser-independent version of this page is shown in Listing 39.13.
... Set RSSchedule = Conn.Execute(SQL) GroupName = RSSchedule("Name") CourseNumber = RSSchedule("Number") %> </HEAD> <% Set BrowCap = CreateObject("MSWC.BrowserType") If BrowCap.Tables Then %> **** Include Code from previous listing **** <% else `(no tables) GroupName = "" CourseNumber = 0 %> <BODY BGCOLOR="#FFFFFF"> <% Do While Not RSSchedule.EOF IF GroupName <> RSSchedule("Name") THEN GroupName = RSSchedule("Name") %> <H1><%=GroupName%></H1> <% End If IF CourseNumber <> RSSchedule("Number") THEN CourseNumber = RSSchedule("Number") %> <H3><font color="#FF0000"><%=RSSchedule("Number")%></FONT> <%=RSSchedule("Title")%> (<%=RSSchedule("Cost")%>)</H3> <BLOCKQUOTE> <H4><%=RSSchedule("Date Range")%> <% Else %> <BR><%=RSSchedule("Date Range")%> <% End If%> </H4></BLOCKQUOTE> </BODY> </HTML>
A common technique used when creating database applications is to have two ways to view the data: a list view (often used to select one item from many), which shows only required information, and a detailed view, which shows all the information available about one item. You can use the same method when creating your Web pages. The course listing and schedule, both discussed in previous sections, assist students in finding the courses for which they want to register. The full information for any one course is available in the details page only.
You could add HTML tags to your list view page (course.asp)that would create a hyperlink from each course to the corresponding detailed view. Using a parameter of the Course ID, you could create URLs in the format "details.asp?id=45" (assuming your detailed view is named "details.asp" and located in the same directory as your list view), which would take users to the details page for Course ID 45. Adding that to your list view is easy--the changes required are marked in Listing 39.14, using asterisks. Notice that a field had to be added to the SQL query, Course ID, because it is needed to create your links. The course number could be used to link to instead, but those numbers are prone to change, making the ID a better choice as a key. This change will slightly alter the appearance of the list because it will underline and color each Course Title, just like other hyperlinks.
<%@ LANGUAGE="VBSCRIPT" %> <HTML> <HEAD> <TITLE>Courses</TITLE> </HEAD> <% Set conn = Server.CreateObject("ADODB.Connection") Conn.Open "DSN=Training" **SQL = "Select Course.ID, Course.Number, Course.Title, " SQL = SQL & "Course.Description, Category.Name as CourseGroup " SQL = SQL & "From Course,Category " SQL = SQL & "Where Course.Category_ID = Category.ID " SQL = SQL & "Order by Category.Name,Course.Title" Set RSCourses = Conn.Execute(SQL) CourseGroup = "" %> <BODY BGCOLOR="#FFFFFF"> <% Do While Not RSCourses.EOF %> <% If CourseGroup <> RSCourses("CourseGroup") Then CourseGroup = RSCourses("CourseGroup") %> <TABLE WIDTH="100%" BGCOLOR="#000000"> <TR> <TD> <FONT COLOR="#FFFFFF" SIZE="5" FACE="Verdana"> <STRONG><%=RSCourses("CourseGroup")%></STRONG> </FONT> </TD> </TR> </TABLE> <%End If%> <P> <FONT COLOR="#FF0000" SIZE="4" FACE="Verdana"> <STRONG><%=RSCourses("Number")%></STRONG> </FONT> <FONT COLOR="#000000" SIZE="4" FACE="Verdana"> ** <A HREF="details.asp?id=<%=RSCourses("ID")%>"> ** <STRONG><%=RSCourses("Title")%></STRONG></A> * </FONT> </P> <P><%=RSCourses("Description")%></P> <P> </P> <% RSCourses.MoveNext Loop RSCourses.Close %> </BODY> </HTML>
With those changes accomplished, you need to create your client's one-course page. Start out with an HTML template page, just like you did for the other pages, sketching out where all the various information should appear (see Figure 39.12). The HTML source for the template (see Listing 39.15) is then saved into a new file named "details.asp", and is used as your starting point. As before, the first step is to add all your code that opens the database and executes a SQL query, and then move on to the rest of the page.
<HTML> <HEAD> <TITLE>[Course Title]</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <P> <FONT COLOR="#FF0000" SIZE="4" FACE="Verdana"> <STRONG>[Course Number]</STRONG> </FONT> <FONT COLOR="#000000" SIZE="4" FACE="Verdana"> <STRONG>[Course Title]</STRONG> </FONT> </P> <P>[Description]</P> <P><STRONG>Audience:</STRONG>[Audience]</P> <P><STRONG>Prerequisites:</STRONG>[Prerequisites]</P> <P><STRONG>Duration:</STRONG>[Duration]</P> </BODY> </HTML>
FIG. 39.12
Details page template.
The database access code you added at the beginning of this file does something new: it uses a parameter, the ID value passed to the page, as a part of its SQL query. The resulting query will bring back only one record--all the information for that course. With all your variables already marked off (using the [ and ] characters), you can quickly go through and replace each placeholder with the appropriate database field. The finished version of the code for this page is shown in Listing 39.16, and the final page appears in Figure 39.13.
FIG. 39.13
Output of details.asp.
<HTML> <% Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "DSN=Training" SQL = "Select Course.* From Course Where Course.ID=" SQL = SQL & Request.QueryString("ID") Set RSCourse = Conn.Execute(SQL) %> <HEAD> <TITLE><%=RSCourse("Title")%></TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <P> <FONT COLOR="#FF0000" SIZE="4" FACE="Verdana"> <STRONG><%=RSCourse("Number")%></STRONG> </FONT> <FONT COLOR="#000000" SIZE="4" FACE="Verdana"> <STRONG><%=RSCourse("Title")%></STRONG> </FONT> </P> <P><%=RSCourse("Description")%></P> <P><STRONG>Audience:</STRONG><%=" " & RSCourse("Audience")%></P> <P><STRONG>Prerequisites:</STRONG> <%=" " & RSCourse("Prerequisites")%></P> <P><STRONG>Duration:</STRONG><%=" " & RSCourse("Duration")%></P> <% RSCourse.Close %> </BODY> </HTML>
Your lists turn out to be exactly what the client wants, and the detailed view works great; but you still don't have any way to register for one particular scheduled course date. You could turn the date ranges from the schedule listing (refer to Figure 39.11) into hyperlinks, but then there wouldn't be any direct path from the course listing. If you were working in MS Access or VB, one solution would be to add a grid control (or sub-form in Access) to the bottom of your detailed course view that would show all scheduled dates for that course. A form that displays a master record and multiple related records is known as a One to Many view and is commonly seen when dealing with order entry systems. You can achieve the same effect from ASP by creating a page that combines the features of both the list view pages and the detail view page. If you take the existing "details.asp" page and add a list of related dates to the bottom (see Figure 39.14), you could use those dates as links to a registration page.
FIG. 39.14
The Course Details page showing information on the course and any dates it
is scheduled for.
There is nothing limiting you to one SQL query per page, so you can create your list, after the current page's content, just like you did your list views. There is already an open connection to your database, so you just need to execute your query and then output and format that information inside a loop. You will want to display some special message if there are no scheduled dates for a particular course, which you can do by checking for EOF after executing your query. You should also filter this list by date, in the same manner as in the main schedule listing itself. The modified source (saved as "Courses.asp") appears in Listing 39.17. This code doesn't handle browser independence, but it can be modified easily to do so.
<HTML> <% Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "DSN=Training" SQL = "Select Course.* From Course Where Course.ID=" SQL = SQL & Request.QueryString("ID") Set RSCourse = Conn.Execute(SQL) %> <HEAD> <TITLE><%=RSCourse("Title")%></TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <P> <FONT COLOR="#FF0000" SIZE="4" FACE="Verdana"> <STRONG><%=RSCourse("Number")%></STRONG> </FONT> <FONT COLOR="#000000" SIZE="4" FACE="Verdana"> <STRONG><%=RSCourse("Title")%></STRONG> </FONT> </P> <P><%=RSCourse("Description")%></P> <P><STRONG>Audience:</STRONG> <%=" " & RSCourse("Audience")%></P> <P><STRONG>Prerequisites:</STRONG> <%=" " & RSCourse("Prerequisites")%></P> <P><STRONG>Duration:</STRONG> <%=" " & RSCourse("Duration")%></P> <% RSCourse.Close SQL = "Select Schedule.*," SQL = SQL & "Instructor.Last_Name & `, `" SQL = SQL & " & Instructor.First_Name as InstrName, " SQL = SQL & "Location.Name as LocName " SQL = SQL & "From Schedule,Instructor,Location " SQL = SQL & "Where (Schedule.Start_Date > Now) AND " SQL = SQL & "(Schedule.Course_ID=" & Request.QueryString("ID") SQL = SQL & ") AND " SQL = SQL & "(Schedule.Location = Location.ID) AND " SQL = SQL & "(Schedule.Instructor = Instructor.ID) " SQL = SQL & "ORDER BY Schedule.Start_Date" Set RSDates = Conn.Execute(SQL) If RSDates.EOF Then %> <P>No Scheduled dates for this course. Contact <A HREF="mailto:training@online-can.com"> <STRONG>Online</STRONG> </A> for more information. </P> <%Else%> <TABLE BORDER="0" WIDTH="100%"> <TR> <TD WIDTH="20%"><STRONG>Date</STRONG></TD> <TD WIDTH="30%"><STRONG>Location</strong></TD> <TD WIDTH="50%"><STRONG>Instructor</STRONG></TD> </TR> </TABLE> <TABLE BORDER="2" WIDTH="100%"> <% Do While Not RSDates.EOF %> <TR> <TD WIDTH="20%"> <A HREF="registration.asp?id=<%=RSDates("Schedule_ID")%>"> <%=RSDates("Start_Date")%> </A> </TD> <TD WIDTH="30%"><%=RSDates("LocName")%></TD> <TD WIDTH="50%"><%=RSDates("InstrName")%></TD> </TR> <% RSDates.MoveNext Loop RSDates.Close End If %> </BODY> </HTML>
Each Course's start date is now a hyperlink to "registration.asp", your currently nonexistent set of pages that enable people to sign up for that particular scheduled event. Each link passes a parameter, the Schedule ID, along to the pages. You'll see how that information is used when you create the registration pages later on. If there are no scheduled dates for this course, an e-mail address is provided (training@online-can.com) so that customers can write in to request that a date be set up.
The capability to create one to many pages is just one example of how standard interface design rules can be applied to your ASP application. The careful use of images, backgrounds, and advanced objects such as Java applets and ActiveX controls enable you to build almost any interface, all within a browser. As programmers, though, you don't necessarily know how to build a great looking site; you do know how to create Active Server Pages, however, and that is enough. Using the template idea discussed earlier in the chapter, you can have your artist or Web designer create samples for each of the pages you are building, and then you can quickly use them to produce a beautiful finished product.
Displaying data is great, and very useful for many companies and their sites, but it isn't all there is. You'll sometimes need to modify and add data to your site, even your client's little Training Catalog needs that capability. Customers who view the site may find a course that interests them and will want to register right away, and you want that to be as easy as possible. You want users to be able to just click the date they want to select and automatically jump to a form that contains all the course information. The information can be submitted at any time, giving your client's company the equivalent of 24-hours-a-day, 7-days-a-week customer service.
To add registration features to your client's site, you will need to create two new Active Server Pages. The first page will be an HTML form that collects all of the visitor's information and passes it on to the second page. The second page simply takes that information and adds it to the Registration table of the database. (Refer to the data model illustrated in Figure 39.1 for more details.)
The first page is mostly standard HTML and can be generated quickly using FrontPage or another editor. After you have the basics of the form in place, you can start adding your script. Back in the "Creating a One to Many Page" section, you made links to this registration page, passing the Schedule ID chosen as a parameter. With this Schedule ID, which you can retrieve by using the Request.QueryString Collection, you can make a SQL statement to retrieve the information needed about that event. This information, such as the course's title and scheduled dates, will be displayed as text above the form. This is so the user knows what they are registering for, but you'll also pass that information along to the next page by using hidden form fields. When a user clicks a particular date for a certain course, they will see a registration form that seems to be for just that one event (see Figure 39.15). The source for this page is included as Listing 39.18.
FIG. 39.15
Registration Form.
<% Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "DSN=Training" SQL = "SELECT Schedule.*,Course.Title FROM Schedule,Course " SQL = SQL & "WHERE (Schedule.Course_ID = Course.ID) " SQL = SQL & "AND (Schedule.Schedule_ID=" SQL = SQL & Request.QueryString("ID") & ")" Set RSSchedule = Conn.Execute(SQL) %> <HTML> <HEAD> <TITLE>Registration</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <P> <FONT COLOR="#000000" SIZE="5" FACE="Verdana"> <B>Course Registration</B> </FONT> </P> <FORM ACTION="savereg.asp" method="POST" name="Course Registration"> <INPUT TYPE="hidden" name="Course_Date" value="<%=RSSchedule("Start_Date")%>"> <INPUT TYPE="hidden" name="Course_Name" value="<%=RSSchedule("Title")%>"> <TABLE BORDER="0"> <TR> <TD WIDTH="80"> <STRONG>Date:</STRONG> </TD> <TD> <%=RSSchedule("Start_Date")%> </TD> </TR> <TR> <TD WIDTH="80"> <STRONG>Course:</STRONG> </TD> <TD> <%=RSSchedule("Title")%> </TD> </TR> <TR> <TD WIDTH="80"> <STRONG>Name:</STRONG> </TD> <TD> <INPUT TYPE="TEXT" SIZE="60" NAME="Name"> </TD> </TR> <TR> <TD WIDTH="80"> <STRONG>Organization:</strong> </TD> <TD> <INPUT TYPE="text" SIZE="60" NAME="Organization"> </TD> </TR> <TR> <TD> <STRONG>Position:</STRONG> </TD> <TD> <INPUT TYPE="text" SIZE="60" NAME="Position"> </TD> </TR> <TR> <TD> <STRONG>Address:</STRONG> </TD> <TD> <INPUT TYPE="text" SIZE="60" NAME="Address"> </TD> </TR> <TR> <TD> <STRONG>City/Town:</STRONG> </TD> <TD> <INPUT TYPE="text" SIZE="60" NAME="City"> </TD> </TR> <TR> <TD> <STRONG>Postal Code:</STRONG> </TD> <TD> <INPUT TYPE="text" SIZE="20" NAME="PostalCode"> </TD> </TR> <TR> <TD WIDTH="80"> <STRONG>Email:</STRONG> </TD> <TD> <INPUT TYPE="text" SIZE="60" NAME="EMail"> </TD> </TR> <TR> <TD WIDTH="80"> <STRONG>Phone:</STRONG> </TD> <TD> <INPUT TYPE="text" SIZE="20" NAME="Phone"> </TD> </TR> <TR> <TD WIDTH="80"> <STRONG>Fax:</STRONG> </TD> <TD> <INPUT TYPE="text" SIZE="20" NAME="Fax"> </TD> </TR> <TR> <TD WIDTH="80"> </TD> <TD ALIGN="right" VALIGN="bottom"> <P ALIGN="right"> <INPUT TYPE="submit" AME="Submit Form" VALUE="Submit Registration Form"> </P> </TD> </TR> </TABLE> </FORM> </BODY> </HTML>
All of the fields in a form, whether visible or not, are available to the target page as part of the Request.Form collection. A registration system may take a variety of information, depending on the needs of the system; but in your case, all you want is standard name and address style information. If you want to collect different information, you have to modify the Form, the receiving Active Server Page, and the database, for everything to work properly. The receiving page is where the actual information saving occurs, using the AddNew method of a Recordset (see Listing 39.19). After you have added a new record, you can put values into each field through straight assignment, RSSave("Name")=Request.Form("Name"), making for pretty easy, though somewhat repetitive code. It is standard practice to put up some sort of confirmation message, as shown in Listing 39.19, or to display any field validation errors encountered. Regardless of what you choose to display, it is better than a blank page--users require some feedback; otherwise, they will register over and over again.
<%@ LANGUAGE="VBSCRIPT" %> <HTML> <head> <TITLE>Save Registration Form</TITLE> </HEAD> <% Set conn = Server.CreateObject("ADODB.Connection") Conn.Open "DSN=Training" Set RSSave = Server.CreateObject("ADODB.Recordset") SQL = "SELECT registration.* FROM registration" RSSave.Open SQL,Conn , 1, 2 %> <BODY BGCOLOR="#FFFFFF"> <% RSSave.AddNew RSSave("Course_Name") = Request.Form("Course_Name") RSSave("Course_Date") = Request.Form("Course_Date") RSSave("Name") = Request.Form("Name") RSSave("Organization") = Request.Form("Organization") RSSave("Position") = Request.Form("Position") RSSave("Address") = Request.Form("Address") RSSave("City") = Request.Form("City") RSSave("Postal Code") = Request.Form("Postal Code") RSSave("Email") = Request.Form("Email") RSSave("Phone") = Request.Form("Phone") RSSave("Fax") = Request.Form("Fax") RSSave("TimeRegistered")=Now() RSSave.Update %> <H1>Registration Saved Successfully...</H1> <H3>Click here to return to the <A HREF="default.asp">Training Page</A></H3> </BODY> </HTML>
NOTE: In Listing 39.19 the Open method of a Recordset object is used instead of just using the Execute method of the Connection object. This is different than what has been done in the previous examples because you are adding a new record in this example. The Open method of a Recordset enables you to specify more options. The parameters of the Open method are as follows:
- The command to execute (your SQL query)
- The connection to use
- The type of cursor you want to open ( 1 for a Keyset Cursor)
- The type of locking you want to have on your recordset (2 for Pessimistic Locking)
You will be using this syntax whenever you need to add or modify a record from here on.
You now have everything your client asked for: the site displays the right information, in several different ways, and users can easily register for any scheduled course. Are you done? Although the site does everything that was requested, there is still one thing unaccounted for. How will your client update this information? Your goal was to make it easy to change this information on a regular basis, easier than a paper catalog. You have stored all their courses, schedules, locations, and instructors into a fancy database, but you haven't given them any method for modifying it.
There are really two alternatives (three if you count going directly to the database, but that isn't really a solution):
The second option is easy. You can create this application right in Access, or you can create in Visual Basic. The choice of creating an ASP interface is more difficult. For most programmers, creating a full database interface using Active Server Pages is relatively unknown. Fortunately for you, you have this book to help you get started. In the end, the extra work required to create an ASP interface will be justified by its advantages over a traditional application:
Creating the full application to administer this training catalog goes beyond the scope of this chapter, but one example will be provided that you can use as a reference for any others you decide to create. You'll be constructing a page (really a set of pages) to edit, add, and delete courses.
The first page you'll need to create is another list view, almost identical to the one shown in Listing 39.14, but with different hyperlinks for each course. Make a copy of the original code from Listing 39.8 and change the links pointing at "details.asp" to point to "EditCourse.asp", still supplying the Course ID as a parameter. From this list, which should be restricted to administrators of the site, you will be able to pick the course you want to edit simply by clicking its name. The editing form, "EditCourse.asp", is the first example page we are going to cover. Once again, you'll start on making this page by creating a template in HTML. The finished page will basically be a Form, as shown in Figure 39.16, with some simple scripting code added in.
Unlike this chapter's registration example, the data you retrieve needs to appear directly in the fields of your Form. You can achieve this effect by substituting the database field references into the value parameters of the appropriate input fields. This is done a little differently in the case of the <TEXTAREA> fields, which you need for editing Memo fields. For the <TEXTAREA> fields, you just place your data between the tags, and it will be placed into the field at runtime.
Another difference from the previous form example is that you will require more than one submit button, each of which will be labeled (and named) differently. When clicked, they will all submit the form using the same target page, but one of the button fields will have a different value. Later on in this section, you will see how to determine which button was clicked. This enables you to have both a delete button and a save button handled through the same form. The functionality of adding a course will be handled by placing a special link onto your list view, which will be covered a little later in this chapter. The source for this form page is shown in Listing 39.20, with the form's Action URL set to "SaveCourse.asp." Notice the category combo box, and the code required to fill it. A page with several such combo boxes would quickly grow complicated.
FIG. 39.16
Editing a course.
<HTML> <% Set Conn=Server.CreateObject("ADODB.Connection") Conn.Open "DSN=Training" SQL = "Select Course.* From Course Where Course.ID = " SQL = SQL & Request.QueryString("ID") Set RSCourse = Conn.Execute(SQL) SQL = "Select Category.* From Category" Set RSCategory = Conn.Execute(SQL) %> <HEAD> <TITLE>Edit Course</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <H1>Edit Course</H1> <form action="SaveCourse.asp" method="POST" name="Edit Course"> <INPUT TYPE="hidden" name="Course_ID" value="<%=RSCourse("ID")%>"> <TABLE BORDER="0"> <TR> <TD><STRONG>Category:</STRONG></TD> <TD><SELECT NAME="Category" size="1"> <%Do While Not RSCategory.EOF If RSCategory("ID") = RSCourse("Category") Then %> <OPTION SELECTED VALUE="<%=RSCategory("ID")%>"> <%=RSCategory("name")%></OPTION> <% Else%> <OPTION VALUE="<%=RSCategory("ID")%>"> <%=RSCategory("name")%></OPTION> <% End If RSCategory.MoveNext Loop %> </SELECT></TD> </TR> <TR> <TD><STRONG>Number:</STRONG></TD> <TD><INPUT TYPE="text" size="5" name="Number" value="<%=RSCourse("Number")%>"></TD> </TR> <TR> <TD WIDTH="80"><STRONG>Title:</STRONG></TD> <TD><INPUT TYPE="text" size="50" name="Title" value="<%=RSCourse("Title")%>"></TD> </TR> <TR> <TD VALIGN="top"> <STRONG>Description:</STRONG></TD> <TD><textarea name="Description" rows="2" cols="50"><%=RSCourse("Description")%> </TEXTAREA></TD> </TR> <TR> <TD VALIGN="top"> <STRONG>Audience:</STRONG></TD> <TD><TEXTAREA NAME="Audience" rows="2" cols="50"><%=RSCourse("Audience")%> </TEXTAREA></TD> </TR> <TR> <TD VALIGN="top"> <STRONG>Prerequisites:</STRONG></TD> <TD><TEXTAREA NAME="Prerequisites" rows="2" cols="50"><%=RSCourse("Prerequisites")%> </TEXTAREA></TD> </TR> <TR> <TD><STRONG>Cost:</strong></TD> <TD><INPUT TYPE="text" size="20" name="Cost" value="<%=RSCourse("Cost")%>"></TD> </TR> <TR> <TD><STRONG>Duration:</STRONG></TD> <TD><INPUT TYPE="text" size="20" name="Duration" value="<%=RSCourse("Duration")%>"></TD> </TR> <TR> <TD> </TD> <TD><P ALIGN="right"><input type="submit" ame="Submit" value="Save"><INPUT TYPE="submit" ame="Submit" value="Delete"></P> </TD> </TR> </TABLE> </FORM> </BODY> </HTML>
NOTE: To test this page without building the list view or "SaveCourse.asp", you can go directly to the page by specifying its URL in your browser. Attach a parameter to the URL for the Course ID ("EditCourse.asp?ID=1"). This type of testing will work for any of your Active Server Pages.
As you might have guessed, the page containing our form (refer to Listing 39.20) isn't really that difficult; all the code that actually modifies the database is contained in the Action page, "SaveCourse.asp". This page can be called to perform two separate tasks: delete the record or save changes to it. The value of your Form fields will be checked right at the beginning of the page to determine which of the two possible tasks (deleting or editing) is intended to be (see Listing 39.21).
<% If Request.Form("Submit") = "Delete" Then `We are deleting the record Else `We are saving it End If %>
If you are deleting the record, then you don't need to worry about any of the form fields except for the Course ID. The code to perform the delete by using a SQL Delete statement is shown in Listing 39.22. After the delete has been performed, display a completion statement, and provide a link back to the list view page ("EditList.asp").
<% `Delete Record Set Conn = Server.CreateObject("ADODB.Connection") SQL = "Delete * FROM Course Where Course.ID = " & Request.Form("Course_ID") Conn.Execute SQL %>
NOTE: In this example, you haven't given the user any chance to confirm the deletion--no "Are you sure?" If you want to, you can set up something like that by displaying a simple form on this page with two buttons (Yes and No). Clicking either button would take the user to another page, "delete.asp", that would either delete the record or redirect the user back to the editing page, based on which button is chosen.
If you do not delete the record, then you are saving the changes. You can use a SQL Insert statement to modify the fields, but when you have Memo fields involved (text fields of indeterminate length), it is a better idea to work with the record directly. Using the Course ID, create a recordset containing only that one record. Now, just go through all the fields, set the database field equal to the value of its equivalent Form field, display a completion message, and then you are done. The complete listing for the "SaveCourse.asp" page is provided in Listing 39.23, including both the deletion and editing code.
<%@ LANGUAGE="VBSCRIPT" %> <HTML> <HEAD> <TITLE>Save Registration Form</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <% Set Conn = Server.CreateObject("ADODB.Connection") If Request.Form("Submit") = "Delete" Then `Delete the Record SQL = "Delete * From Course Where Course.ID = " & Request.Form("Course_ID") Conn.Execute SQL Response.Write "<H1>Record Deleted</H1>" Else SQL = "Select * From Course Where Course.ID = " & Request.Form("Course_ID") Set RSCourse = Server.CreateObject("ADODB.Recordset") Conn.Open "DSN=Training" RSCourse.Open SQL,Conn,1,2 RSCourse("Category") = Request.Form("Category") RSCourse("Title") = Request.Form("Title") RSCourse("Number") = Request.Form("Number") RSCourse("Description") = Request.Form("Description") RSCourse("Audience") = Request.Form("Audience") RSCourse("Prerequisites") = Request.Form("Prerequisites") RSCourse("Cost") = Request.Form("Cost") RSCourse("Duration") = Request.Form("Duration") RSCourse.Update RSCourse.Close Response.Write "<H1>Record Saved</H1>" %> </BODY> </HTML>
NOTE: Using DAO or RDO, you have to use the Edit method before changing those values. In ADO, the Edit command is assumed.
With those last two pages completed, you have the capability to edit or delete any course from your database. The last thing you have to build is the page to add a new course, which you will provide as a link from your administrative list view, "EditList.asp". A simple hyperlink to a page, "NewCourse.asp" in your example, is all you need. Because you are adding a new record, no parameters are necessary.
You don't need to look at the Course table to display this page--the form starts out blank--but you do have to open a recordset of the Category table to fill in your combo box. The source for "NewCourse.asp" is displayed in Listing 39.24. As in the edit/delete form, most of the code is contained in the Action URL, which, in this case, points at the file "AddCourse.asp".
<HTML> <% Set Conn = Server.CreateObject("ADODB.Connection") SQL = "Select * From Category" Set RSCategory = Conn.Execute(SQL) %> <HEAD> <TITLE>New Course</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <H1>Add Course</H1> <FORM ACTION="AddCourse.asp" method="POST" name="New Course"> <TABLE BORDER="0"> <TR> <TD><STRONG>Category:</STRONG></TD> <TD><SELECT NAME="Category" size="1"> <%Do While Not RSCategory.EOF <OPTION VALUE="<%=RSCategory("ID")%>"> <%=RSCategory("name")%></OPTION> <% RSCategory.MoveNext Loop RSCategory.Close %> </SELECT></TD> </TR> <TR> <TD><STRONG>Number:</STRONG></TD> <TD><INPUT TYPE="text" size="5" name="Number"></TD> </TR> <TR> <TD WIDTH="80"><STRONG>Title:</STRONG></TD> <TD><INPUT TYPE="text" SIZE="50" NAME="Title"></TD> </TR> <TR> <TD VALIGN="top"><STRONG>Description:</STRONG></TD> <TD><TEXTAREA NAME="Description" rows="2" COLS="50"></TEXTAREA></TD> </TR> <TR> <TD VALIGN="top"><STRONG>Audience:</STRONG></TD> <TD><TEXTAREA NAME="Audience" rows="2" COLS="50"><%=RSCourse("Audience")%></TEXTAREA></TD> </TR> <TR> <TD VALIGN="top"> <STRONG>Prerequisites:</STRONG> </TD> <TD><TEXTAREA NAME="Prerequisites" rows="2" COLS="50"></TEXTAREA></TD> </TR> <TR> <TD><STRONG>Cost:</STRONG></TD> <TD><INPUT TYPE="text" SIZE="20" NAME="Cost"></TD> </TR> <TR> <TD><STRONG>Duration:</STRONG></TD> <TD> <INPUT TYPE="text" SIZE="20" NAME="Duration"> </TD> </TR> <TR> <TD> </TD> <TD><P ALIGN="right"><INPUT TYPE="submit" AME="Submit" VALUE="Add"></P> </TD> </TR> </TABLE> </FORM> </BODY> </HTML>
Within your code for "AddCourse.asp" you go through a series of validation checks to ensure the values are correct before adding the record to the database. If everything is okay, you can open a Recordset from the Course table and proceed to add your record. From this point on, there isn't a great deal of difference between saving this record and saving a registration. You call the AddNew method of your Recordset, set the values of the database fields to the values from your Form, and display a completion message. That's it, you're done. The source for the finished page is in Listing 39.25.
<%@ LANGUAGE="VBSCRIPT" %> <HTML> <HEAD> <TITLE>Save Registration Form</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF"> <% Error = False If Not IsNumeric(Request.Form("Number")) Then Response.Write "<H2>Course Number has to be a Number</H2>" Error = True End If If Not IsNumeric(Request.Form("Cost")) Then Response.Write "<h2>Cost has to be a Number</h2>" Error = True End If If Not IsNumeric(Request.Form("Duration")) Then Response.Write "<H2>Duration has to be a Number</H2>" Error = True End If If Not Error Then Set Conn = Server.CreateObject("ADODB.Connection") Set RSCourse = Server.CreateObject("ADODB.Recordset") Conn.Open "DSN=Training" SQL = "Select * From Course" RSCourse.Open SQL,Conn , 1, 2 RSCourse.AddNew RSCourse("Category") = Request.Form("Category") RSCourse("Title") = Request.Form("Title") RSCourse("Number") = Request.Form("Title") RSCourse("Description") = Request.Form("Description") RSCourse("Audience") = Request.Form("Audience") RSCourse("Prerequisites") = Request.Form("Prerequisites") RSCourse("Cost") = Request.Form("Cost") RSCourse("Duration") = Request.Form("Duration") RSCourse.Update RSCourse.Close Response.Write "<H1>Record Added</H1>" End If %> </BODY> </HTML>
Through the examples in the last few sections, you have the information you need to build a complete ASP application to maintain most database-driven sites. Don't just assume a Web-based administration system is necessary though; an Access application will fit the needs of many sites and could take a lot less work. If your client, or company, needs the capability to edit the database from anywhere, or if so many people require editing capability that it makes distribution a problem, then an Internet-based solution is probably best. If, on the other hand, one person will be doing the editing, always from the same place, then a more traditional system is probably the correct choice.
If you only create the pages in this chapter, you wouldn't be done. There are many other static pages to build, images to include in the pages, and general tweaking of the entire site. You are not ever really finished until the site is completely ready to be opened to the public, and even then additions will be asked for. Some potential additions to the example Training site are almost guaranteed to be requested, such as a visitor counter, or other bells and whistles (such as "tips of the day" or extensive search capabilities).
Personalization is customizing the behavior or appearance of your pages based on whom the visitor is. The "Internet Start" site from Microsoft, http://home.microsoft.com is an excellent example. By providing information and selecting options, such as what sports you are interested in, or where you live, this site generates custom content that can be different for everyone who views it. Just using a person's name on your site, such as "Good Morning, Dave!" is a form of personalization.
There are two parts to creating a personalized Web site. The obvious one is the actual customization, as just described. The other part is the tricky one: getting the information. Asking a user to enter information into a survey form just so you can say "Welcome, Dave!" isn't very worthwhile. A preferred way is to work backwards--figure out what information you are already collecting, or should be collecting, about the user, and then see what you can do with that information.
In the Training Catalog example, a user provides information only through the registration process, so that is where you would attempt to customize the site. If a user registers for a course on your client's site, then, at some time in the future, the same user registers for another course, they will have to re-enter all their information. If you design the site so that it automatically brings up the user's previous entries, while still enabling the user to change the information if needed, then registration becomes personalized, easier, and quicker.
Registration entries are already being time-stamped, so it is easy to pull up the last entry made by a particular person. Before doing that, you must first identify the current user, which in this case, you can do through their e-mail address. By showing a small form to users that asks them to provide their e-mail address and a password before entering the actual registration page, you can find and load up the last information they entered. The lookup of information would be accomplished using a SQL query such as this one; SELECT * FROM USERS WHERE USERS.EMAIL="<ENTERED EMAIL ADDRESS>".
If this is the user's first time in your client's system, a new record has to be created for them (in the database) containing their e-mail address and password. You could design the system to e-mail them their password to ensure security.
A common addition requested for Web sites is a counter, some method of tracking how many times the site, or particular pages, have been accessed. When using IIS 3.0, all this information is already being saved to log files. Although you can pull information out of those files to create statistical reports about your site, it doesn't provide the same thing that a counter does: instant visual update of hits to your page. Many companies can provide a site with a Web-counter service, but you can accomplish the same thing yourself.
To create a counter, you need to maintain a numeric value in a database, one for each page on which you are tracking hits, and then increment that value every time that page is loaded. At the same time as you are accessing the database to update it, you can return the value for display on your site. A database table that contains two fields: one to hold the path of your page, and one to hold the number of hits. The example shown in Listing 39.26 updates the value and displays it (the ODBC DSN, table name, and field names will change depending on how you set up your database).
<HTML> <HEAD><TITLE>Our Home Page</TITLE></HEAD> <BODY> <Our Page's Content> <% Set Conn=Server.CreateObject("ADODB.Connection") Set RSCounter = Server.CreateObject("ADODB.Recordset") Conn.Open "DSN=Counter" SQL = "Select * from Pages " SQL = SQL & "Where Pages.Page='" SQL = SQL & Request.ServerVariables("PATH_TRANSLATED") & "`" RSCounter.Open SQL, Conn, 1, 2 If RSCounter.EOF Then `There is no record for this page RSCounter.AddNew RSCounter("Page") = Request.ServerVariables("PATH_TRANSLATED") RSCounter("Hits") = 1 Response.Write "<P><STRONG>This page has been visited one time</STRONG></P>" Else RSCounter("Hits") = RSCounter("Hits") + 1 Response.Write "<P><STRONG>This page has been visited " & RSCounter("Hits") & " times</STRONG></P>" End if RSCounter.Update RSCounter.Close %> </BODY> </HTML>
NOTE: In Listing 39.26, the PATH_TRANSLATED Server Variable is used, which contains the physical path to the file to uniquely identify your pages. This is done because, while there can be more than one virtual path to a Web page, there can only be one physical path, making it perfect to use as a unique identifier.
As this is a Visual Basic book, many of you are probably interested in knowing how you can use VB with the Internet. Creating components for ASP is one way to do this, but it isn't really anything new. Your component runs on the server, accesses data on the server, and works with a client (the ASP page) that is also on the server. Nothing different than a standard VB application on one machine, or on an office LAN. ActiveX controls are very different, but still are not really considered using the Internet--the control is installed on the client, runs on the client, and is working together with a program on the client side (the browser). The fact that the control is downloaded across the Internet doesn't really make it an Internet application.
A true VB Internet application has to run on one machine and work with or access data from another machine, across the Internet. Several technologies (from Microsoft) exist to enable you to create applications (and components) such as the Advanced Data Connector (ADC), the Advanced Data Space (ADS), and the Advanced Data Factory (ADF).
ADC is the name for a set of objects from Microsoft that are designed to give your client-side program the capability to access Databases across the Internet. By specifying the URL of a Web server (such as http://www.online-can.com) and an ODBC Connection String, including the server-side DSN, you can execute queries against that remote database. ADC returns the results of these queries to your client program in an ADO-style Recordset. You can add, delete, or modify records; changes made to the recordset are made to the server database, just like in regular ADO. Setting up ADC for use is relatively easy. After it has been installed on your client machine, Visual Basic applications can create instances of its objects by using the CreateObject command. Examples of using this object are given in "A Sample Application."
NOTE: If you have ever programmed in Powerbuilder (a development tool from Powersoft), you may have noticed a very interesting feature of that program. After you receive a "recordset" back from your DBMS, you can change the filtering or ordering of the records, without having to talk to the database again. ADO allows this same thing, avoiding unnecessary network traffic.
The sample application created in this section shows the use of two main components: the Advanced Data Space object, which allows you to create objects on the server and access them remotely, and the Advanced Data Factory, which is created on the server, and handles the database access and returns the query results.
In the Training catalog example, you are going to quickly fill a list box with course names from the server. This uses the existing database for the Training catalog example and assumes that an appropriate DSN is still set up on the server.
To create and run the example, perform the following steps:
Visit the Microsoft ADC site for more information at http://www.microsoft.com/adc.
Dim objADF as object Dim objADS as object Dim RSCourses as object Dim sConnection as Variant Dim sSQL as Variant Set objADS = CreateObject("AdvancedDataSpace") Set objADF = objADS.CreateObject("AdvancedDataFactory", _ "http://www.online-can.com") `Replace this server name with yours sConnection = "DSN=Training" sSQL = "Select Course.Title From Course" Set RSCourses = objADF.Query(sConnection, sSQL) Do While Not RSCourses.EOF List1.AddItem RSCourses("Title") RSCourses.MoveNext Loop
FIG. 39.17
Our sample application.
You've seen how to create a simple database-driven Web site using ASP, but that is not the limit of what you can do. With the ability to access any COM object, including those written in VB, you can create Web sites that do almost anything. The Advanced Data Connector and its related components opens up a whole new group of possible applications. Your Visual Basic programs can now work across the Internet, making distributed computing easier than ever. Overall, developing Internet applications has become easier than ever and Visual Basic programmers now have the edge.
For more information on the topics discussed in this chapter, see the following chapters:
There are also many Web sites that offer useful information on ASP, building ASP components, ADO, and ADC. Here are a few examples:
© Copyright, Macmillan Computer Publishing. All rights reserved.