Solved

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

Posted on 2006-11-06
7
298 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 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