CODEWITHSUNDEEP
delphiado
Yassine Mohammed
Yassine Mohammed

Reputation: 1

How to go to the last record in results of ado.locate (Delphi)

I located some records by this code: 

ADOQuery1.Locate('field1',ADOQuery2.FieldByName('field2').Value,[])

How to go to the last one of these records?

Upvotes: 0

Views: 5508

Answers (2)

Shahram Banazadeh
Shahram Banazadeh

Reputation: 510

If your table has an Autoincrement identity field you can do this

adoquery1.sql.clear;
adoquery1.sql.add('select top 1 * from  yourtablename where field1=value1 and filed2=value2 order by yourAIcolums desc')
adoquery1.execsql;

value1 and value2 are your desired values.pass them as parameters or put them in command text this way you get only row you want and no need to loop

Upvotes: 0

Disillusioned
Disillusioned

Reputation: 14832

You have a number of options. The best depends on a whole lot of considerations you haven't mentioned in your question. I'll provide a very brief overview of the options to avoid this becoming "too broad". It'll be up to you to make your choice and figure out the details. If you get stuck, you can ask a new, more specific question.

Using Locate

A solution involving Locate is only feasible if your dataset is sorted by the same field you're searching on.

Clearly your Search Value is not a unique key. So I'm guessing that you're trying to find the last row matching Search Key in data sorted by some other unique field. (Otherwise the concept of last is meaningless.)

So it's highly probable this is not appropriate for you; unless your data is ordered by a composite key of your search field followed by a unique key.

The approach is simple: navigate forwards until you find a row where the search value doesn't match, then backtrack by 1 row.

if not DataSet.Locate(SearchField, SearchValue, []) then
  { handle not found case as desired }
else
begin
  while (not DataSet.Eof) and (DataSet.FieldByName(SearchField).Value = SearchValue) do
    DataSet.Next;

  { Watch out for case that last row in dataset matches search value }
  if (DataSet.FieldByName(SearchField).Value <> SearchValue) then
    DataSet.Prior;
end;

Implement your own search

This is straight-forward and will always work. But it is inefficient, having O(n) complexity. So not advised for large datasets.

DataSet.Last;
while (not DataSet.Bof) and (DataSet.FieldByName(SearchField).Value <> SearchValue) do
  DataSet.Prior;

NOTE: In order to mirror behaviour of Locate it would be advisable to enhance this method to deal with the case where a match is not found at all. In that case the active record should not be inadvertently changed as a side-effect of the search.

Use filtering

Obviously this solution depends on whether filtering the dataset is appropriate to the rest of your code. But it is a fairly simple option, and depending factors beyond the scope of this answer, it can be more performant than the previous option.

DataSet.Filtered := False;
{ The next line may be a little tricky.
  Ensure the filter string is appropriate for the data-types involved. }
DataSet.Filter := '<string of the form SearchField = SearchValue>';
DataSet.Filtered := True;
DataSet.Last;

See documentation on the Filter property.

NOTE: It may be advisable to take precaution against setting the filter redundantly.

Use a master-detail relationship

This option is included because your question code indicates the SearchValue comes from the active record of another dataset. You're using ADO, so this option is available to you.

DataSet.MasterSource := <Appropriate DataSource>;
DataSet.MasterFields := SearchField;
DataSet.Last;

See documentation on master-detail relationships and on ADO MasterFields.

Offload the work to the RDBMS

Finally, it's worth considering using a stored procedure to get the information you need directly from the database. The advantage is that the server can leverage available indexes and have the potential to provide the most performant option. Again though, a lot depends on the particulars of your application.

A query along the following lines can form the basis of your stored procedure.

select  MAX(UniqueField) as RowKey
from    Table
where   SearchField = SearchValue

Then call your stored procedure, and use its result to find the desired row.

DataSet.Locate(UniqueField, RowKey, []);

NOTE: Don't forget to consider the stored procedure returning NULL if no rows with SearchValue exist.

General Disclaimer

All the above code is extremely brief and for illustrative purposes only. In many cases additional code is required for a robust implementation.
E.g. It might be necessary to DisableControls and enable them again.

NOTE: It's very important with the above to be aware of the actual ordering of the data in your datasets. Failure to take this into account can lead to incorrect behaviour. Even the last option may exhibit worse than expected performance if your dataset is not sorted by UniqueKey.

Upvotes: 7

Related Questions