HOW TO: Query Two Tables in the Same Database in FrontPage 2003

This article describes a method that you can use to query and retrieve information from two tables in a database in FrontPage 2003. The example that is described in this article uses Microsoft SQL statements to search for data in the Categories and Products tables in the sample Northwind database that match a submitted value for a column that is used in both tables.

How to Create a Query to Retrieve Information from Two Tables in a Database

To create a search page and a results page to search for and find specific values from two tables in a database, follow these steps:

1. Create two new pages that are named Search.asp and Results.asp. To do so, follow these:
   1. Start FrontPage (if it is not already started).
   2. On the File menu, point to New, and then in the New task pane, click Blank page.
   3. On the File menu, click Save.
   4. Specify a location where you want to save the file, type Search.asp in the File name box, and then click Save.
   5. On the File menu, point to New, and then in the New task pane, click Blank page.
   6. On the File menu, click Save.
   7. Specify a location where you want to save the file, type Results.asp in the File name box, and then click Save.
2. Create and configure the form on the search page. To do so, follow these steps:
   1. In the Folder List, double-click Search.asp to open the page.
   2. On the Insert menu, point to Form, and then click Textbox.
   3. On the page, right-click the text box that you inserted, and then click Form Field Properties.
   4. In the Name box, type ID, and then click OK.
   5. On the page, right-click the text box, and then click Form Properties.
   6. Under Where to store results, click Send to other, and then click Custom ISAPI, NSAPI, CGI, or ASP Script.
   7. Click Options.
   8. In the Action box, type results.asp, and then click OK.
   9. Click Results.asp, click OK, click OK, and then click OK.
   10. On the File menu, click Save.
3. Configure the database query on the results page. To do so, follow these steps:
   1. In the Folder List, double-click Results.asp to open the page.
   2. On the Insert menu, point to Database, and then click Results.
   3. Click Use a sample database connection (Northwind), and then click Next.
   4. Click Custom query, and then click Edit.
   5. Either type or copy and paste the following line to the SQL Statement box:
      Select * From Categories,Products Where Categories.CategoryID=::ID:: and Categories.CategoryID=Products.CategoryID
      Note This SQL statement returns all the columns from the Categories table and the Products table where the ID that is sent from the Search.asp page matches the CategoryID in the Categories table and the Category ID in the Categories table matches the CategoryID in the Products table.
   6. Click OK.
   7. Click Next three times.
   8. Click Display all records together, click to clear the Add search form check box, and then click Finish.
   9. On the File menu, click Save.
4. In the Folder List, double-click Search.asp to open the page.
5. On the File menu, click Preview in Browser, and then click the Web browser that you want to use.
6. In the text box, type 3, and then click Submit.
   
   The columns from the Categories table and the Products table that contain a CategoryID value of 3 are returned.

For more information about the Database Results Wizard in FrontPage 2003, see FrontPage Help. To do so, on the Help menu, click Microsoft Office FrontPage Help. In the Search for box, type database results, and then press ENTER to view the topics that are returned.

For more information about FrontPage, visit the following Microsoft Web site: Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.