Link to home
Start Free TrialLog in
Avatar of Looking_4_Answers
Looking_4_Answers

asked on

TAdoTable find and find next

i have a TEdit and two tbuttons (find , find next)

I need  search functionality to find the first occurrence in the table - of a record that matches whats in the TEdit box

then when the find next button is clicked, to find the next occurrence, and so on to the end of the table

thanks


Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

You can apply the tedit.text as a filter to the TADOTable, then fire FindFirst to get the first match.
Just use the normal FindNext to go to the next record [in this case, the filtered set]
Avatar of Looking_4_Answers
Looking_4_Answers

ASKER

code please as an example
and can you do partial values?

for example, 'HAM' would match both 'HAMM' and 'HAMMER.'

First filter the table on the field you are searching
Table1.Filter := Format('LastName=%s', [QuotedtStr(Edit1.Text)]);
Navigate to first record;

For find next just call Table1.FindNext;
Remember FindFirst and FindNext is only useful when there is a filter

For a partial search, you need to use
Table1.Locate(FieldName, FieldValue, [loPartialKey])

Here's an example
unit Unit3;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, Grids, DBGrids, ADODB, DBTables;

type
  TForm3 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    ADOConnection1: TADOConnection;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Edit1: TEdit;
    ADOTable1: TADOTable;
    Button3: TButton;
    Button4: TButton;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
  end;

var
  Form3: TForm3;

implementation

{$R *.dfm}

procedure TForm3.Button1Click(Sender: TObject);
var
  Found: Boolean;
begin
  try
    Found := ADOTable1.FindNext;
  except
    Found := False
  end;
  if not Found then
    ShowMessage('Record not found');
end;

procedure TForm3.Button2Click(Sender: TObject);
begin
  ADOTable1.DisableControls;
  ADOTable1.Filtered := False;
  ADOTable1.Filter := Format('LastName = %s', [QuotedStr(Edit1.Text)]);
  ADOTable1.Filtered := True;
  ADOTable1.First;
  ADOTable1.EnableControls;
end;

procedure TForm3.Button3Click(Sender: TObject);
begin
  if not ADOTable1.Locate('LastName', Edit1.Text, [loPartialKey, loCaseInsensitive]) then
    ShowMessage('No marching record found');
end;

procedure TForm3.Button4Click(Sender: TObject);
begin
  if ADOTable1.Filtered then
    ADOTable1.Filtered := False
  else
    ShowMessage('No filter set');
end;

procedure TForm3.FormCreate(Sender: TObject);
begin
  ADOConnection1.Connected := True;
  ADOTable1.Active := True;
end;

end.


//form
object Form3: TForm3
  Left = 0
  Top = 0
  Caption = 'Form3'
  ClientHeight = 376
  ClientWidth = 447
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 105
    Top = 343
    Width = 75
    Height = 25
    Caption = 'Search Next'
    TabOrder = 0
    OnClick = Button1Click
  end
  object Button2: TButton
    Left = 24
    Top = 343
    Width = 75
    Height = 25
    Caption = 'Search'
    TabOrder = 1
    OnClick = Button2Click
  end
  object DBGrid1: TDBGrid
    Left = 24
    Top = 24
    Width = 403
    Height = 281
    DataSource = DataSource1
    TabOrder = 2
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'Tahoma'
    TitleFont.Style = []
  end
  object Edit1: TEdit
    Left = 24
    Top = 312
    Width = 403
    Height = 21
    TabOrder = 3
  end
  object Button3: TButton
    Left = 186
    Top = 343
    Width = 81
    Height = 25
    Caption = 'Partial Search'
    TabOrder = 4
    OnClick = Button3Click
  end
  object Button4: TButton
    Left = 273
    Top = 343
    Width = 75
    Height = 25
    Caption = 'Un-Filter'
    TabOrder = 5
    OnClick = Button4Click
  end
  object ADOConnection1: TADOConnection
    LoginPrompt = False
    Provider = 'SQLOLEDB.1'
    Left = 104
    Top = 120
  end
  object DataSource1: TDataSource
    DataSet = ADOTable1
    Left = 288
    Top = 120
  end
  object ADOTable1: TADOTable
    Connection = ADOConnection1
    TableName = 'Employees'
    Left = 184
    Top = 120
  end
