Filter on integer field

Question

I have client table and i want to filter the table on the field chosen by the user all table fields included in combobox component at run time if the field/item is string type whatever the user type in EdtSearch.Text the filter work however if the user chose the id which is firebird integer auto_inc field get exception:

Exception class EVariantTypeCastError with message 'Could not convert variant of type (OleStr) into type (Boolean)'.

Code:

procedure Tfrm_Personnes.EdtSearchChange(Sender: TObject);
var
  Pattern: string;
begin
  if CbxSearchOptions.Text = 'Commence' then Pattern := QuotedStr(EdtSearch.Text +     '*');
  if CbxSearchOptions.Text = 'Contient' then Pattern := QuotedStr('*' + EdtSearch.Text + '*');
  with TClientDataSet(dts_Tableau_Personnes.DataSet) do
  begin
    if EdtSearch.Text <> EmptyStr then
    begin
      Filter := DisplayToOriginName(dts_Tableau_Personnes, CbxField.Text)+' = ' +     Pattern;
      Filtered := True;
    end else
      Filtered := False;
  end;
end;

function DisplayToOriginName(DataSource: TDataSource; DisplayName: string): string;
var
  I: Integer;
begin
  with TClientDataSet(DataSource.DataSet) do
  for I := 0 to FieldCount - 1 do
  begin
    if SameStr(Fields[i].DisplayName, DisplayName) then
      Result  := Fields[i].FieldName;
  end;
end;

what is the proper way to get the filter work on integer field thank's

Are you aware of the OnFilterRecord event? It will give you more control than the Filter string. — Uwe Raabe, Aug 10 '13 at 20:44
Uwe Raabe thank's i'll give it a try i search before ask here but no one point to OnFilterRecord event some solution said i should add calculated field asString and filtering with it the solution did not convince me.... — S.FATEH, Aug 10 '13 at 21:08
Are you also aware that you can't use the `LIKE` wildcards with numeric fields? (Trying to filter on a number `LIKE 1*` wouldn't make any sense, but it's also not a valid SQL or filter condition.) — Ken White, Aug 10 '13 at 22:25
Ken White thank's i read your comments [from](http://stackoverflow.com/questions/12290204/similar-search-in-integer-field-by-filter-property) that's and the fact that like and select are server side make me run away from using them — S.FATEH, Aug 10 '13 at 22:44

score 1 · Accepted Answer · answered Aug 10 '13 at 23:22

the best solution is to use OnFilterRecord Event from the documentation:

OnFilterRecord events generated by the dataset for each record it retrieves

the event has parameter by reference Accept which determine whether the record is accepted (Example: include it in the DBGride) or not however you can't use wildcard character and other filter method features

If the ClientDataSet already filtered then you must change the property to False then True in order to make the filter work properly.

procedure Tfrm_Personnes.EdtSearchChange(Sender: TObject);
begin
  MyClientDataSet.Filtered := False;
  MyClientDataSet.Filtered := True;
end;

procedure TDM_Tableau.cds_ClientsFilterRecord(DataSet: TDataSet;
  var Accept: Boolean);
var
  s, SubStr: string;
begin
  s := DataSet.FieldByName('ID_ClIENT').AsString;
  SubStr := frm_Clients.EdtSearch.Text;
  Accept := Pos(SubStr ,s) > 0;
end;

Filter on integer field

1 Answers1