Select information from a database

About selecting information

After creating a database connection (database connection: A connection that specifies the name, type, location, and optional information for a database file.), the Database Results Wizard shows the record sources (the names of the  tables and views)  in that database.

  Represents a record source table containing employee information

  Field labels indicating employee's first name, last name, and telephone number

  Three employee records

  A web page displaying first name, last name, and telephone number information for three employees

Once you select the fields to display, the Database Results Wizard enables you to filter the database results — that is, to display only the records that match the criteria you set. For example, you can filter a database to display only the records of employees with the same last name. When the page is browsed, only matching records are displayed.

  Employee records in a database

  Only records that meet the filter criteria are displayed in the web site

You can also sort records. For example, you can sort records alphabetically by last name, and then by first name.

If you already have a database results region, perform the steps below in any order, as needed. Otherwise, do them in the order presented here:

Choose a record source

1. Start the Database Results Wizard by clicking the Insert menu, pointing to Database, and then clicking Results. Click Next to get to Step 2 of the wizard.

   Note   On a page with an existing database results region, right-click the database results region, and then click Database Results Properties. The Database Results Wizard starts.

2. In Step 2 of the wizard, click Record source.
3. In the Record source box, click the table or view that contains the information you want to display in the database results region.

Select the database fields to display

After choosing a record source, determine what fields to show in the database results region and the order they're displayed in.

1. Start the Database Results Wizard by clicking the Insert menu, pointing to Database, and then clicking Results. Click Next to get to Step 3 of the wizard.

   Note   On a page with an existing database results region, right-click the database results region, and then click Database Results Properties. The Database Results Wizard starts.

2. In Step 3 of the wizard, click Edit List.
3. 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.

    
4. When you're finished working with the Displayed fields list, click OK.

Specify criteria for filtering records

The Database Results Wizard can help you build a query to display in the database results region, as follows:

1. Start the Database Results Wizard by clicking the Insert menu, pointing to Database, and then clicking Results. Click Next to get to Step 3 of the wizard.

   Note   On a page with an existing database results region, right-click the database results region, and then click Database Results Properties. The Database Results Wizard starts.

2. In Step 3 of the wizard, click More Options, click Criteria, and then click Add.
3. 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.

4. 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.

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

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

6. Clear the Use this search form field check box.
7. Decide whether your query is complete. Choose one of the following:
   • If you're finished specifying criteria, click OK three times to close 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. In the Criteria dialog box, click Add, and repeat steps 3 through 6 of this procedure. When you have finished, click OK.

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 the sort order for database fields

1. Start the Database Results Wizard by clicking the Insert menu, pointing to Database, and then clicking Results. Click Next to get to Step 3 in the wizard.

   Note   In an existing database results region, right-click the database results region, and then click Database Results Properties. The Database Results Wizard starts.

2. In Step 3 of the wizard, click More Options, and then click Ordering.
3. 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).
4. Do any or all of the following to organize the sort order list:
   • 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.
5. 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.

Create a custom query

You can create custom queries using Structured Query Language (SQL) (Structured Query Language (SQL): A database query and programming language widely used for accessing, querying, updating, and managing data in relational database systems.).

1. Start the Database Results Wizard by clicking the Insert menu, pointing to Database, and then clicking Results. Click Next to get to Step 2 of the wizard.
2. In Step 2 of the wizard, click Custom query.
3. Click Edit, and then, in the SQL statement box, type a valid SQL statement.
4. To insert the name of a form field parameter into the query, click Insert Parameter, and then type the name of the desired field.