Delphi 2009 - clientdataset findnearest

Question

I am having an issue with clientdataset and the findnearest method.

I am displaying a number of records in a dbgrid and utilizing the on title click to change the index and sort of the grid. I can also click on a column a second time to change the column from ascending to descending sort. Also, after I click on a column, I have a Tedit box that the user can enter data and the program utilizes the findnearest method to find the nearest match in the column that has been clicked. It does this for ascending or descending indexes.

My problem is, when the index is in descending sequence, the findnearest picks the record after the one that should have been selected.

Here is some data, one set of data is ascending order, the other is descending.

Ascending - Able, Arnold, Arney, Barney, Bubba

Descending - Bubba, Barney, Arney, Arnold, Able

When I search for "A" on the Ascending, the record pointer lands on Able. When I search for "B" on the Descending, the record pointer lands on Arney. My two examples are not theory, this is what is actually happening.

My questions is - in the descending order, why does it skip Bubba and Barney? Please don't give me another example. Please use my data if you are going to make an example. Please give me an explanation why findnearst skips the first two records in the descending sequence that clearly start with a "B".

Answer 1

I can explain why this is the case (hopefully with some clarity) and why it's actually the proper behavior. (The following description will simply discuss single-byte characters for simplicity, but the answer should apply to others as well.)

Given the following data (I've intentionally made them slightly different for easier explanation - you'll see why below, I hope), let's presume we're at the top row (A for ascending order, and Charlie for descending) in both cases.

(Ascending)         (Descending)
LastName            LastName
========            ========
A                   Charlie
Abe                 Bob
Ada                 Ada
B                   Abe
Bob                 A

Starting from the first row (row A) in ascending order and searching for Ac, we'd encounter Abe first. It's not a match, and what we're looking for should be after it in ascending order, so we move on looking for Ac.

It's not there, and we're now at the row where where the match would have been found, which is the row immediately afterward (Ada). In other words, we're positioned at the place the match would have been if it were present (the nearest row).

Now let's say we search for Al. We're at Ada, so we move to the next row, and find there is no Al. We're now at B, where Al would have been found if it were present. In other words, we're at the nearest row. (This can be confirmed with a quick test.)

When we're in descending order, the examples work the same way. (Remember, I've made the data slightly different, to make it easier to explain.) Let's search for Cecil:

We're at the first row, Charlie, and move to the next row looking for Ce (which would be after Charlie in descending order, not before, as shown by Abe and A in the data above). We discover we don't have a match, and we're currently on row Bob, which is where C would have been found if it were present. In other words, in descending order we're at the nearest row, just like it worked in the ascending example.

So there's not a problem in FindNearest. If you want the behavior to be different, you have to implement it in your code (check to see if you've found an exact match, and if not move one row prior if needed, and check to see if it's where you want to be instead).

Answer 2

Okay I'll have a go.

Your basic problem is you are thinking FindNearest should "know" what order the items are in. i.e. the behaviour you want is when the list is in descending order you want FindNearest to start from the bottom of the list and work upwards.

It doesn't, it always works from the top, you can prove this quite easily by giving it an unordered list.

Answer 3

In delphi findnearest method executes GotoNearest and GotoNearest according to delphi documentation; positions the cursor on the record that is either the exact record specified by the current key values in the key buffer, or on the first record whose values exceed those specified.

This makes sense in ascending order, I have two records AU and BU and searching for B. B is greater than AU but less than BU so it places the cursor on the first record whose values exceeds those specified - BU.

But in descending it works differently in terms of outcome. If I have two records BU and AU in descending sequence, and I search for B, the cursor lands on AU. This is confusing to me. BU is greater than B, so why not land on BU?

The reason; The docuentation says that if there is not a match the cursor will be positioned on the first record whose value exeeed those specified. But in fact what gotonearest does is perform a getnext. So, in an ascending index, the getnext is a larger key, but in a descending index the getnext is a smaller key.