So far, this book has covered the basics of programming for Web server applications. This chapter presents a complete server-side application that uses the Win/CGI interface. The WebGuest site actually consists of a suite of programs that each perform different functions. There are two applications that generate dynamic HTML pages, an application for recording registration information, one for searching the database, one for creating various lists from the database, and of course, one that displays the registered information.
The WebGuest site is an example of using CGI applications for guest book registration. This is probably the most popular use of CGI applications on the Web today. Just about every Web site that is capable of running CGI applications uses a guest book of some sort. The guest book is used to collect information about who is viewing the Web site, what equipment and Web browser the visitor are using, and what their interests in the site might be. Other sites employ CGI registration applications for gathering information from potential subscribers and for customer feedback and product support.
This chapter covers the design of the WebGuest site, the design of the database that stores the information gathered, and how to code and test the WebGuest site.
Before you decide how your Web site should look or how you will program the applications that control the entry and display of guest book information, you must decide what information to collect. The type of data you'll track depends primarily on what you want to do with the information. If your site is used for software support, you'll need to know the specifics of the user's hardware and operating system. If your site enables the user to request additional information be sent to them, you'll need to collect input about how to send the information and what information to send.
Obviously, you can ask the user any question you wish. However, don't count on having too many people answer many questions about personal matters. Most users are happy to enter contact information and information about the software and hardware they're using. In addition, if you're marketing a product or service, the user will probably be willing to let you know his or her interest in your product or service. Also, demographic information such as age range, makeup of the household, gender, and so on, are frequently used to tailor a Web site to match the user's assumed interests. Most visitors to a site providing such customization won't mind providing this information, either.
The other consideration is the number of fields on the data entry form that the user must complete. If it contains too many questions or the form appears overwhelming, the user may choose to skip the form altogether. You must balance the information you require with the user's willingness to provide that information. The key is to make sure the user perceives that he or she will receive some benefit from providing the requested information. If your Web site is used for marketing purposes, I'd recommend obtaining one of the many books that discuss Internet marketing strategies. My favorite is "Guerilla Marketing Online" by Jay Conrad Levinson and Charles Rubin/
For the WebGuest site, I've chosen to collect basic contact information. This includes name, address, work phone, e-mail address, and Web page URL. Users will also be able to choose an icon to represent their entry in the guest book. WebGuest will also track information provided by the CGI interfacethe user's browser, the user's IP address, and the user's host name.
In addition to being able to enter information into the WebGuest database, users can also conduct searches on information entered by others. The search form is identical to the data entry form. The user can also list information, such as the type of Web browser being used. Using this feature, the user would be presented with a list of the Web browsers that have been used to access the WebGuest site. The list would include the name of the Web browser application and a count of the number of times that browser has been used at the WebGuest site. Beneath each entry in this list is a list of all the users who registered using that user agent. This list contains a hyperlink to a page providing the WebGuest entries for each of these users. As you'll see in the section titled "Designing The WebGuest Site," there are many other methods of searching and listing the data contained in the WebGuest database.
Obviously, when developing a Web site such as the WebGuest site you want to keep privacy in mind. If you collect any personal information such as gender, makeup of the household, income range, and so on, you probably don't want to make such information available to the public. You could provide summary data, such as what percentage of registered users are within a certain age range, as long as there is no way of connecting such information back to individual registrants.
With the considerations of the previous section clearly in mind, it's time to design the WebGuest site. The site starts at an entry page that provides links to the rest of the site. The structure of the WebGuest site is shown in Figure 8.1. The page at the top center of Figure 8.1 is the entry page. It serves as the launching pad for the journey through the WebGuest site.
Figure 8.1. The structure of the WebGuest site.
The entry page, shown in Figure 8.2, is dynamically generated by one of the WebGuest applications. It provides a count of the number of records in the registration database as well as links to the registration page, the list page, and the search page. The application that generates the page is created from the entry.vbp project presented in the "Coding the Applications" section later in this chapter. The page is dynamically generated in order to provide the record count.
Figure 8.2. The WebGuest entry page.
The first link on the entry page takes the user to the registration page, shown in Figure 8.3. The second link shown takes the user to the search page. The searching and registration pages are generated by the gen-reg.exe application, which the links on the entry page execute. The application is passed different query strings based on whether the search or register link is selected. The pages created are identical except for the action taken when the HTML Submit button is clicked and some of the text on the page. The registration page's form POSTs the data to an application named register.exe. The search page's form POSTs the form data to an application named search.exe.
Figure 8.3. The WebGuest registration form.
A CGI application is used to generate these pages in order to retrieve the paths to the available pictures from a table in the database. Although this is not required to make the application operate, it does allow the pictures used at the site to be changed without changing any HTML forms or the CGI applications.
The list page, shown in Figure 8.4, provides links to the available methods of listing the WebGuest data. This page is staticit is resident on the server, not generated by a CGI application. The HTML for the page, provided in Listing 8.1, should be placed in a file named wg-list.htm. The file should reside in the Web server's logical root directory. All the links execute the same CGI application, but with different query strings. This application, gen-list.exe, checks the provided query string and creates a list based on its value. In this chapter, I've provided four options: List by Last Name, List by State, List by Remote Host, and List by User Agent. It is quite simple to extend or modify the available lists, as you'll see in the code for gen-list.exe.
Figure 8.4. The WebGuest list page.
Listing 8.1. The List page HTML.
<HTML><HEAD><TITLE>WebGuest List Page</TITLE></HEAD> <BODY BGCOLOR="#FFFFFF"> <H1 ALIGN=CENTER>WebGuest Database Lists</H1> <P ALIGN=LEFT>WebGuest allows you to view the entries in the database using any of these lists:</P> <UL> <LI><P ALIGN=LEFT>List by <A HREF="/cgi-win/gen-list.exe?NAME">Last Name</A></P> </LI> <LI><P ALIGN=LEFT>List by <a href="/cgi-win/gen-list.exe?STATE">State</A></P> </LI> <LI><P ALIGN=LEFT>List by <A HREF="/cgi-win/gen-list.exe?HOST">Remote Host</A></P> </LI> <LI><P ALIGN=LEFT>List by <A HREF="/cgi-win/gen-list.exe?AGENT">User Agent</A></P> </LI> </UL> <P>Return to the <A HREF="/cgi-win/entry.exe">Home Page</A></P> </BODY> </HTML>
The HTML pages created by gen-list.exe and search.exe provide links to the WebGuest entries for the items found. Figure 8.5 shows an example of the page produced when the List by Last Name link is clicked. These links create pages that provide all the information the users entered when they registered at the WebGuest site. They also provide the user's IP address, host name, and the browser used when registering. An example is shown in Figure 8.6.
Figure 8.5. A sample page from the List by Last Name link.
Figure 8.6. The WebGuest Results page.
If this were a published site on the Internet, you might want to limit which data other people could view. For instance, some users may not want their e-mail address made public without their permission. A checkbox on the form next to possibly sensitive fields could be used to allow the registering user to mark those fields as private data. The CGI application that generates the results page would then exclude those fields from its output page for that registered user.
The WebGuest database is a very simple one. It consists of two tables: one for the registration information provided by the user and one for the filenames of the pictures to be displayed on the registration and search pages. The applications presented in this chapter assume the data is stored in a Microsoft Access database, but this is not a necessity.
You can create the WebGuest database in any directory on your HTTP server machine, or in any directory which the JET engine or an ODBC driver can access. The WebGuest applications, as presented in this chapter, use a hard-coded path to the database file. This path could be stored in the registry and then retrieved when the application executes, but for simplicity I just hard-coded it.
The database can be created using either Jet 2.5 or Jet 3.0 tools. However, it is recommended that you use the Jet 3.0 database tools because performance is greatly improved with Jet 3.0.
After the database is created, you can proceed to the next major section, "Coding the Applications," and get into the real meat of this chapter.
The main table, named Guests, contains all the fields that the user fills in on the registration page. It also has fields for some of the information provided by the user's Web browser and the HTTP server. This includes the Web browser's user agent designation and the user's IP address and host name.
Also included is an AutoNumber field (GuestID) which helps provide the registered user with a user identification number. This is useful for sites that provide service to the registered users on an ongoing basis.
The format of the Guests table is shown in Table 8.1. The table provides the field name, data type, and size for each field in the table. In this chapter's applications, none of the fields are required. The text fields should all allow zero length strings to accommodate the fact that the user may not provide data for the field.
Field Name |
Data Type |
Size |
GuestID
|
AutoNumber
|
Long Integer
|
FirstName
|
Text
|
255
|
LastName
|
Text
|
255
|
Title
|
Text
|
255
|
Organization
|
Text
|
255
|
StreetAddress
|
Text
|
255
|
Address2
|
Text
|
255
|
City
|
Text
|
255
|
State
|
Text
|
255
|
ZipCode
|
Text
|
255
|
Country
|
Text
|
255
|
WorkPhone
|
Text
|
25
|
Fax
|
Text
|
25
|
Email
|
Text
|
255
|
URL
|
Text
|
50
|
OS
|
Text
|
25
|
PictureID
|
Text
|
10
|
UserAgent
|
Text
|
255
|
RemoteIP
|
Text
|
50
|
RemoteHost
|
Text
|
255 |
The other table in the database is called Pictures. This is where the names of the pictures displayed in the WebGuest site are stored. The registration page allows the user to choose a picture from the ones specified in this table. The selected picture is then displayed with that entry on the WebGuest results page.
The Pictures table consists of two fields: PictureID and PathName. The PictureID field is the primary key field that matches the PictureID foreign key field in the Guests table. As in the Guests table it is a 10 character Text field.
The PathName field is a 255 character Text field. This field is where you specify the HTTP server-based paths for the pictures to be displayed on the registration and search pages. The paths are relative to your HTTP server's path space. In other words, the PathName field for a picture should match the URI that would be used to retrieve the file from your HTTP server. If, for example, all the pictures reside in a directory named IMAGES, which is below your HTTP root directory, the PathName for a picture named FACE.GIF would be /IMAGES/FACE.GIF.
The WebGuest site utilizes several different CGI applications. These applications are built around the concepts discussed in Chapter 6, "The Win/CGI Interface," and the code presented in Chapter 7, "Creating CGI Applications In Visual Basic." If you have not reviewed these chapters, now is a good time to do so.
The Visual Basic projects in this chapter use the code modules discussed in Chapter 7, in addition to a module specific to each application. There is also a new module named database.bas. The code from the modules presented in Chapter 7 will not be discussed or presented in this chapter. However, these modules are provided in the directory for this chapter on the CD-ROM accompanying the book.
The code for this chapter is provided in a zip file named CODE-8.ZIP. It should extracted into its own directory - Craig
This section presents the code for each application used in the WebGuest site. The first application, entry.exe, is by far the simplest. The most complicated is search.exe, which is where the data provided on the search page is parsed into a SQL statement that is used to produce a results page. The other applications, gen-reg.exe and gen-list.exe, are middle-of-the-road and don't require a lot of explanation if you understand the concepts presented in Chapters 6 and 7.
You must add a reference to the "Data Access Objects" library to all the Visual Basic projects created in this chapter. If the WebGuest database is created using the tools that came with Visual Basic 4 or with Access 95, add the "Microsoft DAO 3.0 Object Library" entry on the VB References dialog. If the database was created using Access 2.x or any of the tools that came with VB3, add the "Microsoft DAO 2.5/3.0 Compatibility Library" reference.
The database.bas module that is added to each of the projects in the WebGuest site contains a couple of basic database functions. These functions are chknull() and tExecAndAnswer(). The code for database.bas is presented in Listing 8.2. Either copy this module from the CD-ROM or create it using a text editor or Visual Basic. Save it in the directory you'll be using for the code of this chapter.
The function chknull() is used to allow direct string manipulations on database fields that may return NULL values. Because none of the fields in the Guests table are required, it is quite likely that some will return NULL. If you try to use a NULL value as a parameter for some of the Visual Basic functions, a run-time error occurs (Invalid use of NULL). The chknull() function provides a way to avoid checking the state (that is, whether or not it contains NULL) of the field every time you need to use the field.
chknull() has two parameters: a database field and a string. The string parameter is returned as the function result if the field passed to the function evaluates to a NULL value. Otherwise, the field's value is returned. As you can see from Listing 8.2, the code is not complicated. The IsNull() function is used in an If...Then...Else construct to check whether or not the field is NULL. If it is NULL, the string parameter is returned. If it's not NULL, the field is returned. Because the returned value is a string, you must use Val() or some other numeric conversion function if the field is a numeric type field.
The second function presented in Listing 8.2 is tExecAndAnswer(). This function takes a SQL statement and an open database variable as its parameters. The pStatus parameter used as an output parameter. It returns the status of the result set. The SQL statement should be written so that it produces a single row, single column result set. An example of such a SQL statement is
Select Count(*) from Guests where State = 'VA'
This returns a count of the number of records in the Guests table that have VA as the value of their State field. The value of the first field of the first row of the result set is returned as a string to the code that called tExecAndAnswer().
The tExecAndAnswer() function saves you the trouble of creating a recordset from the SQL statement. The function defines a local recordset variable named sn. The pStatus parameter is set to DB_STATUS_OK. Then the snapshot created by the lsql$ parameter is opened using OpenRecordset().
If an error occurs opening the snapshot or if snapshot contains no records, the pStatus return parameter is set to DB_STATUS_NORESULTS, and the function returns an empty string. Otherwise, the value of the first field is passed along with an empty string to chknull(). The return value from chknull() is used as the return value, and the function exits.
Listing 8.2. The code for database.bas.
Attribute VB_Name = "DBRoutines" Global Const DB_STATUS_OK = 0 Global Const DB_STATUS_NORESULTS = 1 Public Function tExecAndAnswer(lsql$, db As Database, _ pStatus As Integer) As String Dim sn As Recordset pStatus = DB_STATUS_OK on error resume next Set sn = db.OpenRecordset(lsql$, dbOpenSnapshot) if err then pStatus = DB_STATUS_NORESULTS tExecAndAnswer = "" exit function end if If sn.RecordCount = 0 Then pStatus = DB_STATUS_NORESULTS tExecAndAnswer = "" Else tExecAndAnswer = chknull(sn(0), "") End If End Function Public Function chknull (pfld as Field, ptNullDef as String) as String If IsNull(pfld) Then chknull = ptNullDef Else chknull = pfld endif End Function
The initial page of the WebGuest site is generated by an application named entry.exe. This section covers the code used to create this page. The code that is added to the existing code base is minimal. Most of the grunt work is done by the routines developed in Chapter 7 that are contained in cgi32.bas.
Create a new project in Visual Basic. Add the funcs.bas and cgi32.bas modules from Chapter 7, the database.bas module developed in Listing 8.2, and a new module. Save this new module as entry.bas. Next, create a new procedure named main within entry.bas. This should be assigned as the project's Startup Form. The code for Sub Main is provided in Listing 8.3.
Listing 8.3. The code of Sub Main in entry.bas.
Public Sub main() On Error GoTo FormError Dim lsql$ If InStr(Command$, " ") Then guCGIData.ProfileFile = Left$(Command$, InStr(Command$, " ") - 1) Else guCGIData.ProfileFile = Command$ End If If LoadCGIData() = 0 Then 'if we couldn't load all the CGI data, 'could we at least open the Output File? If guCGIData.OutputFileHandle <> 0 Then 'yes - call ErrorHandler to handle this Call ErrorHandler(-1, "Error loading CGI Data File") Else 'no - forget about anything else! End End If End If 'open the database Dim db As Database Set db = OpenDatabase("d:\website\cgi-win\webguest.mdb") OutputString "Content-Type: text/html" OutputString "" OutputString "<html><head><title>WebGuest Home Page</title>" OutputString "</head><body><H1>WebGuest Home Page</H1>" OutputString "<HR ALIGN=""CENTER"">" 'now output the record count from the database: lsql$ = "Select Count(*) from Guests" OutputString "There are " & Trim$(tExecAndAnswer(lsql$, db, lStatus%)) OutputString " people registered in our database.<P>" 'set up the links to the other WebGuest pages: OutputString "<A HREF=""/cgi-win/gen-reg.exe?Register"">Register as " OutputString "a WebGuest user</A><p>" OutputString "<A HREF=""/wg-list.htm"">View Available WebGuest " OutputString "Listings</A><p>" OutputString "<A HREF=""/cgi-win/gen-reg.exe?Search"">Search the " OutputString " database</A><p><HR ALIGN=""CENTER"">" OutputString "<CENTER>WebGuest<br>Copyright Craig Eddy, 1996</CENTER>" OutputString "</body></html>" Close #guCGIData.OutputFileHandle db.Close End FormError: Call ErrorHandler(Err, Error$) End Sub
The first part of this procedure is the standard Win/CGI startup code that was developed in Chapter 7. The code first obtains the name of the CGI data file, then attempts to load the data that the server placed there. If the attempt to load the data fails (LoadCGIData() = 0), the procedure attempts to create some sort of output file to be returned to the user. This section of code is repeated in all the applications for the WebGuest site.
If the CGI data is successfully read from the data file, Sub Main continues by creating a database variable. The database is then opened, using the location of the Guests database created earlier in this chapter. As I stated earlier, I have hard-coded the path to the database for simplicity. The path could be stored in the system registry or you could use an ODBC data set name that points to the database.
The remainder of the procedure creates the HTML that will become the entry page. The OutputString procedure from cgi32.bas is used to write the HTML to the output file that the server returns to the user's Web browser.
The first use of OutputString informs the HTTP server that the application is returning HTML formatted text. The next four calls to OutputString produce the beginnings of the HTML page. The following two calls to OutputString are where the record count is displayed. The tExecAndAnswer() function is used to return this record count.
The next three OutputString calls set up the links to the other pages in the WebGuest site. The first creates a link to the Registration page. This page is created by the gen-reg.exe application when the query string passed in the URL is "Register". The link's HREF element specifies the location of this application within the server's path space. For my server, the Win/CGI applications are located in the /cgi-win path. The physical location of this directory is set up using the server's administration tools.
Note the use of a pair of quote characters. It is good practice to enclose the textual elements of an HTML tag (such as the text after the equal sign in the HREF element) within double quotes. Because VB uses the double quote as the string delimiter, you must use a pair of double quotes to output a single double quote character. This will appear quite often within your CGI applications that output HTML formatted text.
The next OutputString call creates the link to the static WebGuest listing page, wg-list.htm. The last of these three calls to OutputString creates the link to the Search WebGuest page. This page is also created by gen-reg.exe, but the query string used is "Search".
Finally, the footer information for the page is written to the output file. Then the output file and the database are closed and the application ends. When a CGI application ends, control is returned to the HTTP server, which examines the output file and returns an HTTP response message to the user's Web browser, completing the HTTP transaction.
Now that the code has been entered, save the project and compile it into executable format. Name the executable file entry.exe. Save it in the project directory for now.
The WebGuest site uses a CGI program to create the HTML pages used to register at and search the WebGuest site. This program, gen-reg.exe, is very similar to the previous application. It produces an HTML page that contains an HTML form. The form is where data is entered by the user, either for registering information or for specifying search criteria.
To get started with gen-reg.exe, create a new project in Visual Basic. Add the funcs.bas, cgi32.bas, and database.bas modules and a new module. Save this new module as gen-reg.bas. Next, create a new procedure named main within gen-reg.bas. This should be assigned as the project's Startup Form. The code for Sub Main is shown in Listing 8.4.
Listing 8.4. The code of Sub Main in gen-reg.bas.
Public Sub main() On Error GoTo FormError If InStr(Command$, " ") Then guCGIData.ProfileFile = Left$(Command$, InStr(Command$, " ") - 1) Else guCGIData.ProfileFile = Command$ End If If LoadCGIData() = 0 Then 'if we couldn't load all the CGI data, 'could we at least open the Output File? If guCGIData.OutputFileHandle <> 0 Then 'yes - call ErrorHandler to handle this Call ErrorHandler(-1, "Error loading CGI Data File") Else 'no - forget about anything else! End End If End If 'open the database Dim db As Database Dim picturesDS As Recordset Set db = OpenDatabase("d:\website\cgi-win\webguest.mdb") Set picturesDS = db.OpenRecordset("Pictures", dbOpenSnapshot) 'open the static text file Dim f% f% = FreeFile Open "d:\website\cgi-win\gen-reg.txt" For Binary Access Read As #f% fstr$ = Space$(FileLen("d:\website\cgi-win\gen-reg.txt")) 'read the text into the fstr$ variable Get #f%, , fstr$ 'if this is a search, fix some of the default HTML If UCase$(guCGIData.QueryString) = "SEARCH" Then 'the form should launch search.exe fstr$ = tReplaceChars(fstr$, "register.exe", "search.exe") 'fix some text to make sense for a search form fstr$ = tReplaceChars(fstr$, "WebGuest Registration", "WebGuest Search") fstr$ = tReplaceChars(fstr$, "register at", "search") End If OutputString "Content-Type: text/html" OutputString "" 'output the contents of the file: OutputString fstr$ 'if there are pictures, add them to the form: If picturesDS.RecordCount Then ' make a label depending on the mode we're in If UCase$(guCGIData.QueryString) = "SEARCH" Then OutputString "<pre>Select a picture to search for:</pre>" Else OutputString "<pre>Select a picture to represent yourself " OutputString "in our directory:</pre>" End If OutputString "<table>" 'Output the first row OutputString "<tr><td align=right><input type=radio " 'it defaults to checked if this is to be a registration page If UCase$(guCGIData.QueryString) <> "SEARCH" Then 'use the "checked" element to select the radio button OutputString "checked " End If 'the group name for these buttons is "Picture" ' the value for each button is the PictureID field OutputString "name=""Picture"" " OutputString "value=""" & picturesDS!PictureID & """></td>" 'display the specified picture OutputString "<td width=50%><img src=""" & picturesDS!pathname OutputString """ align=middle width=40 height=38> </td></tr>" picturesDS.MoveNext While Not picturesDS.EOF ' repeat for each additional picture OutputString "<tr><td align=right><input type=radio " OutputString "name=""Picture"" " OutputString "value=""" & picturesDS!PictureID & """></td>" OutputString "<td width=50%><img src=""" & picturesDS!pathname OutputString """ align=middle width=40 height=38> </td></tr>" picturesDS.MoveNext Wend OutputString "</table>" End If 'create and label the form's Submit button If UCase$(guCGIData.QueryString) = "SEARCH" Then OutputString "<p><input type=submit value=""Search"">" Else OutputString "<p><input type=submit value=""Register""> " End If 'create and label the form's Reset button OutputString "<input type=reset value=""Reset Form""> </p>" 'finish up OutputString "</form><hr></h5>Last revised: 4/20/96</h5>" OutputString "</body></html>" OutputString "" Close #guCGIData.OutputFileHandle picturesDS.Close db.Close End FormError: Call ErrorHandler(Err, Error$) End Sub
The procedure begins just like the Sub Main for entry.exe, with the loading of the CGI data file. After the CGI data is successfully loaded, the code opens the WebGuest database and creates a snapshot recordset. The Pictures table is the source for the recordset. If an error occurs while trying to open the database or recordset, the On Error Goto FormError at the start of the procedure causes a jump to the FormError label. This calls a routine in cgi32.bas that attempts to output the error message to the user's Web browser.
After the database has been successfully opened, the procedure retrieves some text from a static file located in the cgi-win directory. This file, named gen-reg.txt, contains most of the HTML that creates the search and registration forms. The contents of this file are shown in Listing 8.5.
Listing 8.5. The HTML contents of gen-reg.txt.
<html> <head> <title>WebGuest Registration</title> </head> <body bgcolor="#FFFFFF"> <h1>WebGuest Registration</h1> <hr> <p>Use this form to register at the WebGuest site.</p> <form action="/cgi-win/register.exe" method="POST"> <p>Please provide the following contact information: </p> <pre><em> First name </em><input type=text size=25 maxlength=256 name="FirstName"> <em> Last name </em><input type=text size=25 maxlength=256 name="LastName"> <em> Title </em><input type=text size=35 maxlength=256 name="Title"> <em> Organization </em><input type=text size=35 maxlength=256 name="Organization"> <em> Street address </em><input type=text size=35 maxlength=256 name="StreetAddress"> <em> Address (cont.) </em><input type=text size=35 maxlength=256 name="Address2"> <em> City </em><input type=text size=35 maxlength=256 name="City"> <em> State/Province </em><input type=text size=35 maxlength=256 name="State"> <em> Zip/Postal code </em><input type=text size=12 maxlength=12 name="ZipCode"> <em> Country </em><input type=text size=25 maxlength=256 name="Country"> <em> Work Phone </em><input type=text size=25 maxlength=25 name="WorkPhone"> <em> FAX </em><input type=text size=25 maxlength=25 name="FAX"> <em> E-mail </em><input type=text size=25 maxlength=256 name="Email"> <em> URL </em><input type=text size=25 maxlength=50 name="URL"> <em>Operating System </em> <input type="radio" name="OS" value="Windows 95">Windows 95 <input type="radio" name="OS" value="Windows 3.x">Windows 3.x <input type="radio" name="OS" value="Windows NT">Windows NT <input type="radio" name="OS" value="Unix">Unix <input type="radio" name="OS" value="Macintosh">Macintosh <input type="radio" name="OS" value="Other">Other </pre>
This file is read into a string variable named fstr$. As you can see from Listing 8.5, the HTML form contains an entry for just about every field in the Guests table. The other fields are filled in with data received from the CGI data file when the registration program is executed. The HTML in Listing 8.5 is set up to produce the registration page, not the search page. The gen-reg application will fix that in the next section of the code.
Appendix A contains an HTML reference guide. You may wish to refer to the section in that appendix that covers HTML forms.
After the file is read into fstr$, the procedure checks the CGI Query String parameter (guCGIData.QueryString) to determine whether it is producing a registration page or a search page. If it is producing a search page, the tReplaceChars() function (contained in funcs.bas) is called to replace the application to be executed with search.exe and to replace some of the existing text with text that is more meaningful for a search page.
Next, it's time to start writing the HTML output page. The procedure starts as entry.exe did, informing the server that it's returning HTML formatted text. Then, in one fell swoop, the entire contents of fstr$ are written to the output file (OutputString fstr$).
The procedure finishes up its output by cycling through the available records in the Pictures table. If there are some records in the table (picturesDS.RecordCount > 0), the procedure outputs them into an HTML table. The table contains two columns: one for a radio button which specifies which picture to use and one to display the picture. There is one row in the HTML table for each row in the recordset table.
When using the value element of an HTML form's input tag (as was done for the picture radio buttons), make sure the data entered as the value is contained on a single line in the output file. The HTML form will submit the value as-is. This means that if a carriage return exists within the value element's specified value, that carriage return will be sent as part of the form data. This will cause the application that reads the data to incorrectly interpret the data.
If the program is producing a registration page, the first picture found in the recordset is selected by default. This is done by using the checked element within an HTML radio button input type. The remainder of the code finishes the HTML for the current row and then cycles to the next record in the pictureDS recordset, producing an unselected radio button/picture row for each record in the recordset. When all the records have been processed, the </TABLE> tag is output to end the table definition.
The procedure then creates the two buttons on the form. One button is a Submit button that causes the user's Web browser to POST the data entered to the appropriate CGI application. The other button is a Reset button which, when clicked, clears the HTML form and restores any default settings specified in the HTML document (such as the default selected picture).
Finally, the procedure outputs the last of the HTML and closes the recordset, database, and output file. It then ends the application, returning control to the Web server. The server passes the HTML document just produced by the application back to the user's Web browser. If the user's browser is not capable of displaying HTML forms, the text will seem meaningless. However, most browsers, especially Windows-based browsers, are capable of displaying forms.
After the code is entered, save the project and compile the executable. Name the executable gen-reg.exe and leave it in the project directory for now.
The registration application takes the data that the user enters into the HTML form created by gen-reg.exe and stores it into the WebGuest database. The program then produces an output page that thanks the user for registering and provides them with a registration number.
This registration number could be used for any number of purposes. At my company we have an HTML form that people who want demo copies of our programs must fill out. When they have supplied the required information and successfully registered in our demo request database, they are provided a registration number. This number can be used to return later to the site to download the demos again without having to enter data again. It is also used for customer support purposes. Because the customers have entered information into our database that is useful in the customer support world, the customer support team has access to the data. It's all tied together using the assigned registration number.
Start a new project, adding the funcs.bas, cgi32.bas, and database.bas modules. Insert a new module and save it as register.bas. Insert a procedure named main into the new module. The code for Sub Main is given in Listing 8.6.
Listing 8.6. The code of register.bas's Sub Main.
Public Sub main() On Error GoTo FormError If InStr(Command$, " ") Then guCGIData.ProfileFile = Left$(Command$, InStr(Command$, " ") - 1) Else guCGIData.ProfileFile = Command$ End If If LoadCGIData() = 0 Then 'if we couldn't load all the CGI data, 'could we at least open the Output File? If guCGIData.OutputFileHandle <> 0 Then 'yes - call ErrorHandler to handle this Call ErrorHandler(-1, "Error loading CGI Data File") Else 'no - forget about anything else! End End If End If 'open the database Dim db As Database Dim guestDS As Recordset Set db = OpenDatabase("d:\website\cgi-win\webguest.mdb") 'open a dynaset to add the information to Set guestDS = db.OpenRecordset("guests", dbOpenDynaset) 'add a new record guestDS.AddNew 'fill the data using the CGI function GetFieldValue() guestDS!FirstName = GetFieldValue("FirstName") guestDS!LastName = GetFieldValue("LastName") guestDS!Title = GetFieldValue("Title") guestDS!Organization = GetFieldValue("Organization") guestDS!StreetAddress = GetFieldValue("StreetAddress") guestDS!Address2 = GetFieldValue("Address2") guestDS!City = GetFieldValue("City") guestDS!State = GetFieldValue("State") guestDS!ZipCode = GetFieldValue("ZipCode") guestDS!Country = GetFieldValue("Country") guestDS!WorkPhone = GetFieldValue("WorkPhone") guestDS!Fax = GetFieldValue("Fax") guestDS!Email = GetFieldValue("Email") guestDS!URL = GetFieldValue("URL") guestDS!OS = GetFieldValue("OS") 'verify the PictureID provided by the form: lsql$ = "Select Count(*) from Pictures where PictureID = '" lsql$ = lsql$ & GetFieldValue("Picture") & "'" If Val(tExecAndAnswer(lsql$, db, lStatus%)) = 0 Then 'the PictureID specified does not exist! lStatus% = 1 Else 'the PictureID is valid, save to the dynaset guestDS!PictureID = GetFieldValue("Picture") lStatus% = 0 End If 'save the CGI data not provided on the form With guCGIData guestDS!UserAgent = .UserAgent guestDS!RemoteIP = .RemoteAddress guestDS!RemoteHost = .RemoteHost End With 'update the record guestDS.Update 'return to the record just added guestDS.Bookmark = guestDS.LastModified 'produce the output page OutputString "Content-Type: text/html" OutputString "" OutputString "<html><head><title>Thanks for Registering</title>" OutputString "</head><body><H1>Thanks for Registering</H1>" OutputString "Your registration number is " OutputString Trim$(Str$(guestDS!GuestID)) 'was the PictureID valid? If lStatus% = 1 Then 'No, inform the user OutputString "<br><hr ALIGN=""CENTER"">The selected picture " OutputString "was not found in the database!" End If 'provide a link back to the entry page OutputString "<hr><A HREF=""/cgi-win/entry.exe"">WebGuest Home</A>" OutputString "</body></html>" 'close down: Close #guCGIData.OutputFileHandle guestDS.Close db.Close End FormError: Call ErrorHandler(Err, Error$) End Sub
It should come as no surprise by now that the procedure begins exactly like the other two Sub Mains I've presented. The CGI data file is read, and the WebGuest database is opened. The procedure then opens a dynaset on the Guests table. This dynaset is where a new WebGuest entry is created and the form data is written.
After the dynaset is opened, a new record is added using the AddNew data access method. Then each field in the table that has a corresponding field on the registration form is filled in with data from the HTML form. The GetFieldValue() function from cgi32.bas is used to retrieve the values entered by the user. If the user left the form field empty, GetFieldValue() returns an empty string.
After all the form's textbox fields are processed, it's time to verify the chosen picture. Although the registration form is generated using the Pictures table to supply the PictureIDs used, there is no way of knowing if the user is filling in a valid form. The registration form could have been generated two months ago, left sitting in the user's Web browser, and the user is just now filling in the data. Perhaps the picture database has changed in that time. It's a far-fetched scenario, but it certainly can happen. So, register.exe plays it safe and verifies the supplied PictureID by performing a SELECT Count(*) from Pictures WHERE PictureID = query, using the Picture field the HTML form supplied as the criteria for the WHERE clause. If a count of zero is returned, the PictureID supplies is invalid. Otherwise, the PictureID is good, so the data is placed in the dynaset's PictureID field.
Next, some of the information supplied by the Win/CGI interface is added to the record. These fields include UserAgent, RemoteIP, and RemoteHost. All these are supplied as elements of the guCGIData global variable that contains the data read from the CGI data file. The fields are useful to the WebGuest database because they provide some demographic information about the user's location (thanks to RemoteIP and RemoteHost) and his or her Web browser (via the UserAgent element).
After the data is appended to the dynaset, the Update method is invoked to save the new record. Because Update moves the dynaset's record pointer and you need to access the GuestID field of the record just added, it is necessary to move the record pointer back to the record just added. Fortunately, the Data Access Objects provide a recordset property named LastModified which contains a bookmark to the last record modified. By setting the Bookmark property of a recordset to LastModified, you'll return to the record just added or modified. That's how register.exe gets back to the record.
Finally, it's time to produce the HTML output. A nice "Thank You" message is printed for the user. Then the GuestID field of the dynaset is displayed as the user's registration number. If the PictureID supplied is invalid, a message to that effect is printed next. A link back to the entry page is provided next. And then the last of the HTML is output, and the application closes down in the normal Win/CGI fashion.
After you enter the code, save the project and compile the executable. Name the executable register.exe and leave it in the project directory for now.
The search application takes data generated by the HTML search form and performs a search on the Guests table. Any matching records found are displayed in a list formatted as Last Name, First Name. The items in the list are hyperlinks that cause WebGuest to display the complete entry for that record. A sample page showing the results of a search is shown in Figure 8.7.
Figure 8.7. A sample search results page.
To start the project, open a new project in VB. Add the funcs.bas, cgi32.bas, and database.bas modules. Insert a new module and save it as search.bas. Insert a procedure named main into the new module. The code for Sub Main is given in Listing 8.7.
The procedure has the same beginnings as the rest of the applications. After the CGI data is successfully obtained, the code outputs the start of the HTML output page.
Listing 8.7. The code of Sub Main from search.bas.
Public Sub main() On Error GoTo FormError If InStr(Command$, " ") Then guCGIData.ProfileFile = Left$(Command$, InStr(Command$, " ") - 1) Else guCGIData.ProfileFile = Command$ End If If LoadCGIData() = 0 Then 'if we couldn't load all the CGI data, 'could we at least open the Output File? If guCGIData.OutputFileHandle <> 0 Then 'yes - call ErrorHandler to handle this Call ErrorHandler(-1, "Error loading CGI Data File") Else 'no - forget about anything else! End End If End If 'start the output OutputString "Content-Type: text/html" OutputString "" OutputString "<html><head><title>WebGuest Search Results</title>" OutputString "</head><body><H1>Search Results</H1>" 'check to see if the user filled in anything: If guCGIData.FormFieldCount = 0 Then 'no fields were filled in: OutputString "<h2>No search fields were found!</h2>" OutputString "</body></html>" Close #guCGIData.OutputFileHandle End End If 'create the SQL statement: lsql$ = "Select LastName, FirstName, GuestID from Guests where " 'create the WHERE clause: lwhere$ = "" For i% = 1 To guCGIData.FormFieldCount 'the form field corresponding to PictureID is named Picture: If guCGIData.FormSmallFields(i%).key = "Picture" Then lField$ = "PictureID" Else lField$ = guCGIData.FormSmallFields(i%).key End If 'get the value entered on the form lValue$ = Trim$(guCGIData.FormSmallFields(i%).value) 'make sure there is actually data in the field: If Len(lValue$) Then If Len(lwhere$) Then lwhere$ = lwhere$ & " and " End If lwhere$ = lwhere$ & lField$ 'replace any single quotes with double-quotes: lValue$ = tReplaceChars(lValue$, "'", """") 'check for a wildcard search If Right$(lValue$, 1) = "*" Then 'wildcard searches use LIKE lwhere$ = lwhere$ & " LIKE " Else 'standard search, use equality lwhere$ = lwhere$ & " = " End If 'recall that all database fields are TEXT: lwhere$ = lwhere$ & "'" & lValue$ & "'" End If Next 'check to see if lwhere$ is empty If Len(Trim$(lwhere$)) = 0 Then 'no fields were filled in: OutputString "<h2>No search fields were found!</h2>" OutputString "</body></html>" Close #guCGIData.OutputFileHandle End End If 'append the WHERE clause to the SQL statement: lsql$ = lsql$ & lwhere$ 'open the database Dim db As Database Dim guestDS As Recordset Set db = OpenDatabase("d:\website\cgi-win\webguest.mdb") 'open a snapshot based on the SQL created above Set guestDS = db.OpenRecordset(lsql$, dbOpenSnapshot) 'create the output If guestDS.RecordCount Then 'there were records found! OutputString "<h2>The following records match the " OutputString "criteria you entered:</h2>" ' output in list format: While Not (guestDS.EOF) OutputString "<a HREF=""/cgi-win/results.exe?" OutputString guestDS!GuestID & """>" If Not (IsNull(guestDS!LastName)) Then OutputString guestDS!LastName If Not (IsNull(guestDS!FirstName)) Then OutputString ", " End If OutputString chknull(guestDS!FirstName, "") OutputString "</a><br>" guestDS.MoveNext Wend Else 'no records found: OutputString "<h2>No records match the criteria you entered.</h2>" OutputString "The SQL string used was:<br>" & lsql$ End If 'provide links to search page and home page OutputString "<hr>Back to the <a HREF=""/cgi-win/gen-reg.exe?Search"">" OutputString "Search Page</a><br>" OutputString "<hr>Back to the <a HREF=""/cgi-win/entry.exe"">" OutputString "WebGuest Home Page</a><br>" OutputString "</body></html>" 'close down: Close #guCGIData.OutputFileHandle guestDS.Close db.Close End FormError: Call ErrorHandler(Err, Error$) End Sub
The line If guCGIData.FormFieldCount = 0 Then tests to see whether the user left the search form blank. If the form has been left blank, there should be no fields in the CGI data file. However, some browsers will still send the empty fields anyway, so later the code will check each field to make sure it has data. If the FormFieldCount is zero, a suitable error message is output to the page and the program ends.
The next task is to create the SQL statement that actually performs the search. The WHERE clause of the SQL statement is generated separately so you can verify that there are really criteria to search for. The code loops through each of the CGI fields that it finds. The FormFieldCount element of the guCGIData variable provides a count of the number of fields that were found. The code will loop once for each field (For i% = 1 to guCGIData.FormFieldCount).
Once inside the loop, the first order of business is to fix an anomaly of the HTML search form. The code that generates the WHERE clause uses the HTML form's field names as the database field names. The radio buttons for the pictures are in a group named Picture. This means that the field name passed in the CGI data file will be Picture. The code checks to see if the field currently being processed is named Picture. If it is, the value of the lField$ variable is set to PictureID. Otherwise, lField$ is set to guCGIData.FormSmallFields(i%).key, which is the field name of the current form field.
Next, the value specified for the field is assigned to the variable lValue$. Then lValue$ is checked to make sure it contains actual text. If so, the code appends to the WHERE clause. Because there may be multiple fields filled in on the search form, the code appends an and to the WHERE clause if it already contains some criteria. The field name is then appended to the WHERE clause. If the criteria specified in the field value contains any single quote characters, they are replaced with double quotes. This is necessary because the SQL string being generated uses single quotes to delimit the values being searched.
The next statement, If Right$(lValue$, 1) = "*" Then, checks to see whether the user has specified a wildcard in the field's value. The wildcard is used to search on a field that begins with the text entered in the field. This uses the LIKE comparison operator instead of the equal sign to do the comparison. Finally, the field's value is appended to the WHERE clause and delimited with single quotes because all the Guests table's fields (except GuestID, which isn't searchable anyway) are text fields. The code then loops to the next available field.
After the WHERE clause is generated, it is checked to make sure it contains some characters. If it is empty, the user left the HTML form blank or filled with spaces. In these cases, the program prints an error message to the output page and ends.
If the WHERE clause contains text, it is appended to the SQL statement. The WebGuest database is opened and a snapshot is created from the SQL statement.
The procedure then checks the RecordCount property of the snapshot. If it is greater than zero, the records of the snapshot are output in a list. Each item in the list displays the name of the registered user and provides a link to that user's results page. This link uses the results.exe application discussed shortly.
If no records match the criteria specified, the program outputs a message to that effect and provides the SQL statement used in the query. Because most of the field names are easy to relate to the form, the user shouldn't have much trouble understanding the SQL statement. I put it in mainly for debugging purposes, so you can remove it if you wish.
Finally, the program outputs links back to the search page and the WebGuest home page and closes the output file, the snapshot, and the database.
Save the project and compile the code into an executable named search.exe.
The WebGuest List pages are used to list all the records registered in the database, grouped by a specific field. The HTML page wg-list.htm which is in the server's document root directory contains four links. These links all execute the gen-list.exe application, but with a different query string. The application then examines the query string to determine which field in the Guests table to use in grouping the records. The output page, an example of which is shown in Figure 8.5, contains each grouping with the available records in that group listed below the group's heading. Each record listing is a hyperlink to the record's full WebGuest entry.
To begin, open a new project in Visual Basic. Add our standard funcs.bas, cgi32.bas, and database.bas modules. Insert a new module and save it as gen-list.bas. Insert a procedure named main into the new module. The code for the Sub Main procedure is given in Listing 8.8.
Listing 8.8. The code of Sub Main from gen-list.bas.
Public Sub main() On Error GoTo FormError If InStr(Command$, " ") Then guCGIData.ProfileFile = Left$(Command$, InStr(Command$, " ") - 1) Else guCGIData.ProfileFile = Command$ End If If LoadCGIData() = 0 Then 'if we couldn't load all the CGI data, 'could we at least open the Output File? If guCGIData.OutputFileHandle <> 0 Then 'yes - call ErrorHandler to handle this Call ErrorHandler(-1, "Error loading CGI Data File") Else 'no - forget about anything else! End End If End If 'open the database Dim db As Database Dim listDS As Recordset Dim guestDS As Recordset Set db = OpenDatabase("d:\website\cgi-win\webguest.mdb") Select Case UCase$(guCGIData.QueryString) Case "NAME" ltType$ = "names" ltNice$ = "Last Name Starts With" lsql$ = "Select Count(*), Left(LastName,1) from Guests " lsql$ = lsq$ & "Group By Left(LastName,1) order by Left(LastName,1)" lsql2$ = "Select LastName, FirstName, GuestID from Guests " lsql2$ = lsql2$ & "where Left(LastName,1) = '" Case "STATE" ltType$ = "states" ltNice$ = "State is" lsql$ = "Select Count(*), State from Guests Group By State " lsql$ = lsql$ & "order by State" lsql2$ = "Select LastName, FirstName, GuestID from Guests " lsql2$ = lsql2$ & "where State = '" Case "HOST" ltType$ = "remote hosts" ltNice$ = "Remote Host is" lsql$ = "Select Count(*), RemoteHost from Guests Group By " lsql$ = lsql$ & "RemoteHost order by RemoteHost" lsql2$ = "Select LastName, FirstName, GuestID from Guests " lsql2$ = lsql2$ & "where RemoteHost = '" Case "AGENT" ltType$ = "user agents" ltNice$ = "User Agent is" lsql$ = "Select Count(*), UserAgent from Guests Group By " lsql$ = lsql$ & "UserAgent order by UserAgent" lsql2$ = "Select LastName, FirstName, GuestID from Guests " lsql2$ = lsql2$ & "where UserAgent = '" Case Else OutputString "Content-Type: text/html" OutputString "" OutputString "<html><head><title>Invalid Query String</title>" OutputString "</head><body><H1>Invalid Query String</H1>" OutputString "An invalid query string was passed to gen-list.exe" OutputString "</body></html>" OutputString "" Close #guCGIData.OutputFileHandle End End Select Set listDS = db.OpenRecordset(lsql$, dbOpenSnapshot) OutputString "Content-Type: text/html" OutputString "" OutputString "<html><head><title>List Results</title>" OutputString "</head><body><H1>List Results</H1>" If listDS.RecordCount Then OutputString "The following " & ltType$ & " are available:<p><DL>" While Not (listDS.EOF) OutputString "<DT>" OutputString ltNice$ & " '" & chknull(listDS(1), "?") & "'" OutputString " (" & Trim$(chknull(listDS(0), "?")) OutputString " records)</DT>" 'get each of the records that match the GROUP BY On Error Resume Next Set guestDS = db.OpenRecordset(lsql2$ & listDS(1) & "'", _ dbOpenSnapshot) If Err = 0 Then While Not (guestDS.EOF) OutputString "<DD><a HREF=""/cgi-win/results.exe?" OutputString guestDS!GuestID & """>" If Not (IsNull(guestDS!LastName)) Then OutputString guestDS!LastName If Not (IsNull(guestDS!FirstName)) Then OutputString ", " End If End If OutputString chknull(guestDS!FirstName, "") OutputString "</a></DD>" guestDS.MoveNext Wend Else OutputString "<DD>Error retrieving the records " OutputString "for this item!</DD>" End If OutputString "<p>" listDS.MoveNext Wend OutputString "</DL>" Else OutputString "No " & ltType$ & " available to list" End If OutputString "</body></html>" guestDS.Close listDS.Close db.CloseClose #guCGIData.OutputFileHandle End FormError: Call ErrorHandler(Err, Error$) End Sub
After the standard CGI application code, the program defines two recordset variables and opens the database. The Select Case Ucase$(guCGIData.QueryString) code utilizes the CGI Query String parameter to decide where to go next. The CGI Query String parameter is the portion of the URL that appears after the question mark. If the user clicks on the List by Last Name link shown in Figure 8.5, the user's Web browser attempts to retrieve the URI /cgi-win/gen-list.exe?NAME. The Query String in this case will be NAME.
The program generates several string variables whose value depends on the CGI Query String parameter. The strings are used when opening the snapshots and for labels. If an invalid Query String parameter has been found, the program falls into the Case Else portion of the Select Case, displays an appropriate error message, and exits.
After the string variables are defined, the first snapshot is opened. This snapshot, listDS, contains the data for the group headers. If the snapshot contains no records, a suitable message is output to the HTML page. If there are records, the program loops through each record in the snapshot. It first outputs a line of HTML that displays the group headers. This is done starting with the line of code that reads OutputString "<DT>" and continuing for two more lines. The "<DT>" tag begins a special type of HTML tag known as a definition. The "<DT>" tag surrounds the term to be defined. The "<DD>" tag surrounds the definition of the term. Web browsers display the definition slightly indented on the next line after the term. Although the application is not using this to display a definition, the format is perfect for displaying grouped information.
After the group header is written, a snapshot containing all the WebGuest entries that belong to the current group is opened. Each resulting record is output using the same format as the search results page: The name is listed and is a hyperlink to the full entry for that record.
After all the records for the current group are output, the program moves the grouping snapshot to its next record and repeats the process. After all groups have been handled, the program finishes the HTML output, closes the database, the snapshots, and the output file, and ends.
Save the project and compile the executable as gen-list.exe.
The last piece of the WebGuest site is where the final results of any searching and listing are displayed. The results.exe application creates an HTML file for a specific record in the Guests table. The query string provided in the URI for this application specifies the GuestID of the record to be displayed. The record is displayed in an HTML table in order to provide some measure of formatting. An example of the results page is shown in Figure 8.6.
To begin, start a new project. Add funcs.bas, cgi32.bas, and database.bas to the project. Insert a new module and save it as results.bas. Insert a procedure named main into the new module. The code for the Sub Main procedure is given in Listing 8.9.
Listing 8.9. The code of Sub Main from results.bas.
Public Sub main() On Error GoTo FormError If InStr(Command$, " ") Then guCGIData.ProfileFile = Left$(Command$, InStr(Command$, " ") - 1) Else guCGIData.ProfileFile = Command$ End If If LoadCGIData() = 0 Then 'if we couldn't load all the CGI data, 'could we at least open the Output File? If guCGIData.OutputFileHandle <> 0 Then 'yes - call ErrorHandler to handle this Call ErrorHandler(-1, "Error loading CGI Data File") Else 'no - forget about anything else! End End If End If 'open the database Dim db As Database Dim guestDS As Recordset Set db = OpenDatabase("d:\website\cgi-win\webguest.mdb") OutputString "Content-Type: text/html" OutputString "" OutputString "<html><head><title>WebGuest Results Page</title>" OutputString "</head><body><H1>WebGuest Results Page</H1>" OutputString "<HR ALIGN=""CENTER"">" lsql$ = "Select * from Guests left join Pictures on Guests.PictureID " lsql$ = lsql$ & "= Pictures.PictureID where GuestID = " lsql$ = lsql$ & Val(guCGIData.QueryString) Set guestDS = db.OpenRecordset(lsql$, dbOpenSnapshot) 'if there is a matching registered user: If guestDS.RecordCount Then 'output the data OutputString "<table>" OutputString "<tr><td ALIGN=""right""><b>Name:</b></td>" OutputString "<td>" & chknull(guestDS!FirstName, "") & " " OutputString chknull(guestDS!LastName, "") & "</td></tr>" OutputString "<tr><td ALIGN=""right""><b>Title:</b></td>" OutputString "<td>" & chknull(guestDS!Title, "") & "</td></tr>" OutputString "<tr><td ALIGN=""right""><b>Organization:</b></td>" OutputString "<td>" & chknull(guestDS!Organization, "") & "</td></tr>" OutputString "<tr><td ALIGN=""right""><b>Street Address:</b></td>" OutputString "<td>" & chknull(guestDS!StreetAddress, "") & "</td></tr>" OutputString "<tr><td ALIGN=""right""><b>Address 2:</b></td>" OutputString "<td>" & chknull(guestDS!Address2, "") & "</td></tr>" OutputString "<tr><td ALIGN=""right"">" OutputString "<b>City, State ZIP:</b></td><td>" If Len(chknull(guestDS!City, "")) Then OutputString chknull(guestDS!City, "") If Len(chknull(guestDS!State, "")) Then OutputString ", " End If OutputString chknull(guestDS!State, "") & " " OutputString chknull(guestDS!ZipCode, "") & "</td></tr>" OutputString "<tr><td ALIGN=""right""><b>Country:</b></td> " OutputString "<td>" & chknull(guestDS!Country, "") & "</td></tr>" OutputString "<tr><td ALIGN=""right""><b>Work Phone:</b></td>" OutputString "<td>" & chknull(guestDS!WorkPhone, "") & "</td></tr>" OutputString "<tr><td ALIGN=""right""><b>Fax:</b></td> " OutputString "<td>" & chknull(guestDS!Fax, "") & "</td></tr>" 'output the EMail address w/ a mailto: directive OutputString "<tr><td ALIGN=""right""><b>Email:</b></td><td> " If Len(chknull(guestDS!Email, "")) Then OutputString "<A HREF=""mailto:" OutputString chknull(guestDS!Email, "") & """>" OutputString chknull(guestDS!Email, "") & "</A><br>" End If OutputString "</td></tr>" 'output the URL as a hyperlink if it exists OutputString "<tr><td ALIGN=""right""><b>URL:</b></td><td> " If Len(chknull(guestDS!URL, "")) Then OutputString "<A HREF=""" & chknull(guestDS!URL, "") OutputString """>" & chknull(guestDS!URL, "") & "</A><br>" End If OutputString "</td></tr>" OutputString "<tr><td ALIGN=""right""><b>Operating System:</b></td> " OutputString "<td>" & chknull(guestDS!OS, "") & "</td></tr>" OutputString "<tr><td ALIGN=""right""><b>Web Browser:</b></td>" OutputString "<td>" & chknull(guestDS!UserAgent, "") & "</td></tr>" OutputString "<tr><td ALIGN=""right""><b>IP Address:</b></td> " OutputString "<td>" & chknull(guestDS!RemoteIP, "") & "</td></tr>" OutputString "<tr><td ALIGN=""right""><b>Host Name:</b></td>" OutputString "<td>" & chknull(guestDS!RemoteHost, "") & "</td></tr>" 'if there is a valid picture, display it: If Len(chknull(guestDS!pathname, "")) Then OutputString "<tr><td ALIGN=""right""><b>Pictoral " OutputString "Representation:</b></td>" OutputString "<td><img align=""absmiddle"" src=""" OutputString guestDS!pathname & """ alt=""WebGuest " OutputString "Picture""></td></tr>" End If OutputString "</table>" Else 'no matching entry, print message OutputString "<H2>No entries matched the GuestID provided!</H2>" End If OutputString "</body></html>" 'close everything guestDS.Close db.Close Close #guCGIData.OutputFileHandle End FormError: Call ErrorHandler(Err, Error$) End Sub
After the CGI data is read and some header information is output to the HTML output file, the SQL statement that retrieves the record is created. The SQL statement is
SELECT * FROM Guests LEFT JOIN Pictures on Guest.PictureID = Pictures.PictureID WHERE GuestID = x
The SQL statement uses a LEFT JOIN on the Guests and Pictures tables to produce the recordset. The LEFT JOIN ensures that a record will be returned, even if there is no PictureID for the record specified by GuestID. The x is replaced with the GuestID specified in the Query String parameter. The recordset is then opened.
If the recordset contains a record, its fields are output in an HTML table format. The table defined in the output consists of two columns and multiple rows. The left column displays a label for the field. It is right-justified. The right column displays the data from the recordset. The Email field is output using a mailto: hyperlink. Clicking on such a link in an e-mail-aware Web browser causes the browser to create a new e-mail message addressed to the mailbox specified in the link. The URL field is also output as a hyperlink, and clicking it causes the Web browser to load the resource specified by the URL. Finally, if a picture is specified for the record, it is displayed using the <IMG> tag.
If no records are found, the application outputs a message to that effect.
After all the HTML has been output, the application closes the snapshot, the database, and the output file, and ends.
Save the project and compile it into an executable named results.exe.
Now that the WebGuest database and applications have been created, it's time to install and test them on your HTTP server. If you haven't already done so, compile each project into executable form, naming them entry.exe, gen-reg.exe, register.exe, search.exe, gen-list.exe, and results.exe. Also, if you haven't created wg-list.htm and gen-reg.txt and placed them in your server's root document directory and Win/CGI directory, respectively, do so now (see Listing 8.1 and Listing 8.5 for the HTML to create these files).
The executable files should be copied into the Windows CGI or executable application directory as set up for your HTTP server.
For the WebSite server (which I used in developing these applications; a demo version is provided on the CD-ROM), the directories for Windows CGI applications are specified on the Mapping tab of the WebSite Server Admin application (see Figure 8.8). The Win/CGI URL Path element should be specified when creating links to these applications either in an HTML page or another CGI application.
Figure 8.8. The WebSite Server Admin's Mapping tab.
For testing purposes, you should also enable CGI's Debug mode. With the WebSite Server Admin tool, this is done by enabling the CGI Execution tracing option on the Logging tab. This causes WebSite to leave the temporary files created during the CGI execution process in the CGI temporary storage directory. If this option is not enabled, the server will delete all these files after returning control to the user's Web browser.
Fortunately, testing the WebGuest application is much easier than coding it. Before you begin to test the WebGuest site, check the following:
After you've checked all these points, launch your Web browser. Enter a URL that points to the entry.exe application. For example, on my local server I have the Win/CGI programs in a logical directory named cgi-win. The URL for entry.exe on my server (if I'm running my Web browser on the same machine) is http://localhost/cgi-win/entry.exe.
The reference to localhost refers to the local machine and is assigned to the loopback IP address (127.0.0.1).
The page should load and display a message saying that there are zero people registered in the database. The page should look like Figure 8.2. If not, return to the entry.vbp project, review the code, and try to determine the problem.
If you have CGI tracing enabled, locate the directory that your server uses to store temporary CGI files (for WebSite, this defaults to a directory named cgi-temp which is a subdirectory of the WebSite install directory). Change the Visual Basic command-line arguments option to point to the newest .INI file in the temporary directory. This will be the file the server created before launching the application. You can view its contents using Notepad. By setting the command-line argument to point to this file, you can step through the application to see just what occurred.
If a run-time error occurs while the CGI program is executing, a special page returns and specifies the error description, error number, and specifics about the execution. This can be handy for tracking down coding errors or database problems.
The trouble shooting tips apply to any CGI program you write. Although the application will eventually run without any human interaction at the server, that doesn't have to be the case while you're writing the application.
From the entry page, click on the Register link. The registration page shown in Figure 8.3 should appear. Enter some information for yourself and click the Submit button. After the application executes, you should see a page thanking you for registering and providing a registration number. This number is the GuestID field assigned to the record you just entered. Return to the registration page (you can use the Back feature of your Web browser to avoid waiting for the gen-reg.exe application to execute again) and enter the information for a few of your friends. Try to enter a variety of data in the Last Name and State fields. Also, if you have several Web browsers on your machine, try using different ones each time. This will give you more varied lists when you list by user agent.
If an error occurs, follow the trouble shooting tips provided in the preceding paragraphs.
After you've entered enough data to satisfy your future curiosity, return to the Entry page. Click on the View Available WebGuest Listings link. The page from Figure 8.4 should appear. If you get a message saying that the resource could not be located, check that the file wg-list.htm exists in the Web server's document root. If the file exists there, check to make sure the link that was clicked on the entry page points to /wg-list.htm. If it doesn't, the problem lies in entry.exe. If the file was found but doesn't appear correctly, check the HTML coding within the file. In particular, look for unmatched quotes and angled brackets. Each < must have a corresponding > in the proper place, or the Web browser will be unable to interpret the document correctly.
After the page loads correctly, click any of the links to see the listing. The page should appear similar to Figure 8.5, depending on the list you chose. Select one of the WebGuest entry links, and the information you entered for that person should appear as in Figure 8.6.
After you've listed to your heart's content, return to the Entry page. Click the Search the Database link. The gen-reg.exe program will produce a search page that should look like Figure 8.9. Enter some text to search for. Use the wildcard character also (the asterisk at the end of a piece of text). Select an operating system if you entered some when creating the entries. Click the Search button. If all goes as planned, a Search Results page similar to Figure 8.7 will appear. Select any of the entries on the list to see the full entry.
Figure 8.9. The WebGuest Search Form page.
I hope you survived the testing process without having to revisit the Visual Basic projects too frequently. Debugging CGI applications is a looping process: load the page, click the link that causes the app to launch, interpret the resulting page, return to the VB project and run interactively using the CGI temporary data files, recompile the executable, and return to the first step. It's a tedious process but if you enjoy programming it can become addictive!
The WebGuest project is just the tip of the iceberg when it comes to Web-based customer information gathering and dissemination. You can take the concepts you learned in this chapter and apply them to just about any application where you'd like to connect a database to your Web server. That is, indeed, the beauty of the Win/CGI interface and the reason that the interface is called a gatewaythese applications truly serve as gateways between your Web server and any system accessible to the machine the Web server runs on.