Using LIKE and '%' in an ADO dataset filter

Question

I have a problem when trying to apply a filter to a ADO dataset in Delphi XE2 ...

Filter := ' [Name] like ''%john'' ';

It raises an exception:

Project Test.exe raised exception class EOleException with message 
'Arguments are of the wrong type, are out of acceptable range, 
or are in conflict with one another'. Process stopped. 

but when using:

Filter := ' [Name] like ''john%'' '

it works fine!

why?

Answer 1

The Operator can only be one of the following: = < > <= >= <> LIKE If you use the LIKE operator, you can also use the * or % wildcards as the last character in the string or as the first and last character in the string.

http://www.devguru.com/technologies/ado/quickref/recordset_filter.html

You can catch the filter on the event OnFilterRecord:

procedure TForm1.ADODataSet1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
var
  iPos: Integer;
begin
  iPos:= pos('john',ADODataSet1name.AsString);
  if (iPos>0) and
     (iPos = length(ADODataSet1name.AsString)-3) then
  begin
    Accept:= True;
  end
  else
  begin
    Accept:= False;
  end;
end;

or

function TForm1.IsLastCriteria(AText: String): Boolean;
var
  iPos: Integer;
begin
  iPos:= pos(AText,ADODataSet1name.AsString);
  Result:= (iPos>0) and
           (iPos = length(ADODataSet1name.AsString)-length(AText)-1);
end;

procedure TForm1.ADODataSet1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
begin
  Accept:= IsLastCriteria('john');
end;

Answer 2

AS. I still ask you to read http://www.catb.org/esr/faqs/smart-questions.html#beprecise and describe your environment accordingly.

• What is database server ?
• What version are MDAC/ADO components?
• What the query is ?
• What is Name column type in SQL ?

You are "upping" the comments, that si nice. But you don't answer the questions. And that is not nice. We are not ESPers, we canot read you mind.

I put you few suggestions in comments above. Did you tried them ? Did they worked ? To quote them:

1. Maybe you can update ADO/MDAC to 2.8sp1 version. Referenced MSDN KB articles are told to apply to MDAC up to 2.7 version. Maybe 2.8sp1 no more has that limitation.
2. Maybe you can use some data-server specific tricks like copying last 4 letters of Name into a separate column.
3. Maybe you can move that condition into SQL SELECT WHERE clause and re-open the query.
4. Maybe there is event-handler like TBDEDataSet.OnFilterRecord

There are 4 workarounds and you either did not tried them or did not reported the results. Not nice.

• Idea #1 is self-explanatory
• Idea #3 was detailed by @SertacAkyuz as far as he could do it, giving lack of information about your program.
• Idea #4 was detailed by @Ravaut123
• Idea #2 is outlined below

Assuming your "ADO database" is backed by Microsoft Access, and according to http://www.databasedev.co.uk/access-sql-string-functions.html ... Or using MS SQL and according to http://msdn.microsoft.com/en-us/library/ms177532.aspx ... Or...

Query.SQL.Text := 'select Right(Name, 4) as name_tail, * from table where ...'

See that extra column added to query! 4 is length for "john" there.

With query like that the following filtering conditions are to be equivalent:

• Filter := ' [Name] like ''%john'' ';
• Filter := ' [name_tail] = ''john'' ';

However, if on some rows Name column is shorter than 4 letters, i don't know what RIGHT function would do. Maybe it will truncate the result, or maybe throw an error and abort the query. The latter maybe - depending on the real data - maybe can be alleviated by padding with spaces to the 4 length like

LTrim(Right('    ' || Name, 4)) as name_tail

That is for you to test, since only you know the details of your environment.