Part V: Publishing the Database | Programming Active Server Pages (Microsoft Programming Series)

In this part of the exercise, you will publish the Bookstore database by allowing a user to search the database for a book by title, author, or publisher. You will build a form on the home page for searching and publish the results on a separate page.

Step 1

If you don't already have the Bookstore project open, open it now. To start building the form, right-click the file DEFAULT.HTM and choose Open With from the pop-up menu. Open the file in Microsoft FrontPage Editor.

In the table where you inserted HOTLIST.GIF, insert the search image into the top cell of the right-hand column by choosing Image from the Insert menu. In the From Location text box, specify this location:

/bookstore/images/search.gif

To create a bookmark to the new image, select the entire image and choose Bookmark from the Edit menu. Name the new bookmark Find Book. The Agent will use this bookmark later to display the form to the user.

WARNING: When inserting a bookmark for the image, you must be careful to select, or highlight, the entire image. If the image is not completely highlighted, the bookmark menu will be unavailable.

Step 2

In the cell below the search image, build a table to allow searches. Place three text boxes in the cell by choosing Form Field/One-Line Text Box from the Insert menu for each text box. Use text to label the boxes Title, Author, and Publisher, respectively.

To name the controls for the text boxes, right-click each box and choose Form Field Properties from the pop-up menu. In the Text Box Properties dialog box, name the controls txtTitle, txtAuthor, and txtPublisher.

After creating the text boxes, add Submit and Reset buttons to the form by choosing Form Field/Push Button from the Insert menu. Right-click the first button, and choose Form Field Properties from the pop-up menu. Change the value of the button to Go! Click OK, and right-click the second button to change its properties. Change the button type for the second button to Reset. Click OK, and save your work.

Step 3

Now that the form is created, you need to change the form attributes to submit the form data to your search engine. Right-click the form in the Web page, and choose Form Properties from the pop-up menu. In the Form Properties dialog box, name the form frmLocate. Be sure that the form handler option is set to Custom ISAPI, NSAPI, Or CGI Script, the appropriate setting for sending the form data to an ASP page.

Click the Settings button to change the target for the form. In the Action text box in the Settings For Custom Form Handler dialog box, enter /BOOKSTORE/LOCATE.ASP, which is the name of the ASP page that will publish the database. You have not yet created LOCATE.ASP, but you will shortly. Click OK in both dialog boxes, and save your work.

Step 4

Leave FrontPage Editor, and return to Visual InterDev. To add a new file, click the project title on the FileView tab and then choose New from the File menu. In the New dialog box, create a new ASP page, name it LOCATE.ASP, and click the OK button.

In the BODY section of LOCATE.ASP, create some VBScript code that runs a query on the database and returns the results to the browser. Start by clicking the Data View tab in the project window. On the tab, double-click the Authors table, which should become visible in the work area of Visual InterDev.

You should also see the Query toolbar floating over the work area. Click the Show Diagram Pane button to view table relationships, and drag the Titles, Publishers, and Title Author tables from the project window to the work area.

These data tools can help create a query by selecting fields and specifying criteria. Start by clearing all the check marks in the tables you dragged to the work area. Next carefully check the Author, Title, and Company Name boxes in the Authors, Titles, and Publishers tables.

Now click the Show SQL Pane button on the Query toolbar to view the resulting SQL statement. The statement should look like this:

SELECT Authors.Author, Titles.Title, Publishers.`Company Name` FROM Authors, `Title Author`, Titles, Publishers WHERE Authors.Au_ID = `Title Author`.Au_ID AND `Title Author`.ISBN = Titles.ISBN

This SQL statement is not the complete statement, but you can use it as a starting point for modifying your Web page. Add the following code to the BODY section to run the query and format the result:

