Add information from a database to your web pages

With this step-by-step topic, you will learn how to integrate a database with a web site using the Database Results Wizard (Database Results Wizard: A FrontPage wizard that guides you through creating a region on a page that displays information obtained from the records of a database. You can use an existing database to accomplish this or let the wizard create one for you.).

What you should know before you begin

About your database

• The type and name of your database (for example, a Microsoft Access database named fpnwind.mdb)
• Where the database is stored (for example, on a web server, a database server, or on your hard drive)

About your server and client computers

About your web site

To use the Database Results Wizard, you must first create (or open) a web site.

If you have a local web server, create or open your web site using HTTP (for example, http://localhost/web). If you don't have a local server, use C:/My Documents/My Webs, or another disk-based location.

Important   You can't test how your site displays database information on a disk-based web until you publish it to a server that meets the server requirements.

How to use the Database Results Wizard

Step 1 — Connect to a database

Before you can use the information in a database on your web pages, you must create a database connection (database connection: A connection that specifies the name, type, location, and optional information for a database file.). Step 1 of the Database Results Wizard is used to set up the connection. Once you set up a database connection, you can use it on any page in your web site.

If you're familiar with databases, you can set up a new connection. Otherwise, you can practice using the sample connection and database provided with Microsoft FrontPage.

A System Data Source Name (System DSN) on a web server

A System DSN (system DSN: A system data source name is specific to a local computer. The system or any user with privileges can use a data source that is set up with a system DSN.) is located on a web server. A System DSN can connect to an ODBC-compliant (Open Database Connectivity (ODBC): A standard method of sharing data between databases and programs. ODBC drivers use the standard Structured Query Language (SQL) to gain access to external data.) database (such as a Microsoft Access or Microsoft Excel database), or a database management system (such as Microsoft SQL Server).

Ask your web server administrator to create a System DSN if you need one defined.

1. Click System data source on Web server, and then click Browse.

   The System Data Sources on Web Server dialog box is displayed. The list shows the System DSNs defined on the web server.

2. Select the System DSN for the database you want to access, and then click OK.
3. Click OK to close the New Database Connection dialog box, and then click OK to close the Web Settings dialog box.

A database server on a network

A database server is a computer dedicated solely to managing and maintaining large databases (such as Microsoft SQL Server (Microsoft SQL Server database: A database in Microsoft SQL Server, it consists of tables, views, indexes, stored procedures, functions, and triggers.)). A network connection to a database server accesses this type of database.

1. Click Network connection to database server, and then click Browse.
2. In the Type of database driver box, select the type of database driver you want to use to connect to the database server.
3. In the Server name box, type the URL of the network server containing the database you want to access.
4. In the Database name box, type the name of the database, and then click OK.
5. Click OK to close the next three dialog boxes.

A database using a custom definition

A custom connection uses a file or string that defines all the necessary information.

1. Click Custom Definition, and then click Browse.
2. In the Files of type box, select a File DSN, Universal Data Link (UDL) file, or edit a connection string:
   • To select a File DSN or UDL file in the current web, click Browse. In the URL box, type the path to the File DSN or UDL file to which you want to connect, and then click OK. Or, use the Look in box and file list to navigate to the File DSN or UDL file you want, and then double-click the file in the list.
   • To edit a connection string, click Advanced. In the Connection string box, type the appropriate string to pass to the database driver to which you want to connect. Click OK.

     Note   Do not use a File DSN to connect to a database file in the current web; the File DSN will contain file paths that are incorrect for the web server. Instead, create a database connection to a file or folder in the current web.

     Any timeouts or other name-value parameters for the database must be contained in the File DSN. If they are not, click Advanced, and then enter the appropriate information.

3. In the Database name box, type the name of the database, and then click OK. The database driver referred to by the File DSN or the connection string must be present on the web server for the connection to work properly.
4. Click OK to close the next three dialog boxes.

Use a sample connection and database

The sample database provided by Microsoft FrontPage is a Microsoft Access database. If you use this database, the wizard automatically imports the database file fpnwind.mdb into the Fpdb folder of your web site. If you have Microsoft Access 2000 or later, you can open this file to view its contents. Viewing the contents will help you understand the other options in the wizard.

1. Make sure your insertion point is on a web page. Start the Database Results Wizard (on the Insert menu, point to Database, and then click Results).
2. In Step 1 of wizard, click Use a sample database connection (Northwind).

Step 2 — Choose a record source

After you have created the database connection, Step 2 of the Database Results Wizard displays the record sources (the names of the tables and views) in the database. Choose the source you want to use.

1. In Step 2 of the wizard, click Record source.
2. From the drop-down list, click the table or view that contains the information you want to display.

Step 3 — Determine how records will display

Step 3 of the wizard lets you determine how you want the records in the database to display. In this step of the wizard:

• Choose the database fields to display (for example you may want to show the ProductName field, and the UnitPrice field).
• Filter specific records (for example, you may only want to see Food products).
• Sort the records in a alphabetical or numerical order. You can sort in either ascending or descending order.
• Determine how many records to display (for example, you can show anywhere between 1 and 256 records that meet your filter criteria).

  Select the database fields to display

  1. In Step 3 of the wizard, click Edit List.
  2. Do one of the following to modify the Displayed fields list:
     • Remove a field from the list.
     • Select the field you want to remove, and then click Remove.
     • Change the field display order.
     • Select the field you want to move, and then click Move Up or Move Down.
     • Add a field to the list.
     • In the Available fields list, select the field you want to add, and then click Add to add the field to the Displayed fields list.
  3. When you're finished working with the Displayed fields list, click OK.

  Specify criteria to filter database results

  1. In Step 3 of the wizard, click More Options, click Criteria, and then click Add.
  2. In the Field name box, select the field that contains the values you want to look for.

     For example, from a database of employees, you could select the LastName field.

  3. In the Comparison box, select the type of comparison you want to perform.

     For example, to filter all last names equal to "Smith," you would select Equal in the Comparison box.

  4. In the Value area, enter the value that you want to filter.

     For example, to display all last names equal to "Smith," you would enter Smith in the Value box.

  5. Clear the Use this search form field check box.
  6. Decide whether your query is complete. Choose one of the following:
     • If you're finished specifying criteria, click OK until you leave criteria setup, but continue using the wizard.
     • To continue to build your query, select And or Or in the And/Or box, and then click OK.
  7. In the Criteria dialog box, click Add and repeat steps 2-6 of this procedure, and then click OK when finished.

  Notes  

  • To modify a clause in existing criteria, select the condition you want to modify in the Criteria dialog box, and then click Modify.
  • To remove a clause from existing criteria, select the condition you want to remove in the Criteria dialog box, and then click Remove.

  Specify a sort order for records

  1. In Step 3 of the wizard, click More Options, and then click Ordering.
  2. In the Available fields list, select the primary field you want to use to sort the database results, and then click Add to add that field to the Sort order list.
     • The Available fields list displays all of the fields in the selected record source.
     • The Sort order list displays the fields that will be used to sort the database results. An up arrow next to a field name indicates that the field will be sorted in ascending order (for example, from A to Z). A down arrow indicates a descending sort order (for example, from Z to A).
  3. To organize the sort order of the list, do any of the following:
     • To remove a field from the Sort order list, select it, and then click Remove.
     • To change the order of fields in the Sort order list, select the field you want to move, and then click Move Up or Move Down.
     • To change the sort order for a field from ascending to descending, select the field in the Sort order list, and then click Change Sort. The arrow next to the field name changes to indicate the sort direction.
  4. When you are satisfied with the sorting order in the Sort order list, click OK.

  Note   You can add multiple fields to the Sort order list. Database results will be sorted by the first field in the list, and then they will be sorted by the second field in the list. For example, if you want to display a list of employees sorted by last name and then by first name, you would add the fields LastName and then FirstName to the Sort order list.

  Show a specific number of records

  You can set a maximum number of records to be returned and displayed in the Database Results region (Database Results region: An area on an Active Server Page that can be dynamically populated by the results of a database query when the page is displayed in a Web browser.).

  1. Start the Database Results Wizard (Database Results Wizard: A FrontPage wizard that guides you through creating a region on a page that displays information obtained from the records of a database. You can use an existing database to accomplish this or let the wizard create one for you.) (on the Insert menu, point to Database, and then click Results), and click Next to get to Step 3.
  2. In Step 3 of the wizard, click More Options.
  3. Select the Limit number of returned records to check box, and then type a value representing the maximum number of records.

Step 4 — Choose formatting options for database results

In Step 4 of the wizard you determine how you want to format the database results. You can choose to format the information in a table, a list, or a drop-down list.

Format database results as a list

In Step 4 of the wizard, in the Choose formatting options for the records returned by the query list, click List - one field per item.

Specify any additional list formatting options you want, as follows:

• To hide field labels and display only data, clear the Add labels for all field values check box.
• To display the results in one continuous list, with no separator between records, clear the Place horizontal separator between records check box.
• In the List options box, select a formatting style for the list, such as Bulleted list or Numbered list.

Format database results as a drop-down list

1. In Step 4 of the wizard, in the Choose formatting options for the records returned by the query list, click Drop-down list - one record per item.
2. Under Display values from this field, select the field you want to use for the choices that will be displayed to the site visitor.
3. In the Submit values from this field box, select the field to be submitted if the drop-down list is inside a form. You can choose the same field you selected in the previous step, or choose a different one.

Step 5 — Show records together or in groups, and finish the wizard

The final step of the wizard lets you determine whether to show all records together or separate them into smaller groups. If you choose to display records in smaller groups, Microsoft FrontPage automatically adds navigation controls to let site visitors navigate through the groups of records.

Note   The option to Split records into groups is unavailable if you select Drop-down list in Step 4 of the wizard.

1. In Step 5 of the wizard, choose one of the following grouping options:

   Display records in one list or table on a page

   Click Display all records together.

   Divide records into smaller groups

   Click Split records into groups, then type the number of records you want to include in each group.

    
2. Click Finish to end the wizard. The web page will display the database results region you created using the wizard.

Save and test your web site

Once you have completed the wizard, a Database Results region (Database Results region: An area on an Active Server Page that can be dynamically populated by the results of a database query when the page is displayed in a Web browser.) is inserted on the web page. To view how the data will look when your web is browsed, you must save the page as an Active Server Page (ASP) (Active Server Page (ASP): A file that contains embedded server-side ing that is executed on a server and sent to and displayed in a client Web browser as a standard HTML file. ) and browse the site to view database results.

1. On the File menu, click Save as. Notice that the Save as type box indicates Active Server Pages. Enter a name for the file in the File Name box and ensure that the file extension is .asp.
2. Click Preview in Browser  to view the site.

Note   If you created a disk-based web site, you must first publish your site to a web server that meets the server requirements.