• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

DB search records

To search through DB records I use this:

if Edit1.Text <> '' then
 ADOQuery1.Locate('Name', Edit1.Text,  [loCaseInsensitive, loPartialKey]);

This is On change event of the edit box.So when I begin typing my grid scrolls accordingly
to the matching entry.Works fine...
The problem is this:
When my grid displays the result of my search (I have entered a full name) how am I to continue searching
through my DB records?Say the match is right but it is not the right person...i.e I am looking another person with the same name...How am I to go arround this problem as there are many same names and surnames?
I had in mind to put a button next to edit with "new search" caption on it so when the name
is in the edit and I hit this button I get taken to the next person with the same name.
How am I to do this?
0
Quake
Asked:
Quake
  • 10
  • 4
  • 4
  • +5
1 Solution
 
kretzschmarCommented:
maybe you could use a filter instead of locate
or in combination
0
 
jswebyCommented:
It would probably be a good idea to sort the results in the grid by the field you are searching by, so that when you have entered a full name, all the people with that name are lumped together and are therefore easier to find.

As kretzschmar has said, if you put a filter (or SQL query) on the table when the name is being entered then you will narrow down the result set to only those whose names contain the characters entered.

The problem you are having is because FindKey, Locate and Lookup will all give you the first instance they find of a particular search match, starting from the top of the table, which means that it is difficult to do an equivalent to "Find Next".

J.
0
 
meirchenCommented:
A filter on the table component (with the onfilter event) might be the best way to handle this, offcourse you won't see the other non-matching records.

In the Onfilter you can use MatchesMask() to check whether the name matches the filter.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
QuakeAuthor Commented:
I have seen such thing done,i.e "find next" (something like searching through windows registry with F3 or in Access find) where onclick of button db grid displays next same entry i.e it keeps jumping from record to record as long as the name parameter is the same...
0
 
kretzschmarCommented:
such a simulation may possible,

just add a query instance, which includes the unique-id and as whereclause your user input with an upper function, to get it caseinsensitive.

if you step forward (next) through the resultset of the query, you could do a locate on the dataset which is displayed in the grid

meikl ;-)
0
 
gemartiCommented:
The command you are looking for is LocateNext('Name', Edit1.Text,  [loCaseInsensitive, loPartialKey])
0
 
geobulCommented:
There is no LocateNext supported by TADOQuery (D5 Ent).

Regards, Geo
0
 
QuakeAuthor Commented:
You are right GEO,no such support for ADOQueery LocateNext.
But the idea is good though...
Meikl,mind elaborating a little i.e step instructions?Not very PRO at this,hugh...
MEIR - I would like to see the rest of records.
JSWEBY - no filtering else this gets too complicated.
0
 
kretzschmarCommented:
i just got not the time for a more PRO work.
take it as a possible hint :-)

filtering would be easier,
well,l you may not have the effect of jumping (through your dataset),
more an effect of shrinking (of your dataset)

meikl ;-)
0
 
gemartiCommented:
Okay locateNext is not part of the ADOQuery but if you just use

Next;

What happens?
0
 
gemartiCommented:
Or better yet : right from the help file:

Provides access to a secondary recordset.

function NextRecordset(var RecordsAffected: Integer): _Recordset;

Description

Call NextRecordset in situations where multiple recordsets are returned to provide access to the second (and subsequent) recordsets. The ADO Recordset object returned by NextRecordset must be assigned to the Recordset property of an ADO dataset component, such as TADODataSet, to be accessible.

ADODataSet1.Recordset := ADOQuery1.NextRecordset

RecordsAffected is the number of rows in the returned recordset.
0
 
VSFCommented:
U could use some SQL to do the task, and filter the records that match the typed string.
This is how I did it on my apps:

Keep your TDBgrid and your TEdit
Add a Tquery and link it properly

On the Onchange Event add the following:

procedure TForm1.Edit1Change(Sender: TObject);
begin
with Query1 do      
 begin
  close;
  sql.clear;
  sql.add('Select * from MYDATABASE where UPPER(MYFIELD) LIKE UPPER (''%'' + :1 + ''%'')');
  params[0].asstring:=Form1.edit1.text;
  open;
 end;
end;

This should do it!

Hope this helps
VSF
www.victory.hpg.com.br
UIN:14016999
0
 
jordimCommented:
Do you have the last update of d5 and bde?....some problems associated to ado are solved in the update.
(I'had problems with master/detail, for example)
Also...i'd like much more to build the query (with clause "where ......"), instead to use filters and searches...Is faster and better....
I hope to help you.
0
 
kretzschmarCommented:
courious answer
0
 
QuakeAuthor Commented:
I shall try VSF suggestion and that of GEMARTI.Tell you later how it goes...However I must refuse this answer as
it is more of a hint than an answer.I need an answer.
0
 
kretzschmarCommented:
listening ...

VSF is near on the solution i have in mind ...
0
 
kretzschmarCommented:
>the solution i have in mind ...
well, thats not PRO enough for you :-)
0
 
