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
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
ASKER
code please as an example
ASKER
and can you do partial values?
for example, 'HAM' would match both 'HAMM' and 'HAMMER.'
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
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:
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;
ASKER
@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
"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.
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.
ASKER
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('LastNa me');
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;
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('LastNa
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.
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;
@cyberkiwi
Thanks for catching that, i'm just used to zero indexes, too much PChar stuff :)
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
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Just use the normal FindNext to go to the next record [in this case, the filtered set]