Re: Using Tdataset.locate with multiple fields

Posted by webmaster Guido on April 05, 2001 at 00:14:56:

In Reply to: Using Tdataset.locate with multiple fields posted by Ahmed on March 30, 2001 at 23:04:16:

: I tried to use locate for multiple fields in that way as the help of delphi says:
: fl:=ds.locate(df,'12',[]);
: where df=''id','code''
: but it give error.

Maybe the contents of your variable DF is wrong? From what I see, you should specify
DF := 'id;code' instead of DF := ''id','code''

Or maybe it is something else? Just compare with the following explanation, and write us again if it doesn't solve your problem.

Let's start with a short refresher for the other readers, so that they know what we are talking about.

Locate is used to search a dataset (such as a TTable or a TQuery) for a record that corresponds to certain conditions. It returns True if it finds a matching record, and it makes that record the "current" record. Otherwise it returns False.

Delphi's help gives the following syntax for Locate:

function Locate(const KeyFields: string; const KeyValues: Variant;
Options: TLocateOptions): Boolean;

KeyFields is a string, containing one field n ame or several field names separated by semicolons.

Suppose that we have a table Products.DB with the following fields:

id   - alfanumeric - size 2
code - alfanumeric - size 2
size - integer 

KeyFields could be 'id' or 'id;code' or 'id;code;size'.

KeyValues is a "variant", containing the value(s) to search for. A variant can be of any type: string, integer, boolean,...

If there is only one field in KeyFields, then KeyValues simply contains the value to search for. Examples:

  Found := Product.Locate('id', '12', []);
  Found := Product.Locate('size', 3,  []); 

If there are several fields in KeyFields, then KeyValues should be a "variant array". It is allowed to construct that variant array "on the fly" using the VarArrayOf function. Example:

  Product.Locate('id;code', VarArrayOf(['12', 'AB']), []);

Options is a "set" that optionally specifies additional search conditions when your condition contains one or more *string* fields:
- if Options contains "loCaseInsensitive", then there is no difference between upper case and lower case;
- if Options contains "loPartialKey", then also *partial* matches for string fields are located;
- if Options is empty, or if KeyFields does not include any string fields, Options is ignored.

To search for all products with an 'id' starting with '2':

  Found := Product.Locate('id',   '2', [loPartialKey]); 

To search for all products with a 'code' starting with 'a' or 'A':

  Found := Product.Locate('code',   'a', [loCaseInsensitive, loPartialKey]);