Recent

Author Topic: [SOLVED] How to filtering a grid with an editbox  (Read 12630 times)

dquinter

  • New member
  • *
  • Posts: 7
[SOLVED] How to filtering a grid with an editbox
« on: August 03, 2017, 09:05:13 pm »
Hello everyone,
I’m new to Lazarus (And Pascal) and i think i have an easy problem, but since I’m new, I have no idea how to fix it.

I’m trying to make an application for a database made in Firebird, so I have a TZConnection (I'm using Zeos), my TZQuery, which is a select * from table, and my TDataSource linked to my TZQuery.

My problem is filtering of a grid with the help of an editbox.
I want to filter the grid, with what I type into the editbox, so when I type in someting like "LAZ", the grid only shows the records with "LAZ" in the field I’m searching in, foe example, a record with LAZARUS in the text.

So I’m thinking of an onchange event for the editbox, but after that I’m lost.

Please help.  :) :) :)
« Last Edit: August 11, 2017, 09:00:15 pm by dquinter »

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: How to filtering a grid with an editbox
« Reply #1 on: August 04, 2017, 08:35:11 am »
sourcecode could be handly.

How do you filter your dbgrid (actually dataset) in your code.
Do you recall the query with a where clause or with the property filter?
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

balazsszekely

  • Guest
Re: How to filtering a grid with an editbox
« Reply #2 on: August 04, 2017, 10:11:16 am »
@dquinter
Quote
Hello everyone,
I’m new to Lazarus (And Pascal) and i think i have an easy problem, but since I’m new, I have no idea how to fix it.

I’m trying to make an application for a database made in Firebird, so I have a TZConnection (I'm using Zeos), my TZQuery, which is a select * from table, and my TDataSource linked to my TZQuery.

My problem is filtering of a grid with the help of an editbox.
I want to filter the grid, with what I type into the editbox, so when I type in someting like "LAZ", the grid only shows the records with "LAZ" in the field I’m searching in, foe example, a record with LAZARUS in the text.

So I’m thinking of an onchange event for the editbox, but after that I’m lost.

1. Create OnChange event for the Edit
2. Create OnFilterRecord event for the Query
3. Don't forget to change "YOURFIELD"
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Edit1Change(Sender: TObject);
  2. begin
  3.   if Trim(Edit1.Text) <> '' then
  4.   begin
  5.     if not SQLQuery1.Filtered then
  6.       SQLQuery1.Filtered := True;
  7.     SQLQuery1.Refresh;
  8.   end
  9.   else
  10.     SQLQuery1.Filtered := False;
  11. end;
  12.  
  13. procedure TForm1.SQLQuery1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
  14. begin
  15.   Accept := Pos(UpperCase(Edit1.Text), UpperCase(DataSet.FieldByName('YOURFIELD').AsString)) > 0;
  16. end;
« Last Edit: August 04, 2017, 12:12:24 pm by GetMem »

fiazhnd

  • New Member
  • *
  • Posts: 36
Re: How to filtering a grid with an editbox
« Reply #3 on: August 05, 2017, 04:59:59 pm »
Code: Pascal  [Select][+][-]
  1. procedure TForm1.EditSearchChange(Sender: TObject);
  2. begin
  3.  if EditSearch.Text='' then SQLQuery1.Filtered:=false else
  4.  begin
  5.    SQLQuery1.Filtered:=false;
  6.     SQLQuery1.Filter:=' Name LIKE '+ QuotedStr('*'+EditSearch.Text+'*') +
  7.                      'OR CName LIKE '+ QuotedStr('*'+EditSearch.Text+'*') +
  8.                      'OR City LIKE '+ QuotedStr('*'+EditSearch.Text+'*')+
  9.                      'OR Phone LIKE '+ QuotedStr('*'+EditSearch.Text+'*')+
  10.                      'OR Car LIKE '+ QuotedStr('*'+EditSearch.Text+'*');
  11.   SQLQuery1.Filtered:=true;
  12.  end;
  13.  end;
  14.  
« Last Edit: August 05, 2017, 05:03:09 pm by fiazhnd »

dquinter

  • New member
  • *
  • Posts: 7
Re: How to filtering a grid with an editbox
« Reply #4 on: August 11, 2017, 08:56:52 pm »
sourcecode could be handly.

How do you filter your dbgrid (actually dataset) in your code.
Do you recall the query with a where clause or with the property filter?

I did someting like this:

Code: Pascal  [Select][+][-]
  1.   procedure TfrmClientes.btnBuscarClick(Sender: TObject);
  2.   begin
  3.     DClientes.Close;
  4.     DClientes.SQL.Clear;
  5.     DClientes.SQL.Add('select * from CLIENTES');
  6.     DClientes.SQL.Add('where upper(NOME) like :BUSCAR');
  7.     DClientes.SQL.Add('order by NOME');
  8.     DClientes.ParamByName('BUSCAR').AsString := UpperCase(Format('%s%s%s',['%', Trim(edtBuscar.Text),'%']));
  9.     DClientes.Prepare;
  10.     DClientes.Open;
  11.   end;
  12.  

But I'll try the other methods than GetMem and fiazhnd told me.

Thanks to all! :)

