Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

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

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
TSFLLC
Asked:
TSFLLC
  • 4
  • 3
1 Solution
 
SanclerCommented:
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
 
TSFLLCAuthor Commented:
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
 
SanclerCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TSFLLCAuthor Commented:
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
 
TSFLLCAuthor Commented:
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
 
SanclerCommented:
>>
with the Form's KeyPreview = True
<<

????

Roger
0
 
TSFLLCAuthor Commented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now