Adding a search that searches data in a DBGrid and temporarily changes what that DBGrid displays- Delphi

Question

Ok so, I'm using a DBGrid that displays data from a database.

The database contains a table which contains 11 columns. Column number 4 is called customer name and contains name of customers.

I'd like a search box (similar to google search bar) where I would write in a client name I want Once I do that the DBGrid should change and display only the rows that contain that client name in the 4th column (column customer names)

Example:

Column names: 

column1 - column2 - column3 - customer name - column5 - column6 - column7 - column8 - column9 - column10 - column11
blabla    blabla    blabla    John Edwards    blabla    blabla    blabla    blabla    blabla    blabla     blabla
blabla1   blabla1   blabla1   Michael Skunk   blabla    blabla    blabla    blabla    blabla    blabla     blabla
blabla2   blabla2   blabla2   John Edwards    blabla2   blabla2   blabla2   blabla2   blabla2   blabla2    blabla2

And so on.. lots of rows, all with different information in every column. At some point I will have in my database different information in the rest of the columns but the same client name in column number 4 (customer name column)

In my example, I already do, as you can see I have 2 entries for John Edwards

Subsequently when I write that customer name in the search field I'd like all those rows to display on the DBGrid while any other row that doesn't contain that name in column number 4 to dissapear

If using the above example, once I write John Edwards in the search field I should only see those 2 rows on the DBGrid

Once I clear the search field of what I wrote, the DBGrid should revert back to it's initial state and display everything, like it was before searching.

Any idea how to do this? I have no clue since I'm rather new to Delphi and searches both here and on Google haven't come up with anything useful / anything I can work with..

I would appreciate any help, thanks a lot!

What do you mean by records? Sorry not native english and I don't want to confuse what you're referring too, so be more specific please, thanks — t1f
Number of records that are in the table that you want to filter? — John Easley
First thing, you should store John and Edwards in separate columns in your table (as Forename and Surname). Then, a simple Sql query on the Surname column will give you a list of the distinct surnames in your table, which you can then display in a gui list to add new entries with the same surname. — MartynA
What you're looking for is TDataSet.Filter and TDataSet.Filtered, which will allow you to temporarily change what is displayed by filtering the data to only display content that matches the filter expression. There are examples both in the documentation and here on SO that show you how to filter data. You don't do it by the DBGrid; you do it by filtering the data itself. — Ken White

Ken White Ken White · Accepted Answer · 2016-10-04T22:19:23

If you're dealing with a small number of rows returned by your SQL SELECT, you can use TDataSet.Filter and TDataSet.Filtered. You can get the input from anywhere you'd like, such as a plain old TEdit.

As you've posted no details (such as the DB controls you're using, the version of Delphi, any code that gives variable names, or anything else), here's a very generic sample that may help. I'm calling the query attached to the DBGrid Qry, because have no idea what else to call it based on what you've posted. FilterRecordsButton and ClearFilterButton are TButtons, and SearchEdit is a TEdit. Feel free to use any control you want to toggle the filter or get the input from the user.

procedure TForm1.FilterRecordsButtonClick(Sender: TObject);
begin
  if SearchEdit.Text <> '' then
  begin
    {
     The brackets around the column name are required because you've got 
     spaces in the name; they're also needed if your column name is a 
     reserved word. QuotedStr puts the necessary quote characters around
     the value.
    }
    Qry.Filter := '[Customer Name] = ' + QuotedStr(SearchEdit.Text);
    Qry.Filtered := True;
    Qry.First;
    FilterRecordsButton.Enabled := False;
    ClearFilterButton.Enabled := True;
  end;
end;

procedure TForm1.ClearFilterButtonClick(Sender: TObject);
begin
  Qry.Filtered := False;
  Qry.Filter := '';
  Qry.First;
  ClearFilterButton.Enabled := False;
  FilterRecordsButton.Enabled := True;
end;

If you're dealing with a large number of rows (SELECT * FROM MyTable without a WHERE that returns a few hundred thousand rows, for instance), then the performance if Filtered may not be acceptable. In that case, you're better off just adding the appropriate WHERE clause to your SELECT and re-opening the query to display only the relevant rows. Of course, you should never be doing a SELECT without a WHERE, so you won't need to do that. :-)

Adding a search that searches data in a DBGrid and temporarily changes what that DBGrid displays- Delphi

1 Answers