DOCUMENT:Q306430  13-MAY-2002  [frontpg]
TITLE   :FP: SQL Syntax Generated by the FrontPage Database Results Wizar
PRODUCT :Word Front Page
PROD/VER::
OPER/SYS:
KEYWORDS:kbdta

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft FrontPage 2002 
 - Microsoft FrontPage 2000 
-------------------------------------------------------------------------------

SUMMARY
=======

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.

MORE INFORMATION
================

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%" (without the quotation marks) 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.

REFERENCES
==========

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

   http://msdn.microsoft.com/

Additional query words:

======================================================================
Keywords          : kbdta 
Technology        : kbFrontPageSearch kbFrontPage2002 kbFrontPage2000Search kbFrontPage2002Search kbZNotKeyword5
Version           : :
Issue type        : kbinfo

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2002.