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.
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.
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.
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.
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.
Save your work, and run the project in Internet Explorer. Test the search form to see whether results are returned properly.
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
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
Save your work, and run the Web site in Internet Explorer. Test the search form and the Agent functions.