Link to home
Create AccountLog in
Avatar of g_johnson
g_johnsonFlag for United States of America

asked on

VB.Net combobox items.addrange method

My application needs to frequently read a database table of about 20000 items and re-fill a combobox.  Speed is an issue.  I would like to make it faster by building an array from the table read loop, then use the items.addrange method to populate the combobox.

I can't figure out how to do that.



Avatar of Joel Coehoorn
Joel Coehoorn
Flag of United States of America image

I doubt adding the items to the combobox is your bottleneck.  You will get a much better return on your work by trying to improve your database query.
You should, for sure, look at the database query, but keep in mind that adding all those items to a combobox can take a significant amount of time. How many items are you adding?

You may want to utilize Me.SuspendLayout() while adding the items to the box.
Agree with ZachSmith. Why don't you show us you code ?
Avatar of g_johnson

ASKER

I'll gladly show you my code.  It couldn't be simpler, and is exactly as described above.  It reads about 25000 records from the table.  The table is indexed.on compitemno.

If you can improve on it, that would be great.

But I would also like an answer to the "addrange" question asked, as I have other uses for it, too.

Thanks for your help!




    Private Sub loadVI()
        Dim sConn As String = System.Configuration.ConfigurationSettings.AppSettings("connection")
 
        'getting the list of competitor items on the system
 
        Try
            Me.Cursor = Cursors.WaitCursor
            'clear previous
            Me.cboItem.Items.Clear()
            Dim c As New SqlConnection(sConn)
            Dim cm As SqlCommand
            Dim rdr As SqlDataReader
 
            c.Open()
            cm = New SqlCommand
            cm.Connection = c
            cm.CommandType = CommandType.Text
            cm.CommandText = "SELECT compitemno,compitemdesc from ast_cpcvlookup order by compitemno"
            rdr = cm.ExecuteReader
            While rdr.Read
                Me.cboItem.Items.Add(CType(rdr(0), String).PadRight(30) & "  " & CType(rdr(1), String))
            End While
            rdr.Close()
            If c.State <> ConnectionState.Closed Then
                c.Close()
            End If
        Catch ex As Exception
            MessageBox.Show("Error 1032 Getting Competitor Items | " & ex.Message, "CPC Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            Me.Cursor = Cursors.Default
        End Try
 
    End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of ZachSmith
ZachSmith
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Have you tried using databinding?  Just set the reader as the combobox's data source.  And previously I was thinking you were doing some filter on the data before adding it.  I seriously hope you aren't asking your users to manually filter through 20,000 items.  That's just cruel.
[ I seriously hope you aren't asking your users to manually filter through 20,000 items.  That's just cruel.]

LOL -- that's the way they wanted it.  I assume you mean as opposed to a search or lookup of some type?

I don't know how to do this:

[Have you tried using databinding?  Just set the reader as the combobox's data source. ]
Thanks Zach -- I'll also try to implement that method.
Sounds good.. Keep us updated
>> Just set the reader as the combobox's data source
That not work fine jcoehoorn because he have to format the field


The ZachSmith method is quick but you need to calculate the affected rows or use a big dimention for the array.