VSFCommented:
Note that the ''%''  are used as SQL wildcards
In this case I used then before and after the typed string,
but you could also add some options with radio buttons like:
"Match records that begin with the typed string"
"Match records that have typed string in any position"
etc....

The code should look like:

procedure TForm1.Edit1Change(Sender: TObject);
begin
with Query1 do      
begin
 close;
 sql.clear;
 sql.add('Select * from MYDATABASE where UPPER(MYFIELD) LIKE UPPER (')
 if checkbox1.checked then
  sql.add('"%" + ');
 sql.add(':1');  
 if checkbox2.checked then
  sql.add(' + "%"');
 sql.add(')');
 params[0].asstring:=Form1.edit1.text;
 open;
end;
end;

Hope this helps!

VSF
www.enge.cjb.net
www.victory.hpg.com.br

0
 
QuakeAuthor Commented:
No,this is not it!
I do not want to alter any SQL strings but just jump through existing records with edit text as the basics.
VSF
[Error] Unit26.pas(450): Undeclared identifier: 'params'
GEMARTI
Okay locateNext is not part of the ADOQuery but if you just use
Next;
What happens? Norhing - I get too many actual param. error.
Of your second example
:function NextRecordset(var RecordsAffected: Integer): _Recordset;
How is the code supposed to look like?Where do I stick that function.,
Please a simple detailed answer...
As a matter of fact I use radiobuttons for my search:
if Edit3.Text <> '' then
case RadioGroup1.itemIndex of
 0 : ADOQuery2.Locate('Surname', Edit3.Text,  [loCaseInsensitive, loPartialKey]);

 1 : ADOQuery2.Locate('Name', Edit3.Text,  [loCaseInsensitive, loPartialKey]);

So how on earth do I jump from name/surname to name/surname in the same queery???
0
 
kretzschmarCommented:
you could just do a loop with next until the next matching record appears :-)

or just using my not PRO hint :-)
0
 
kretzschmarCommented:
well,

a sample
(as i don't use ado, a sample with paradox using the dbdemos and the table employee)

you must transform self this sample to ado-syntax

unit loc_with_q_u;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Db, DBTables, Grids, DBGrids;

type
  TForm1 = class(TForm)
    Edit1: TEdit;
    DBGrid1: TDBGrid;
    Query1: TQuery;  //holds select * from employee
    Query2: TQuery;  //holds a input depnded subselect
    DataSource1: TDataSource;
    Button1: TButton;
    Button2: TButton;
    procedure Edit1Change(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.Edit1Change(Sender: TObject);
const sql = 'Select Empno from Employee where Upper(LastName) Like %s';
begin
  query2.close;  //lookup if there are records
  query2.sql.text := Format(sql,[QuotedStr(UpperCase(edit1.text+'%'))]);
  query2.Open;
  If query2.IsEmpty then
    ShowMessage('No Match Found')
  else  //if yes locate the first match
    query1.Locate('EmpNo',query2.FieldByName('EmpNo').Value,[]);
end;


//locate next match
procedure TForm1.Button1Click(Sender: TObject);
begin
  query2.Next;
  if Query2.Eof then
    ShowMessage('Last match')
  else
    query1.Locate('EmpNo',query2.FieldByName('EmpNo').Value,[]);
end;

//locate prior match
procedure TForm1.Button2Click(Sender: TObject);
begin
  query2.Prior;
  if Query2.Bof then
    ShowMessage('First match')
  else
    query1.Locate('EmpNo',query2.FieldByName('EmpNo').Value,[]);
end;

end.

regards

meikl ;-)
0
 
gemartiCommented:
Surname query
  Select * from DataTable Where Surname = :Sname

Name Query
  Select * from DataTable WHERE Name = :Sname

Right?

The ADODataset can function as a query component...
Here is an example:


procedure TForm1.RadioGroup1Click(Sender: TObject);
var
  UName : Variant;
begin
  Case RadioGroup1.ItemIndex of
    0:
      begin
        ADODataSet1.Parameters.ParamByName('UName').Value := Edit1.Text;
        ADODataSet1.CommandText := 'Select * From Assignments WHERE [User Name]= :UName';
        ADODataSet1.Active := True;
      end;
    1:
      begin
        ADODataSet1.Parameters.ParamByName('UName').Value := Edit1.Text;
        ADODataSet1.CommandText := 'Select * From Assignments WHERE [Last Name]= :UName';
        ADODataSet1.Active := True;
      end;
    end;
end;


Next Button

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADODataSet1.Next;
end;



0
 
kretzschmarCommented:
hmm gem,
this would have the same effect as to use a filter
(a shrinked dataset)

meikl ;-)
0
 
kretzschmarCommented:
as it is
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 10
  • 4
  • 4
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now