Description of the SQL syntax that is generated by the FrontPage Database Results Wizard| Article ID | : | 306430 | | Last Review | : | January 31, 2007 | | Revision | : | 3.2 |
This article was previously published under Q306430 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.
 Back to the top
MORE INFORMATIONNumeric 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. |
Back to the top REFERENCES For additional information about SQL, please visit the
following Microsoft Web site: Back to the top
APPLIES TO| • | Microsoft FrontPage 2002 Standard Edition | | • | Microsoft FrontPage 2000 Standard Edition |
Back to the top | Other Support Options - Contact Microsoft
Phone Numbers, Support Options and Pricing, Online Help, and more. - Customer Service
For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more. - Newsgroups
Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.
|
|
