SELECT – Restricted
This statement restricts the data returned by comparing the data in the table against the conditions specified in the WHERE clause.
Syntax
SELECT * FROM tablename WHERE fieldname-expression [AND|OR fieldname-expression]
Parameters
- tablename
- Specifies the name of the table from which to retrieve data.
- fieldname-expression
- Can take one of the following forms:
- [NOT] fieldname operator constant
- fieldname IS [NOT] NULL
- fieldname IS [NOT] TRUE
- fieldname IS [NOT] FALSE
- fieldname [NOT] LIKE “string%”
- fieldname
- Specifies the name of a field in the table to use in a comparison operation.
- operator
- Can be one of the values described in the following table.
Operator
|
Description
|
| = |
Equals |
| > |
Greater than |
| >= |
Greater than or equal |
| < |
Less than |
| <= |
Less than or equal |
| <> |
Not equal |
- constant
- Specifies a numeric string or date, enclosed by quotation marks.
- string%
- String constant followed by the “%” or “*” wildcard character. A wildcard character can also be used by itself to match everything. Use a wildcard character only at the end of the string constant.
Remarks
Parentheses can be used in the usual way to group expressions and establish precedence. The following additional rules apply to the WHERE statement:
- No type conversion is done.
- BETWEEN is not supported.
- IN is not supported.
- LIKE only works against varchar data types.
- WHERE clauses against unsigned data are not supported.
- The varbinary data types are supported only with the IS [NOT] NULL operator.
- Floating point comparisons are approximate. Testing for equality against floating point values is not advised.
Return Values
One of the following error values can be returned:
- E_OUTOFMEMORY
- DB_E_ERRORSINCOMMAND
- DB_E_NOTABLE
- DB_E_BADCOLUMNID
- DB_E_CANTCONVERTVALUE
- DB_E_DATAOVERFLOW
Example
Dim rs
Set rs = CreateObject("adoce.recordset")
rs.open "select * from msystables where tablename like 'msys%'"
MsgBox rs.RecordCount
rs.Close
Set rs = Nothing