C# Textbox AutoComplete: Limit to ~50 suggestions

Question

I'm working in C# with a textbox that acts as input for a database (Access SQL) record lookup by id number. I want to be able to use AutoComplete on the text box but with some limitations.

The big issue is that the number of ids in the system is on the order of thousands so instead of filling the AutoComplete box once with all of them, I need to monitor what is in the textbox and only show the autocomplete suggestions when there are ~50 or fewer choices.

Currently, I am doing this query on each KeyDown: SELECT count(*) FROM Table WHERE id LIKE 'textbox.text%'

When the count is less than 50 I fill the autocomplete with the results from a SELECT id version of the above statement. This has led me to several problems, most seem to be C# quirks I don't understand.

1) When I clear or add to the AutoCompleteCustomSet within a single KeyDown event, the actual key pressed does not get added to the string (i.e. normal text box input behavior does not occur).

2) I tried separating the AutoCompleteCustomeSet update to a different event (KeyPress or KeyUp) but this either resulted in a crash, or the autocomplete display would only show briefly before being hidden.

I feel like this problem must be common and I am just going about it the wrong way. Can anyone offer some advice? Thanks!

EDIT: this is Windows Forms

EDIT2: A top 50 select doesn't fix the problem that as the user types (and potentially backspaces and re-types) the top 50 will change.

Answer 1

Can't you just SELECT TOP 50 FROM Table WHERE ...?

Answer 2

Would just limiting your suggestions to 50 work for you? When your selecting your suggestions you can use a query like this:

SELECT TOP 50 * FROM YourTable WHERE.....

Answer 3

Did you try the TextChanged event instead ? I would expect that event to be fired AFTER the textbox has been updated, thus avoiding the quirks you mention.

Answer 4

The reason you get crash may be that you get a race condition when replacing the autocomplete source, which is used by a background thread to compute candidates while you are typing (or you put null instead of String.Empty in the candidate list)

Before Windows Vista, the Autocomplete object match candidates with prefix only, so don't populate candidate strings not beginning with the typed string.

Use IAutoCompleteDropDown::ResetEnumerator to reset the candidate list.