Solved

TAdoTable find and find next

Posted on 2010-11-10
15
1,166 Views
Last Modified: 2012-05-10
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


0
Comment
Question by:Looking_4_Answers
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34107102
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]
0
 

Author Comment

by:Looking_4_Answers
ID: 34107408
code please as an example
0
 

Author Comment

by:Looking_4_Answers
ID: 34107424
and can you do partial values?

for example, 'HAM' would match both 'HAMM' and 'HAMMER.'
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34108292

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

0
 
LVL 24

Expert Comment

by:jimyX
ID: 34110180
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

0
 

Author Comment

by:Looking_4_Answers
ID: 34110979
@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
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34111130
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.
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34111715
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.

0
 

Author Comment

by:Looking_4_Answers
ID: 34113919
sorry, replacing the tadotable is not an option, to many other vaiables.
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34114104
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;
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34114677
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

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34114691
@cyberkiwi

Thanks for catching that, i'm just used to zero indexes, too much PChar stuff :)
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34117384
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

0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 34117408
Need to check for filter when findnext is clicked or use current text entered in the search edit
procedure TForm3.btnSeacrhNextClick(Sender: TObject);
var
  Found: Boolean;
begin
  if not ADOTable1.Filtered then
  begin
    if Trim(Edit1.Text) <> '' then
      btnSearchClick(btnSearch)
    else
      ShowMessage('No search was specified');
    Exit;
  end;

  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

0
 
LVL 24

Expert Comment

by:jimyX
ID: 34118274
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question