0

I'm using Delphi 2007.

I know I can use the .locate method of a TClientDataSet to locate a record, like this:

myClient.locate('name','John',[loPartialKey,loCaseInsensitive]);

But let's say I want to locate any record with 'John' containing on its name, something as:

name like '%John%'

Would do it in a regular SQL expression.

Is this possible using the .locate method?

It seems the [loPartialKey] works as starting with instead of containing.

fatihyildizhan
  • 8,614
  • 7
  • 64
  • 88
delphirules
  • 6,443
  • 17
  • 59
  • 108
  • Have you try to use the lookup method not sure if is in 2007? https://edn.embarcadero.com/article/29176 – Deleted Dec 03 '19 at 20:02

4 Answers4

3

In .Locate, loPartialKey matches from the first character in the field value, so you can't do what you want using only .Locate.

However, the Filter property of TClientDataSet can contain like, as in setting the CDS's Filter property to 

AField like '%w%'  // matches all AField values containing `w`

or, in code,

  ClientDataset1.Filtered := False;
  ClientDataset1.Filter :=  'AField like ' + QuotedStr('%' + edFilter.Text + '%');
  ClientDataset1.Filtered := True;

so you may be able to use the filter to narrow down the number of rows and use Locate to find a specfic one, or simply iterate over the filtered rows to find the exact one you want.

MartynA
  • 30,454
  • 4
  • 32
  • 73
  • Are you sure about `AField`? – Ilyes Dec 03 '19 at 20:49
  • @Sami: Yes, AField being the field's name. It certainly works in D7. – MartynA Dec 03 '19 at 20:52
  • Yup, I know what did you mean but it should be a string `'FieldName'` and you should also use `QuotedStr()` for `'%w%'`. Something like `:= 'AField like ' + QuotedStr('%w%');` – Ilyes Dec 03 '19 at 20:55
  • @Sami, sorry, what I quoted, w/o quotes around AField works fine if entered using the propertt editor. Thanks foir catching that. I can't immediately see that QuotedStr would mae any difference on the RHS, but it's been a long day. – MartynA Dec 03 '19 at 21:00
  • I suppose you're tired :). but a `Filter` property is a string, so when you pass the string as you're suggesting, then it'll be `'AField LIKE %w%'`. Is that valid? – Ilyes Dec 03 '19 at 21:11
  • Nice, did not know the filter expression can contain like ! – delphirules Dec 03 '19 at 23:54
  • @Sami: I've updated it to show setting the filter in code. Also I can confirm that when set via the `Filter` property of the CDS, the quotes around %w% aren't required, which is a bit curious as you noted. I'll remind myself how the filter expression gets parsed and see if that reveals why. – MartynA Dec 04 '19 at 08:27
0

No. On the first part can be partially matched. As mention in the help for "Using Locate".

Locate moves the cursor to the first row matching a specified set of search criteria. In its simplest form, you pass Locate the name of a column to search, a field value to match, and an options flag specifying whether the search is case-insensitive or if it can use partial-key matching. (Partial-key matching is when the criterion string need only be a prefix of the field value.) For example, the following code moves the cursor to the first row in the CustTable where the value in the Company column is "Professional Divers, Ltd.":

var
  LocateSuccess: Boolean;
  SearchOptions: TLocateOptions;
begin
  SearchOptions := [loPartialKey];
  LocateSuccess := CustTable.Locate('Company', 'Professional Divers, Ltd.', SearchOptions);
end;

ref: Delphi online documentation: Using Locate

Brian
  • 6,717
  • 2
  • 23
  • 31
0

LocateRecord internally uses DSCursor.LocateWithFilter so I believe it's possible to write custom method doing what you want

Vladimir Ulchenko
  • 463
  • 2
  • 8
0

It is better to use .FindFirst and .FindNext :

myDataset.Filter := 'CustomerName LIKE ' + ('*' + edtSearch.Text + '*').QuotedString;
myDataset.Filtered := False;
Found :=  myDataset.FindFirst

if Found then

Use .Locate when you want to search for a key that you know the exact value.

And using just Filtered := true will work, for situations that you do not mind if other records will disapear on the dbGrid.

Omid Mehdizadeh
  • 73
  • 4