>For years we have given the user a pick list of inventory items to select from using a grid in a small form. We just selected the closest inventory item and then ran the form. The user could then browse up and down the list and then select the desired item.
>
>This all worked well even with 20,000 or more recs when we were using .DBF tables and we could just open the table and do a seek(). Does anyone have any idea how this could be done simply using SQL into a cursor? The part that I can't figure out is how to get some range around the selected record. It would really bog the system down to pull 20,000 recs down from the server every time the user hit the pick list button so I would like to grab about 200 or so recs, 100 before and 100 after the selected rec. I thought about using top nnn but that won't give me the 100 recs before the selected one. The part numbers are such that I can’t just grab everything that starts with just the first 2 or so characters. I might get 5 recs or 2000 recs.
How about pulling just the keys into a cursor, ordered by the key itself, and index it by that key. Keep that cursor open for as long as you need. Pulling a single-column cursor of 20000 records shouldn't take long.
When you need a range of keys, just seek() into that cursor, get the key values of 10th preceding and 10th following record, and select from the server anything that's between these two values. So you have the best of both.