Description of the SQL syntax that is generated by the FrontPage Database Results Wizard

FrontPage generates different Structured Query Language (SQL) syntax depending on whether you are querying for numeric or text/string data. This article explains the SQL syntax created by the Microsoft FrontPage Database Results Wizard by using examples based on the Categories table in the sample Northwind database. The Northwind database ships with FrontPage.

Numeric Field Queries

To view the comparison options available for numeric data fields, follow these steps:

1. In FrontPage, open a Web on an ASP-enabled Web server.
2. Start a new, blank page.
3. On the Insert menu, point to Database, and then click Results.
4. In Step 1 of the Database Results Wizard, click Use a sample database connection (Northwind), and then click Next.
5. In the Record Source list, click Categories, and then click Next.
6. Click More Options.
7. Click Criteria.
8. Click Add.
9. In the Field Name list, click CategoryID.

Following is a list of comparison options that are available for the CategoryID numeric data field and the syntax of SQL statements resulting from either manually entering the comparison value or by using the search feature.

• Using the Equals comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryID = ::CategoryID::)
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryID = number)
• Using the Not Equal comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryID <> ::CategoryID::)
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryID <> number)
• Using the Less Than comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryID < ::CategoryID::)
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryID < number)
• Using the Not Less Than comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryID >= ::CategoryID::)
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryID >= number)
• Using the Greater Than comparison:
  • If you choose Use this search form field:
    SELECT * FROM Categories WHERE (CategoryID > ::CategoryID::)
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryID > number)
• Using the Not Greater Than comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryID <= ::CategoryID::)
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryID <= number)
• Using the Is Null comparison:
  SELECT * FROM Categories WHERE (CategoryID IS NULL)
• Using the Is Not Null comparison:
  • SELECT * FROM Categories WHERE (CategoryID IS NOT NULL)

Text Field Queries

To view the comparison options available for text or string data fields, follow these steps:

1. In FrontPage, open a Web on an ASP-enabled Web server.
2. Start a new, blank page.
3. On the Insert menu, point to Database, and then click Results.
4. In Step 1 of the Database Results Wizard, click Use a sample database connection (Northwind), and then click Next.
5. In the Record Source list, click Categories, and then click Next.
6. Click More Options.
7. Click Criteria.
8. Click Add.
9. In the Field Name list, click CategoryName.

Following is a list of comparison options that are available for the CategoryName text/string data field and the syntax of SQL statements resulting from either manually entering the comparison value or by using the search feature.

• Using the Equals comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName = '::CategoryName::')
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName = 'text string')
• Using the Not Equal comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName <> '::CategoryName::')
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName <> 'text string')
• Using the Begins With comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName LIKE '::CategoryName::%')
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName LIKE 'text string%')
• Using the Not Begin With comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName NOT LIKE '::CategoryName::%')
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName NOT LIKE 'text string%')
• Using the Ends With comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName LIKE '%::CategoryName::')
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName LIKE '%text string')
• Using the Not End With comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName NOT LIKE '%::CategoryName::')
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName NOT LIKE '%text string')
• Using the Contains comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName LIKE '%::CategoryName::%')
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName LIKE '%text string%')
• Using the Not Contain comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName NOT LIKE '%::CategoryName::%')
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName NOT LIKE '%text string%')
• Using the Less Than comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName < '::CategoryName::')
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName < 'text string')
• Using the Not Less Than comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName >= '::CategoryName::')
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName >= 'text string')
• Using the Greater Than comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName > '::CategoryName::')
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName > 'text string')
• Using the Not Greater Than comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName <= '::CategoryName::')
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName <= 'text string')
• Using the Like comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName LIKE '::CategoryName::')
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName LIKE 'text string')
• Using the Not Like comparison:
  • If you choose Use this search form field, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName NOT LIKE '::CategoryName::')
  • If you manually enter the Value, the SQL statement looks like this:
    SELECT * FROM Categories WHERE (CategoryName NOT LIKE 'text string')
• Using the Is Null comparison:
  SELECT * FROM Categories WHERE (CategoryName IS NULL)
• Using the Is Not Null comparison:
  SELECT * FROM Categories WHERE (CategoryName IS NOT NULL)

Notes About Text/String Queries

• Text or string comparisons offer a wider range of query possibilities because text queries can use wildcard characters, whereas numeric queries are limited to values or NULL.
• Functionally, queries using the Like comparison as opposed to the Equals comparison may yield almost the same results. However, if you choose the Like comparison option, you can enter wildcard characters on the search form. For example, if you enter CON% in the search form, to search the CategoryName field in the Categories table of the Northwind database, the results include Condiments and Confections.
• Using a Greater Than or Less Than query with text data allows you to search for text that occurs alphabetically before or after the word you are using in the comparison. For example, if you are performing a Greater Than comparison for the word Meat in the CategoryName field in the Categories table of the Northwind database, the results include Meat/Poultry, Produce, and Seafood.

For additional information about SQL, please visit the following Microsoft Web site: