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