KnowledgeBase Archive

An Archive of Early Microsoft KnowledgeBase Articles

View on GitHub
Q306430: FP: SQL Syntax Generated by the FrontPage Database Results Wizar

Article: Q306430
Product(s): Word Front Page
Version(s): 
Operating System(s): 
Keyword(s): kbdta
Last Modified: 13-MAY-2002

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