Solved

using 'Locate' method on a filtered DAO dataset

Posted on 2000-03-05
17
1,521 Views
Last Modified: 2011-10-03
When I use 'Locate' method on a dataset that is not filtered, correct record is returned. However, if I apply a filter to the dataset, 'Locate' returns incorrect record or raises exception 'Record not found'.
I use Delphi 5, Recordset is returned by TDAOStoredProc linked to Access 2000 Query.
Is there a workaround?
0
Comment
Question by:graga
  • 8
  • 6
  • 3
17 Comments
 
LVL 1

Expert Comment

by:yk030299
ID: 2586327
was the specified record out of critiria?
a matching record was not found and the cursor is not repositioned
How about FindFirst, Findxxxx....?
0
 

Author Comment

by:graga
ID: 2586357
The error occurs when there is a record that matches criteria for 'Locate' but it is 'outside' filtered set.

For example:
AA
BB
CC
DD
vvvvvvv filter
EE
FF
GG
^^^^^^^ filter
HH
II

when searching for AA, Locate returns EE
when searching for HH, Locate raises exception.

I didn't have this problem using D3 + ODBC

FindFirst etc, I don't think this will work with ADOStoredProc.


0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2586880
to graga,

did you tried in both cases with such code

if yourdataset.locate(AField,AValue,[]) then
// found
else
// not found

did you tried it, outside the ide?

additional you can put it into try..except blocks like

try
  if yourdataset.locate(AField,AValue,[]) then
  // found
  else
  // not found
except
  // not found or another error
end;

meikl
0
 
LVL 1

Expert Comment

by:yk030299
ID: 2586959
yes, Meikl is right! you should check the value return from locate()!
0
 

Author Comment

by:graga
ID: 2587311
kretzschmar,

The solution with trapping the error through 'except' will only solve the second scenario, ie in my example searching for HH.

My 'Locate' is within the 'if' and in the first case (searching for AA) it returns TRUE.

I have found some more frustrated ppl on newsgroups, using ADO having the same problem.

It must be a bug in ADO. I am thinking of running a parallel query, not filtered, and do a 'Locate' on this query and then try to do something with my filtered proc.

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2587345
to graga,
your TSeachOptions are []?
meikl
0
 

Author Comment

by:graga
ID: 2587825
kretzschmar,
u mean TLocateOptions?
it's [loCaseInsensitive, loPartialKey]
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2587881
ooops sorry,
yes, i meant the TLocateOptions.

whats the result if TLocateOptions are [].

whats the result if TLocateOptions are [loCaseInsensitive].

(seems there is a bug)
(going to recherche)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:graga
ID: 2589692
Doesn't make any difference.
In both cases I get the same error.
0
 

Author Comment

by:graga
ID: 2589879
I have just installed a patch that I received from some group user.
After application, the second error is fixed, however the first error is still there, ie if Locate matches a record that is before the filter it returns TRUE and positions the cursor on the first record in the filtered subset (which of course does not match search criteria)
0
 
LVL 1

Expert Comment

by:yk030299
ID: 2589968
hi, Pls notice:

From Delphi Help, I got
"Ordinarily an application does not call Locate at the TDataSet level, but instead calls a redeclared and implemented FindLast in a descendant object such as TTable. "
&
"Implements a virtual method for positioning the cursor on the first record in a FILTERED dataset."
0
 
LVL 1

Accepted Solution

by:
yk030299 earned 100 total points
ID: 2589974
My idea is that U may implement searching by yourself.
E.g.

With Table1 do begin
  findFirst;
  repeat
    if <<conditions>> then .....; //found //exit;
  until findNext=false;
end;
0
 

Author Comment

by:graga
ID: 2590031
yk

i have tried your suggestion, this is my code:

        with frmData.dsAssetList.Dataset do begin
             SavePlace := GetBookmark;
             DisableControls;
             iSelStart := txtLookFor.SelStart;
             flgDone := False;
             FindFirst;
             repeat
                   case cboSearchIn.ItemIndex of
                        0: if UpperCase(copy(FieldByName('AssetList_ID').AsString,1,length(txtLookFor.Text))) = UpperCase(txtLookFor.Text) then
                              flgDone := True;
                        1: if UpperCase(copy(FieldByName('AssetList_Description').AsString,1,length(txtLookFor.Text))) = UpperCase(txtLookFor.Text) then
                              flgDone := True;
                        end;
                   if not flgDone then
                      Next;
                   until eof or flgDone;
             if flgDone then begin
                txtLookFor.SelStart := iSelStart;
                txtLookFor.SelLength := 100;
                end
             else
                GoToBookmark(SavePlace);
             FreeBookmark(SavePlace);
             EnableControls;
             end;

It works like a champion. As you see, I am trying to implement incremental search and the routing is executed on KeyUp. I have tested this on a small database with some 20 records and speed is okay.

Just before I give you points I will generate some larger datafile and check the performance.

thanx
0
 
LVL 1

Expert Comment

by:yk030299
ID: 2590070
??????if not flgDone then Next;
why Next? It should be FindNext, OR it will be time-waste!
Because FindNext will skip some filtered records.
0
 

Author Comment

by:graga
ID: 2590072
okay, on database with 10K records it is strugling a bit, but I think I found a solution;

Based on Filtered property I will use either 'Locate' or FindFirst ... loop.

Since my application has an average database size < 1000 records, any filtered set will be less than that and performance on that set is okay.

thanx yk, I'm happy, you got the points
0
 
LVL 1

Expert Comment

by:yk030299
ID: 2590135
I am glad that the problem is faded. :-)
0
 

Author Comment

by:graga
ID: 2590150
yes, locate for non-filtered set and findfirst+ for filtered set work perfectly together.
strangely, FindNext seems to take much longer than Next... I'll keep testing.
thanx again and let MYOB be with you :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
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.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 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

19 Experts available now in Live!

Get 1:1 Help Now