Page 277 - DCAP408_WEB_PROGRAMMING

Unit 12: Recordset Object

For numbers, you can use decimal points, dollar signs, and scientific notation. If Operator Notes
is LIKE, Value can use wildcards. Only the asterisk (*) and percent sign (%) wild cards are
allowed, and they must be the last character in the string. Value cannot be null.

Notes To include single quotation marks (‘) in the filter Value, use two single quotation
marks to represent one. For example, to filter on O’Malley, the criteria string should be
“col1 = ‘O’’Malley’”. To include single quotation marks at both the beginning and the end
of the filter value, enclose the string with pound signs (#). For example, to filter on ‘1’, the
criteria string should be “col1 = #’1'#”.
 There is no precedence between AND and OR. Clauses can be grouped within parentheses.
However, you cannot group clauses joined by an OR and then join the group to another
clause with an AND, like this:
(LastName = ‘Smith’ OR LastName = ‘Jones’) AND FirstName = ‘John’
 Instead, you would construct this filter as
LastName = ‘Smith’ AND FirstName = ‘John’) OR (LastName = ‘Jones’
AND FirstName = ‘John’)
 In a LIKE clause, you can use a wildcard at the beginning and end of the pattern

Example: LastName Like ‘*mit*’
, or only at the end of the pattern

Example: LastName Like ‘Smit*’
The filter constants make it easier to resolve individual record conflicts during batch update
mode by allowing you to view, for example, only those records that were affected during the
last UpdateBatch method call.
Setting the Filter property itself may fail because of a conflict with the underlying data (for
example, a record has already been deleted by another user). In such a case, the provider returns
warnings to the Errors collection but does not halt program execution. A run-time error occurs
only if there are conflicts on all the requested records. Use the Status property to locate records
with conflicts.
Setting the Filter property to a zero-length string (“”) has the same effect as using the adFilterNone
constant.
Whenever the Filter property is set, the current record position moves to the first record in the
filtered subset of records in the Recordset. Similarly, when the Filter property is cleared, the
current record position moves to the first record in the Recordset.
When a Recordset is filtered based on a field of some variant type (e.g., sql_variant), an error
(DISP_E_TYPEMISMATCH or 80020005) will result if the subtypes of the field and filter values
used in the criteria string do not match. For example, if a Recordset object (rs) contains a column
(C) of the sql_variant type and a field of this column has been assigned a value of 1 of the I4 type,
setting the criteria string of rs.Filter = “C=’A’” on the field will produce the error at run time.
However, rs.Filter = “C=2” applied on the same field will not produce any error although the
field will be filtered out of the current record set.

LOVELY PROFESSIONAL UNIVERSITY 271