NOTE: This SQL statement can be tricky to create. The code is designed to concatenate pieces to produce one complete SQL statement. In this construction, you will find strange characters such as the back accent (`) and the single quote('). Don't confuse the two!

<% Public dbBooks Public rsBooks Dim strSQL ` Open a connection to Biblio Set dbBooks = Server.CreateObject("ADODB.Connection") dbBooks.Open("WebPages") ` Build SQL statement strSQL = "SELECT Authors.Author, Titles.Title, Publishers.`Company Name` " strSQL = strSQL & "FROM Authors, `Title Author`, Titles, Publishers " strSQL = strSQL & "WHERE (Authors.Au_ID = `Title Author`.Au_ID AND " strSQL = strSQL & "`Title Author`.ISBN = Titles.ISBN AND " strSQL = strSQL & "Titles.PubID = Publishers.PubID)" If Request.Form("txtTitle") <> "" Then     strSQL = strSQL & " AND Title LIKE `%" & Request.Form("txtTitle") & "%'" End If If Request.Form("txtAuthor") <> "" Then     strSQL = strSQL & " AND Author LIKE `%" & _         Request.Form("txtAuthor") & "%'" End If If Request.Form("txtPublisher") <> "" Then     strSQL = strSQL & " AND `Company Name` LIKE `%" & _         Request.Form("txtPublisher") & "%'" End If ` Run the query Set rsBooks = Server.CreateObject("ADODB.Recordset") rsBooks.Open strSQL, dbBooks, 3 %> <!-- Build the results table --> <% If rsBooks.BOF And rsBooks.EOF Then%>     <H2><CENTER>Sorry, no results! Please try again!</CENTER></H2> <%Else     ` Populate the cursor     rsBooks.MoveLast     rsBooks.MoveFirst End If If rsBooks.RecordCount > 200 Then%>     <H2><CENTER>     Sorry, too many results. Please narrow your search     </CENTER></H2> <%Else%>     <%If Not rsBooks.BOF Then%>     <H2>Here are the results of your search:</H2>     <TABLE BORDER>         <TR>         <TH>         Author         </TH>         <TH>         Title         </TH>         <TH>         Publisher         </TH>         </TR>     <%     Do While Not rsBooks.EOF     %>         <TR>         <TD>         <%=rsBooks("Author")%>         </TD>         <TD>         <A      HREF=     "/Chapter7/OrderForm.asp?Title=<%=Server.URLEncode(rsBooks("Title"))%>">         <%=rsBooks("Title")%>         </A>         </TD>         <TD>         <%=rsBooks("Company Name")%>         </TD>         </TR>     <%         rsBooks.MoveNext     Loop     %>     </TABLE>     <%End If%> <%End If%> <% ` Close database rsBooks.Close dbBooks.Close Set rsBooks = Nothing Set dbBooks = Nothing %>

NOTE: Normally you would expect such an application to present result sets as pages containing sets of 10 or 20. While ADO supports paging, not all ODBC drivers do. In particular, the Access drivers used in this exercise do not support paging.

Step 5

Save your work, and run the project in Internet Explorer. Test the search form to see whether results are returned properly.

Step 6

Now add a new menu command that will automatically submit the search form for the user. Close LOCATE.ASP, and open DEFAULT.HTM in Visual InterDev Editor. Locate the Window_OnLoad event where you described the commands that the Agent would perform. Add a new command named Find Book by using the Add method of the Commands object. The following code shows the complete Window_OnLoad event for the Agent:

Sub Window_OnLoad()     ` Initialize     ` the variables when the page     ` is fully loaded     ` Scrolling status     strMessage = "Welcome to Web Pages!!"     intSpaces = 100     intTimeout = Window.SetTimeout("Scroll", 100)     ` Dimension variables     Dim Agents     Dim AgentPth          ` Set variables     Set Agents = Wizard.Characters     AgentPath = "c:\program files\microsoft agent\characters\"          ` Load character     Agents.Load "WIZARD", AgentPath & "Merlin.acs"     ` Create custom commands     Agents("WIZARD").Commands.Caption = "Custom Commands"     ` Name, Caption, Voice Command, Enabled, Visible     Agents("WIZARD").Commands.Add "Search", "Search", "Search", _         True, True     Agents("WIZARD").Commands.Add "Find Book", "Find Book", _         "Find Book", True, True          ` Show Agent     Agents("WIZARD").Show     Agents("WIZARD").Play "Surprised"     Agents("WIZARD").Speak "Welcome to WebPages!" End Sub

Step 7

When a user chooses the Find Book command, the Agent should explain how to use the search form to locate a book. This is accomplished through the Wizard_Command event, which moves the Agent and explains the form. Code for the Wizard_Command event should look like this:

Sub Wizard_Command(UserInput)     ` Set variables     Dim Agent     Set Agent = Wizard.Characters("WIZARD")          ` Go to search page     If UserInput.Voice = "Search" Or _        UserInput.Name = "Search" Then         Window.Navigate "search.htm"             End If     ` Explain book search     If UserInput.Voice = "Find Book" Or _        UserInput.Name = "Find Book" Then         Window.Navigate "#Find Book"                  ` Move Agent         Agent.MoveTo Window.Screen.Width - 100,                       Window.Screen.Height - 100         ` Point to form         Agent.GestureAt Int(Window.Screen.Width / 2),                          Int(Window.Screen.Height / 2)         ` Give instructions         Agent.Speak "Use this form to find a book."                  ` Move again         Agent.MoveTo 0, 0     End If End Sub

Step 8

Save your work, and run the Web site in Internet Explorer. Test the search form and the Agent functions.