Solved

Combo box binding slow - need to find code for autocomplete text box

Posted on 2006-11-06
7
288 Views
Last Modified: 2012-08-13
I have unfortunately found after a long and arduous design of an application that I have to change code in almost every form because of a speed issue dealing with binding a particular combo box.  I have a contact table with 12K records in it and reference that list in MANY forms.  It take 4-5 seconds to bind using VB.NET 2005 and SQL Server Express.  I have definitely narrowed it down to that.  Talk about a sucker punch.....

I have another application that contains approximately 1K recs in it with no noticeable slowdown.  So I continued down this merry road not knowing what was about to happen after loading these 12K recs.

I have to ask....is there no other way around having to change to an object other than a combo box?  I've read other postings about breaking up or filtering the combo box but that is impossible.

If I have no other choice, my main question is this.....can anyone point me to a piece of code almost identical, if not identical, to the textbox completion of an email address as implemented in MS Outlook???  It's clean, requires only one key click, highlights the first row in a simple one-line border dropdown, backspace & tab keys work flawlessly.


Sick as a dog,
Phil Tate
0
Comment
Question by:TSFLLC
  • 4
  • 3
7 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 17883303
Phil

Try this out.  One form, one textbox (tb1) and one listbox (lb1) placed immediately underneath it.  This code

Public Class Form1

    Private dt As New DataTable("TestTable")
    Private dv As New DataView

    Private Sub filltable()
        dt.Columns.Add(New DataColumn("ID", GetType(Integer)))
        dt.Columns.Add(New DataColumn("Name", GetType(String)))
        For i As Integer = 1 To 12000
            Dim dr As DataRow = dt.NewRow
            dr(0) = i
            dr(1) = i.ToString
            dt.Rows.Add(dr)
        Next
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'make some data - rquvalent to filling table from database
        filltable()
        'set up dataview
        dv.Table = dt
        'filter on something that won't exist
        dv.RowFilter = "Name = 'z'"
        'bind listbox to dataview
        lb1.DataSource = dv
        lb1.DisplayMember = "Name"
        lb1.ValueMember = "ID"
    End Sub

    Private Sub tb1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles tb1.TextChanged
        'set default to something that won't exist
        Dim textFind As String = "z"
        'if entered text is not blank
        If Trim(tb1.Text) <> "" Then
            'use it as filter
            textFind = tb1.Text
        End If
        'apply filter to dataview
        dv.RowFilter = "Name like '" & textFind & "%'"
    End Sub

End Class

Obviously it would need tarting up for real purposes.  But it's quite a bit quicker because it only loads a fraction of the records.  Although you say "I've read other postings about breaking up or filtering the combo box but that is impossible", given the speed advantage, I have to ask: why is it impossible?

Roger
0
 

Author Comment

by:TSFLLC
ID: 17884400
Hey Roger,

It's not an issue to filter if I'm using a text box and a listbox.  But I can't filter using a combo box because any one of my contact records are valid records for the specific field.

I am about halfway through writing and testing something very similar to what you've included above.  I am making the list box visible/hidden as text is changed in this one textbox field because room is of the essence.  So I am positioning and attempting to create the flow of the textbox/listbox almost identical to that of the Outlook textbox I referred to.

I only have a couple of issues with doing this and I would appreciate it if you could help me.

The flow is that the user continues to key characters into the textbox.  Backspace has been accounted for so that the filter field can be adjusted accordingly.  If after a keypress the dv.count > 0 then I select the first row of the listbox automatically.  I capture the Up & Down and change the selected listbox row accordingly.  Then the user can press TAB and the textbox is populated with the selected listbox item.  Else they can click on a different item in the listbox and the textbox is re-populated.

Issues...well looks like only one after trying a few things before continuing with this post.

1)  Can I capture the TAB key to populate the textbox with the listbox.text value?  I'm having trouble using the textbox.Leave event because if I click on a value within the listbox, then tb.Leave executes with the previously selected value.

Thanks!
Phil
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 17884703
Phil

As before, but with the Form's KeyPreview = True, and this revised code

Public Class Form1

    Private dt As New DataTable("TestTable")
    Private dv As New DataView
    Private tabbing As Boolean = False

    Private Sub filltable()
        dt.Columns.Add(New DataColumn("ID", GetType(Integer)))
        dt.Columns.Add(New DataColumn("Name", GetType(String)))
        For i As Integer = 1 To 12000
            Dim dr As DataRow = dt.NewRow
            dr(0) = i
            dr(1) = i.ToString
            dt.Rows.Add(dr)
        Next
    End Sub

    Private Sub Form1_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles Me.KeyUp
        If Me.ActiveControl.Name = "tb1" Then
            If e.KeyCode = Keys.Tab Then
                tabbing = True
                tb1.Text = CType(lb1.SelectedItem, DataRowView)("Name")
                e.Handled = True
            End If
        End If

    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'make some data - rquvalent to filling table from database
        filltable()
        'set up dataview
        dv.Table = dt
        'filter on something that won't exist
        dv.RowFilter = "Name = 'z'"
        'bind listbox to dataview
        lb1.DataSource = dv
        lb1.DisplayMember = "Name"
        lb1.ValueMember = "ID"
    End Sub

    Private Sub tb1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles tb1.TextChanged
        If tabbing Then
            tabbing = False
            Exit Sub
        End If
        'set default to something that won't exist
        Dim textFind As String = "z"
        'if entered text is not blank
        If Trim(tb1.Text) <> "" Then
            'use it as filter
            textFind = tb1.Text
        End If
        'apply filter to dataview
        dv.RowFilter = "Name like '" & textFind & "%'"
    End Sub

End Class

Can you develop that idea?

Roger
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:TSFLLC
ID: 17884849
Excellent!

I've just realized that incorporating this with all of my combo boxes will speed up form display greatly.

I see how to incorporate this inside what I have already designed.  The KeyUp will work great I think.  We'll leave it at that!

Appreciate your help Roger!

Phil
0
 

Author Comment

by:TSFLLC
ID: 17885955
Roger,

After testing these changes, several things seem apparent.

1)  The code under Me.KeyUp never gets launched.  I've tried to set a breakpoint and also something like a Msgbox("Here!")
2)  TAB does not have any effect on KeyUp even if I create a new function specifically for say tb1.KeyUp.  I am unable to capture it.

I went down this road before when I was designing code for AutoComplete and a combo box.  I ran into this same problem and never resolved it.

If you have used the code you included above regarding the TAB key before.....any words of wisdom?




0
 
LVL 34

Expert Comment

by:Sancler
ID: 17885983
>>
with the Form's KeyPreview = True
<<

????

Roger
0
 

Author Comment

by:TSFLLC
ID: 17886171
Sorry about that.  That line in your post went right by me.  Just tested it and we're in business.

I also was unaware of the form's KeyPreview capability.  That was the root of my original problem on the post I just sent.

My eyes are opened!!

Sincerely,
Phil
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now