end

Open in new window

It's better if you use SQL TAdoquery so you will benefit of the functionality and the enhancement of the SQL (unless your requirement is TADOTable):

Let's say your table is "Table1" and you look up in field "Name", so you can get all the records that start with 'HAM' in field name by using the wildcard, as follows:

Adoquery.SQL.text := 'Select * from YourTable T where T.Name like '+ QuotedStr(Edit1.Text+'%') +' order by Name';

This will get you all the records that exactly equal to or start with whatever in the Edit Box.

You always check first to see if the database is opened and is not empty otherwise that should be properly handled before executing the code in the buttons such as, if the database is empty then show message "no records found".

So the code of the Find and FindNext buttons will be:




// At button Find onclick
procedure TForm1.ButtonFindClick(Sender: TObject);
begin
  Adoquery.Close
  Adoquery.SQL.text := 'Select * from YourTable T where T.Name like '+ QuotedStr(Edit1.Text+'%') +' order by Name';
  Adoquery.Open;

  if Adoquery.RecordCount <= 0 then
    Showmessage(Edit1.text + ' is not found!')
  else
    Showmessage(Adoquery.FieldbyName('Name').AsString);
end;

// At button Find Next onclick
procedure TForm1.ButtonFindNextClick(Sender: TObject);
begin
  if Adoquery.RecordCount <= 0 then
    showmessage('no records found')
  else if Adoquery.RecNo = Adoquery.RecordCount then
         showmessage('Reached the end of the Database!')
       else
         begin
           Adoquery.Next;
           showmessage(Adoquery.FieldByName('Name').AsString);
         end;
end;

Open in new window

@ewangoya:

"For a partial search, you need to use
Table1.Locate(FieldName, FieldValue, [loPartialKey])"

A partial using locate will not allow me to do a Find Next

@jimyX

I thought of using SQL, but because of my TADOTable based screens, i would have to display the results in another screen - right?

Or can i apply this SQL to the current table (like a filter) and then turn it off when needed? If so, how
Normally SQL is more powerful than tables and the flexibility of the quires gives you more control over your data than tables. So, TADOTable could be replaced, easily, by SQL.
jimyx is absolutely right, remember using TADOTable loads all the data from your table and this may sometimes be alot and not desireable.
You can simply replace the TADOTable with TADOQuery without changing anything else much. You don't have to use any other screen, just run the query as jimyx shows in the code snippet.

Remember to enable and disable controls when changing your query otherwise the screen flickers.

@jimyx
You can just test for ADOQuery1.IsEmpty instead of looking at the record count and check for
ADOQuery1.Eof to detect if you can't move to the next record instead of comparing RecordCount to current record no.

sorry, replacing the tadotable is not an option, to many other vaiables.
In that case, implementing you have to implement your own FindNext using the partial key
It should not be difficult but you cant take advantage of the Indexes of the TADOTable.

On button click for FindNext, check if the record set is not empty, run a while loop and on each record compare the current field with your search key

ie
var
  SearchField: TField;
FoundRecord: Boolean;
begin
  Foundrecord := False;
  SearchField := ADOTable.FindField('LastName');
  ADOTable1.DisableControls;
  while not ADOTable1.Eof do
  begin
     FoundRecord := Pos(Edit1.Text, Field.AsString) = 0;
     if FoundRecord then
       Break;
  end;
  ADOTable1.EnableControls;
  if not Foundrecord then
     ShowMessage('Record not found');
end;        
     end;
On ewangoya's last post, it makes more sense if it is

     FoundRecord := Pos(Edit1.Text, Field.AsString) = 1;  // found at the beginning

0 = not found
It is also using 2 names for the same thing ... SearchField / Field

Try this.