majid.ebru

  • Sr. Member
  • ****
  • Posts: 494
Re: How to filtering a grid with an editbox
« Reply #5 on: March 06, 2018, 07:49:19 pm »
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Edit1Change(Sender: TObject);
  2. begin
  3.   if Trim(Edit1.Text) <> '' then
  4.   begin
  5.     if not SQLQuery1.Filtered then
  6.       SQLQuery1.Filtered := True;
  7.     SQLQuery1.Refresh;
  8.   end
  9.   else
  10.     SQLQuery1.Filtered := False;
  11. end;
  12.  
  13. procedure TForm1.SQLQuery1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
  14. begin
  15.   Accept := Pos(UpperCase(Edit1.Text), UpperCase(DataSet.FieldByName('YOURFIELD').AsString)) > 0;
  16. end;

Hi

@GetMem  Thank you for your codes
« Last Edit: March 06, 2018, 07:51:03 pm by majid.ebru »

Bobito

  • New Member
  • *
  • Posts: 21
Re: [SOLVED] How to filtering a grid with an editbox
« Reply #6 on: October 25, 2018, 07:36:41 pm »
@GetMem
Amazing; just a few lines of code make marvels !!!
Where do I have to study to get that far?

I am a newbie in Lazarus. I am already using you code. Before I read this thread I had a messy onKeyPress procedure and never got it to work.

Thanks a lot.

p.s. Again. Where should I study to get that far?
« Last Edit: October 26, 2018, 03:02:41 am by Bobito »

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: [SOLVED] How to filtering a grid with an editbox
« Reply #7 on: October 26, 2018, 09:50:49 am »
Quote
p.s. Again. Where should I study to get that far?
Trying, practising, reading....
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

Bobito

  • New Member
  • *
  • Posts: 21
Re: [SOLVED] How to filtering a grid with an editbox
« Reply #8 on: February 06, 2019, 10:52:23 pm »
mangakissa you are very rigth !!!
Trying, practicing and reading
Definitely I have to follow your advice.

Soner

  • Sr. Member
  • ****
  • Posts: 305
Re: How to filtering a grid with an editbox
« Reply #9 on: February 07, 2019, 12:06:26 am »
....

I did someting like this:

Code: Pascal  [Select][+][-]
  1. ...
  2.     DClientes.SQL.Add('where upper(NOME) like :BUSCAR');
  3. ...
  4.  
...

 @dquinter:
you don't need to use upper(NOME) when you define your string fields as collate unicode _ci something like this:

Code: SQL  [Select][+][-]
  1. NOME VARCHAR(yourfieldlength) CHARACTER SET UTF8 COLLATE UNICODE_CI,
  2.  

 

TinyPortal © 2005-2018