We are trying to rebuild a current Access 2002 (XP) mdb program to VB.NET
with a SQL database - we are having problems with a suitable combobox.
There are many threads discussing multiple column comboboxes in .NET. We
are having success with the multiple columns similar to the combobox from
Access 2002 (XP).
Our biggest problem is speed. In a form in Access 2002, our combobox is
able to load data in a table in separate Access database file in a fraction
of a second. This table is already sorted in the proper order, and has
65,000 records (almost the limit of items in the Access combobox). For this
speed test, the table consists of one field. The records are just integers
counting from 1 to 65,000. The Access combobox is filled with data by
setting the combobox.RowSource property to equal a SQL Select Statement:
SELECT field1
FROM table1
IN 'c:\pathto\database.mde"
We tried to do a similar test using the VB.NET combobox with Microsoft SQL
Desktop Edition 2000. Same table as above. We tried several approaches
including binding to a DataSet, binding to an array (filled the array with
DataReader), and using the combobox.Items.AddRange(array). The best result
we could get with the 65,000 records was to use a DataReader to fill an
Array, then use the combobox.item.addrange - but this was approx. 7 seconds -
still slow compared to Access, and too slow for our purpose. This 7 second
was during the AddRange step, not during the looping through the DataReader
to fill the Array. Filling the Array was very quick. All of our tests
indicate the the slow down is getting the information into the combobox, not
get the data from the database.
Is there a Microsoft suggestion get a large list of data into the combobox
quickly the way Access could do it?
Is there a third party combobox that can work in VB.NET and load the data
quickly, preferable with multiple columns?
Any other suggestions?
VB.NET solutions are preferable, but we do have VS.NET Pro 2003, so we can
support other languages as well.
Thank you for all that took the time to read this long message.
PS - it was suggested that 65,000 records are too many to be showing to a
user. The purpose of this list to allow the patient to select a
client/patient from a large and growing list in a medical lab setting.
Medical labs are always receiving new patients, and usually only see a
patients once or twice. They do need to keep the patient active in case the
patient returns.