FWIW, a TADOTable does not bring in the entire table. If it is set to keyset, all it does is place server side cursor that moves one record at a time.  So TADOTable.Open merely retrieves the first record.

My first comment assumed that you are not displaying data in a grid, in which case a

.Filter := 'SomeColumn LIKE ' + Edit1.Text + '*';

will perform a partial match.  You can allow the user to control partial/full match by just using

.Filter := 'SomeColumn LIKE ' + Edit1.Text;

They can add the * to make it partial.
If the data is displayed in a grid, obviously you don't want to lose sight of all the other records, and only want to focus on the found record.  Both options are presented below.
procedure TForm1.ButtonFindClick(Sender: TObject);
begin
  ADOTable1.Filtered := True;
// this code perform partial matches only
// to allow the user to add/omit the *, some changes are needed here
  ADOTable1.Filter := .Filter := 'SomeColumn LIKE ' + Edit.Text + '*';
  ADOTable1.First;
  If Pos(LowerCase(Edit1.Text), LowerCase(ADOTable1.FieldByName('SomeColumn').AsString) <> 1 then
     ShowMessage('No match found');
end;

procedure TForm1.ButtonFindNextClick(Sender: TObject);
begin
  if not ADOTable1.Filtered then begin
    ButtonFindClick(Sender); // do an initial find
    exit;
  end;
  ADOTable1.Next; // next in the filtered set
  If ADOTable1.EOF then
     ShowMessage('No more matches found');
end;

============================= without filtering =============================

procedure TForm1.ButtonFindClick(Sender: TObject);
begin
  ADOTable1.First;
  // if first record is already a match, leave it be
  If not Pos(LowerCase(Edit1.Text), LowerCase(ADOTable1.FieldByName('SomeColumn').AsString) <> 1 then
    ButtonFindNextClick(Sender);
end;

procedure TForm1.ButtonFindNextClick(Sender: TObject);
var
  AField: TField;
begin
// this code can be improved by taking a bookmark and returning to it if there
// are no more matches. in the current form, it puts the cursor on the last
// record when there are no more matches

  AField := ADOTable1.FieldByName('SomeColumn');
  while not ADOTable1.EOF and Pos(LowerCase(Edit1.Text), LowerCase(AField.AsString) <> 1 do
    ADOTable1.Next; // keep looking
  if ADOTable1.EOF then
    ShowMessage('No more matches found');
end;

Open in new window

@cyberkiwi

Thanks for catching that, i'm just used to zero indexes, too much PChar stuff :)
Improving on Cyberkiwi's entry
You actually don't need to code the partial search at all since the LIKE key word is a valid filter as pointed out by cyberkiwi

Here's the solution
procedure TForm3.btnSeacrhNextClick(Sender: TObject);
var
  Found: Boolean;
begin
  try
    Found := ADOTable1.FindNext;
  except
    Found := False
  end;
  if not Found then
    ShowMessage('Record not found');
end;

procedure TForm3.btnSearchClick(Sender: TObject);
begin
  ADOTable1.DisableControls;
  ADOTable1.Filtered := False;
  ADOTable1.Filter := Format('LastName = %s', [QuotedStr(Edit1.Text)]);
  ADOTable1.Filtered := True;
  ADOTable1.First;
  ADOTable1.EnableControls;
end;

procedure TForm3.btnPartialSearchClick(Sender: TObject);
begin
  ADOTable1.DisableControls;
  ADOTable1.Filtered := False;
  ADOTable1.Filter := Format('LastName LIKE %s', [QuotedStr(Edit1.Text + '%')]);
  ADOTable1.Filtered := True;
  ADOTable1.First;
  ADOTable1.EnableControls;
end;

procedure TForm3.btnUnfilterClick(Sender: TObject);
begin
  if ADOTable1.Filtered then
    ADOTable1.Filtered := False
  else
    ShowMessage('No filter set');
end;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
TAdoquery can work side by side with the TAdotable, so you can do what you have to do with the TAdotable (data projection) and when you want to search the records (find and find next) use the SQL in TAdoquery.