using 'Locate' method on a filtered DAO dataset

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?
gragaAsked:
Who is Participating?
 
yk030299Connect With a Mentor Commented:
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
 
yk030299Commented:
was the specified record out of critiria?
a matching record was not found and the cursor is not repositioned
How about FindFirst, Findxxxx....?
0
 
gragaAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
kretzschmarCommented:
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
 
yk030299Commented:
yes, Meikl is right! you should check the value return from locate()!
0
 
gragaAuthor Commented:
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
 
kretzschmarCommented:
to graga,
your TSeachOptions are []?
meikl
0
 
gragaAuthor Commented:
kretzschmar,
u mean TLocateOptions?
it's [loCaseInsensitive, loPartialKey]
0
 
kretzschmarCommented:
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
 
gragaAuthor Commented:
Doesn't make any difference.
In both cases I get the same error.
0
 
gragaAuthor Commented:
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
 
yk030299Commented:
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
 
gragaAuthor Commented:
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
 
yk030299Commented:
??????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
 
gragaAuthor Commented:
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
 
yk030299Commented:
I am glad that the problem is faded. :-)
0
 
gragaAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.