Solved

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

Posted on 2006-11-06
7
306 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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 …
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…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

630 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