Solved

using 'Locate' method on a filtered DAO dataset

Posted on 2000-03-05
17
1,554 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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
 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to use Hashing sha1 in Delphi 2010 4 269
Machine not responding during CopyFile() 3 98
oracle global variables 4 68
Twebbrowser add css to the header 3 25
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

770 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