autocomplete using large lookup table
-
I'm creating Winforms database application using VCS Express 2005 I have some large lookup tables (may be up to 500000 records) which contains name and id and are stored in sql server. I need to create single line combobox style control which: 1. Allows to type first characters in name 2. Auto-completes entered data by using first match 3. Allows to open picklist based by entered data and select name I tried to use Combobox with lookup table. I can set combobox autocomplete source to lookup table and autocomplete window shows matches very well. Lookup parts table is big, it takes a lot of time to load the data source. I think I need virtual combobox control with autocomplete and selection from list. Is is not reasonable to load whole table as combobox lookup table during combobox creation like ms doc sample recommends. I have found 2 possibilities: 1. Add some code to combobox events to implement virtual mode. Is this possible ? 2. Create textbox, selection button and use (virtual?) DataGridView to emulate virtual dropdown list. In this case I must create UI in code also. Which way is better ? Where to find more information about this ?
Andrus
-
I'm creating Winforms database application using VCS Express 2005 I have some large lookup tables (may be up to 500000 records) which contains name and id and are stored in sql server. I need to create single line combobox style control which: 1. Allows to type first characters in name 2. Auto-completes entered data by using first match 3. Allows to open picklist based by entered data and select name I tried to use Combobox with lookup table. I can set combobox autocomplete source to lookup table and autocomplete window shows matches very well. Lookup parts table is big, it takes a lot of time to load the data source. I think I need virtual combobox control with autocomplete and selection from list. Is is not reasonable to load whole table as combobox lookup table during combobox creation like ms doc sample recommends. I have found 2 possibilities: 1. Add some code to combobox events to implement virtual mode. Is this possible ? 2. Create textbox, selection button and use (virtual?) DataGridView to emulate virtual dropdown list. In this case I must create UI in code also. Which way is better ? Where to find more information about this ?
Andrus
Take a look at Ajax: Ajax AutoComplete[^] Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)
-
Take a look at Ajax: Ajax AutoComplete[^] Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)
-
I'm creating Winforms database application using VCS Express 2005 I have some large lookup tables (may be up to 500000 records) which contains name and id and are stored in sql server. I need to create single line combobox style control which: 1. Allows to type first characters in name 2. Auto-completes entered data by using first match 3. Allows to open picklist based by entered data and select name I tried to use Combobox with lookup table. I can set combobox autocomplete source to lookup table and autocomplete window shows matches very well. Lookup parts table is big, it takes a lot of time to load the data source. I think I need virtual combobox control with autocomplete and selection from list. Is is not reasonable to load whole table as combobox lookup table during combobox creation like ms doc sample recommends. I have found 2 possibilities: 1. Add some code to combobox events to implement virtual mode. Is this possible ? 2. Create textbox, selection button and use (virtual?) DataGridView to emulate virtual dropdown list. In this case I must create UI in code also. Which way is better ? Where to find more information about this ?
Andrus
I have implemented a similar function by loading all the data to memory when it's first called for - like you, the time to load if done every time just made the function useless to use. To make sure the data is kept up to date, I have triggers to log updates to another table; the program then checks this table periodically and just reads the data for the updated records. This is not a good approach if a lot of records are frequently updated - the program then spends all it's time reading updates. It's a compromise, but it works for me.
-
I have implemented a similar function by loading all the data to memory when it's first called for - like you, the time to load if done every time just made the function useless to use. To make sure the data is kept up to date, I have triggers to log updates to another table; the program then checks this table periodically and just reads the data for the updated records. This is not a good approach if a lot of records are frequently updated - the program then spends all it's time reading updates. It's a compromise, but it works for me.
Thank you. I think it is better to avoid loading all data, since user actually looks only very small part of data. I think I need a simpler approach: autocomplete reads first match from sql server. Pressing dropdonw button reads first 10 matched from server. Scrolling in dropdown list reads next 10 records. This works probably fast even without caching. Do you have anyidea how to implement such combo ? My major issue is which event I should capture in combobox or how to use virtual grid for this. I havent found any such sample.
Andrus
-
Thank you. I think it is better to avoid loading all data, since user actually looks only very small part of data. I think I need a simpler approach: autocomplete reads first match from sql server. Pressing dropdonw button reads first 10 matched from server. Scrolling in dropdown list reads next 10 records. This works probably fast even without caching. Do you have anyidea how to implement such combo ? My major issue is which event I should capture in combobox or how to use virtual grid for this. I havent found any such sample.
Andrus
I don't use winforms so don't know how to do it with that. Certainly the windows messages can be processed for standard windows, so winforms probably has something similar.