Solved

TAdoTable find and find next

Posted on 2010-11-10
15
1,107 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
  • 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
 
LVL 32

Expert Comment

by:ewangoya
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 32

Expert Comment

by:ewangoya
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:ewangoya
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:ewangoya
ID: 34114691
@cyberkiwi

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

Expert Comment

by:ewangoya
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:
ewangoya 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now