tcsh tcsh - 1 year ago 94
MySQL Question

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

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)


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!

Answer Source

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);
  if SearchEdit.Text <> '' then
     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;
    FilterRecordsButton.Enabled := False;
    ClearFilterButton.Enabled := True;

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

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. :-)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download