Autocomplete with big lookup table

[Andrus]

I'm creating a database Winforms 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.

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

>
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 ?

500K rows are WAY too much, you need to look for another way of displaying
the info.

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

I think what you want to do here is handle the keypress event, and after
there are at least 2 or 3 letters typed, you would use this as a filter in
the WHERE clause of your SQL Statement to return a small matching subset, and
bind this to your control.
There are a number of implementations of this for both Windows Forms and
ASP.NET, you can find them easily with a well-constructed google or live.com
search.
Peter

--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net


"Andrus" wrote:

I'm creating a database Winforms 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.

[pfc_sadr]

go to the first day of the next month and subtract one.

it's really not that complex
Public Function DaysLeftInMonth(DIn as Date) as Integer
'Dim DOut as Date
'DOut = DateSerial(Month(DIn), 1, Year(Din) - 1
'DaysLeftInMonth = DateDiff(d, DIn, Dout)
DaysLeftInMonth = DateDiff(d, DIn, DateSerial(Month(DIn), 1, Year(Din)
- 1)
End Function
On Feb 15, 11:48 am, "Andrus" <[e-mail]:

I'm creating a database Winforms 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.

[Andrus]

500K rows are WAY too much, you need to look for another way of

displaying the info.

Thank you.
I'm not planning to load all data since user actually looks only very small
part of data.

I think I need a simple approach: autocomplete reads first match from sql
server.
Opening picklist shows and reads first 10 matched from server.
Scrolling in pick list reads next 10 records etc.

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

[Andrus]

Thank you.

..NET combox has autosuggest feature.
I think I can use this. In this case there is no need to implement
autocomplete in my code.

In thi case I need to capture autosuggest not found event and in this event
read matching row from sql server.

I searched google.com and live.com for a keywords "c# combobox" and looked
for results. I read all combobox arcticles from codeproject.com

Most result describe creating auto-complete and multi-column combobox.

I havent found any which describes creating virtual combobox or similar
control.

Which keywords I should use to search or where to find information about
creating such control ?

Andrus.
"Peter Bromberg [C# MVP]" <pb*******@yahoo.yabbadabbadoo.comwrote in
message news:27**********************************@microsof t.com...

>I think what you want to do here is handle the keypress event, and after
there are at least 2 or 3 letters typed, you would use this as a filter in
the WHERE clause of your SQL Statement to return a small matching subset,
and
bind this to your control.
There are a number of implementations of this for both Windows Forms and
ASP.NET, you can find them easily with a well-constructed google or
live.com
search.
Peter

--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net


"Andrus" wrote:

>I'm creating a database Winforms 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.