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.