Solved

DB search records

Posted on 2001-07-24
24
457 Views
Last Modified: 2010-04-04
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
Comment
Question by:Quake
  • 10
  • 4
  • 4
  • +5
24 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6312121
maybe you could use a filter instead of locate
or in combination
0
 
LVL 4

Expert Comment

by:jsweby
ID: 6312193
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
 

Expert Comment

by:meirchen
ID: 6312526
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
 

Author Comment

by:Quake
ID: 6312657
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6312837
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
 
LVL 21

Expert Comment

by:gemarti
ID: 6313068
The command you are looking for is LocateNext('Name', Edit1.Text,  [loCaseInsensitive, loPartialKey])
0
 
LVL 17

Expert Comment

by:geobul
ID: 6313319
There is no LocateNext supported by TADOQuery (D5 Ent).

Regards, Geo
0
 

Author Comment

by:Quake
ID: 6313902
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6314050
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
 
LVL 21

Expert Comment

by:gemarti
ID: 6314059
Okay locateNext is not part of the ADOQuery but if you just use

Next;

What happens?
0
 
LVL 21

Expert Comment

by:gemarti
ID: 6314069
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
 
LVL 3

Accepted Solution

by:
VSF earned 300 total points
ID: 6314303
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Expert Comment

by:jordim
ID: 6316234
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6316253
courious answer
0
 

Author Comment

by:Quake
ID: 6316527
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6316548
listening ...

VSF is near on the solution i have in mind ...
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6316567
>the solution i have in mind ...
well, thats not PRO enough for you :-)
0
 
LVL 3

Expert Comment

by:VSF
ID: 6316863
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
 

Author Comment

by:Quake
ID: 6317744
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6318012
you could just do a loop with next until the next matching record appears :-)

or just using my not PRO hint :-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6318114
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
 
LVL 21

Expert Comment

by:gemarti
ID: 6318175
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6318307
hmm gem,
this would have the same effect as to use a filter
(a shrinked dataset)

meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6322318
as it is
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 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

12 Experts available now in Live!

Get 1:1 Help Now