AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data
Go Back  Xtreme Visual Basic Talk > > > AutoComplete Textbox with multi-column Listbox and filtered table data


Reply
 
Thread Tools Display Modes
  #1  
Old 01-02-2013, 01:59 PM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default AutoComplete Textbox with multi-column Listbox and filtered table data


This should be simple.
I've seen examples of this in VB6 but can't find one in VB.Net.

I have a textbox.
As I start typing in the textbox a listbox appears directly below it.

Say the first letter I typed in the textbox was "A".

The listbox that appears below the textbox has a list of "A" string words drawn
from the second column of an SQL table off a remote server that appears
as a multicolumn snaking list (and it would be nice to be able to set the listbox to a fixed width horizontally,
but expand vertically so no scollbars appear if there's a lot of words).

Basically it should show all the "A" words from the second column of the table, only filtering out duplicates of each word.

As I I type the letter "u" after the "A" in the textbox then the multi-column snaking list of words in the Listbox
should suddenly shrink to only words that start with "Au" (I think this is the "Select" part of Autocomplete),
and so forth, until enough letters are typed that there is only one word that will uniquely match.
At that point the textbox autocomplete appends that word.

The textbox should not allow typing in a letter that doesn't match.
If there is no "Auj" selection in the Listbox list then trying to type a "j" after "Au" just doesn't work
(key input of that letter at that point is rejected).

As soon as this autocomplete append happens the listbox goes away,
but if the backspace key is pressed it re-appears with the filtered list again.

Has anyone seen an example like this?

Is it possible to use a DataView somehow in this scenario?
Note: I don't want a multi-column like a ListView or DataGridView or a multi-column ComboBox control.
I just want a list that snakes from one column to another like a newspaper.

I know there has to be a VB.Net code demo of this somewhere I just can't find it...

I did find some code examples that might work if the Listbox wasn't being fed/bound to a remote data source:
Code:
Private Sub UpdateAutoComplete()
        'Clear current autocomplete list
        TxtUserInput.AutoCompleteCustomSource.Clear()
        'Loop through each listbox item and add it to the Autocomplete source
        For i As Integer = 0 To LstCriteria.Items.Count - 1
            TxtUserInput.AutoCompleteCustomSource.Add(LstCriteria.Items(i))
        Next
End Sub
..or:
Code:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Me.SetCompleteList(Me.TextBox1, Me.ListBox1.Items)
End Sub
     
Private Sub SetCompleteList(ByVal tb As TextBox, ByVal list As IList)
    Dim source As New AutoCompleteStringCollection
     
    For Each item As Object In list
        source.Add(item.ToString())
    Next item
     
    tb.AutoCompleteCustomSource = source
End Sub
Looking around a little more found this class (but still doesn't support remote binding):
Code:
Public Class sayt     
    Dim items() As String = {"one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten"}
    Dim aTextBox As TextBox
    Dim aListBox As ListBox
     
    Public Sub New(ByVal tb As TextBox, ByVal lb As ListBox)
        aTextBox = tb
        aListBox = lb
        AddHandler tb.TextChanged, AddressOf tb_TextChanged
        aListBox.DataSource = items
    End Sub
     
    Private Sub tb_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        aListBox.DataSource = If(aTextBox.Text = "", items, items.Where(Function(s) s.Contains(aTextBox.Text)).ToArray)
    End Sub 
End Class
'used like:
Public Class Form1     
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim s1 As New sayt(TextBox1, ListBox1)
        Dim s2 As New sayt(TextBox2, ListBox2)
    End Sub  
End Class
Tried searching for ListBox FindString and FindStringExact.
Found this page that had the following code (still no remote binding though):
Code:
Private Sub TextBox1 TextChanged(...) Handles TextBox1.TextChanged
    Dim srchWord As String = TextBox1.Text.Trim
    If srchWord.Length = 0 Then Exit Sub
    Dim wordIndex As Integer
    wordIndex = ListBox1.FindStringExact(srchWord)
      If wordIndex >= 0 Then
          ListBox1.TopIndex = wordIndex
          ListBox1.SelectedIndex = wordIndex
      Else
          wordIndex = ListBox1.FindString(srchWord)
          If wordIndex >= 0 Then
            ListBox1.TopIndex = wordIndex
            ListBox1.SelectedIndex = wordIndex
          Else
            Debug.WriteLine("Item " & srchWord & " is not in the list")
          End If
      End If
End Sub
Hmmm.found something that suggests maybe I should be Casting
(but still no idea about remote binding):
Code:
ListBox1.Items.AddRange(NewString() {"????", "???"})
TextBox1.AutoCompleteSource = AutoCompleteSource.CustomSource
TextBox1.AutoCompleteCustomSource.AddRange(Me.ListBox1.Items.Cast(Of String).ToArray)
TextBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend
Another hour of fruitless searching.
One thing though I should add - speed.
In order for autocomplete to work properly the data has to be cached from the remote server.
The caching has to be done carefully though because multiple users are accessing the database simultaneously.

I have found the only way to get data viewing latency down to acceptible levels (under a second for scrolling through lots of data in real time)
is to use a DataGridView in Virtual Mode with JIT code where the DataRetriever object is used
to implement the IDataPageRetriever interface required by the Cache class.

In case not everyone know what I'm talking about:
MSDN How to: Implement Virtual Mode with Just-In-Time Data Loading in the Windows Forms DataGridView Control

Yes I know the Listbox doesn't have a Virtual binding mode (only the ListView does),
but maybe there is some other way of caching that can be done..?

I do know I can "chunk" the data by breaking it down into separate tables keyed to alphabetic index if I had to,
but there's some Join-ing involved so things could get complicated if I had to go that route..


After a few more hours of searching I finally found some code for databinding a multicolumn listbox
that allows a rowset of cells data to be broken out and displayed in textboes.

Maybe if I can reverse this setup and find a away to use async SQL commands I might be able to get what I want.

Still definitely look for help though..

Here's the code:
Code:
Imports System.Data.SqlClient
Public Class Form1
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      'Create Connection object
      Dim connectionString As String = "yourconnectionstringhere"
      Dim thisConnection As New SqlConnection(connectionString)
       ' Sql Query 
      Dim sql As String = "SELECT * FROM dbo.Table1"
      ' Create Data Adapter
      Dim da As New SqlDataAdapter(sql, thisConnection)
      ' Fill Dataset and Create DataTable
      Dim DataSet1 As New DataSet
      da.Fill(DataSet1, 0, 90, "Table1")
      Dim dt As DataTable = DataSet1.Tables("Table1")
      ListBox1.DataSource = dt
      ListBox1.DisplayMember = "field0"
      ' Bind to field 1 column of the employees table
      TextBox10.DataBindings.Add("text", dt, "field1")
      ' Bind to field 2 column of the employees table
      TextBox1.DataBindings.Add("text", dt, "field2")
      ' Bind to field 3 column of the employees table
      TextBox2.DataBindings.Add("text", dt, "field3")
      ' Bind to field 4 column of the employees table
      TextBox3.DataBindings.Add("text", dt, "field4")
      ' Bind to field 5 column of the employees table
      TextBox4.DataBindings.Add("text", dt, "field5")
      ' Bind to field 6 column of the employees table
      TextBox5.DataBindings.Add("text", dt, "field6")
      ' Bind to field 7 column of the employees table
      TextBox6.DataBindings.Add("text", dt, "field7")
   End Sub
End Class

Last edited by bokeh; 01-03-2013 at 06:52 AM.
Reply With Quote
  #2  
Old 01-07-2013, 06:06 PM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default Dataview needed?

I'm looking into using DataViews as a way of filtering, but most of the examples around the internet don't work.
Mainly because of the connection string being invalid.

I finally found one working example after days and days of searching.

The other way I thought might be possible is to use a special class to populate the listbox,
but the only good working example I found used a datasource different than what I need.
Attached Files
File Type: zip DataGridView_filter_by_selected_columns.zip (23.4 KB, 57 views)
File Type: zip Listbox_populate_class.zip (20.5 KB, 51 views)

Last edited by bokeh; 01-07-2013 at 06:35 PM.
Reply With Quote
  #3  
Old 01-08-2013, 04:45 AM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default DataView Find without sorting--seeming dead end; plus alternate approaches

I found an working example of using a textbox to search through a dtagridview and find a certain value.

Sounds great, doesn't it!
No - it's horrible.
You have to Sort (on at least one column) before you can use Find.
I really don't want to have to Sort at all just to "select" a column to be the column that Find uses for its looking.
I though about what if we Sort multiple columns with the last returning the DtagridView to its original striaght-from-the-datasource-ordering).

If only Find could use multiple columns for it's looking?

Here is the MSDN Finding Rows (ADO.NET) page, which does offer a way to sort by multiple columns:
Quote:
For sort orders containing multiple columns, you pass an array of objects. Note that for a sort on multiple columns,
the values in the object array must match the order of the columns specified in the Sort property of the DataView.
'code snippet from bottom of page:
Code:
Dim foundRows() As DataRowView = _
  custView.FindRows(New object() {"The Cracker Box", "Liu Wong"})

If foundRows.Length = 0 Then
  Console.WriteLine("No match found.")
Else
  Dim myDRV As DataRowView
  For Each myDRV In foundRows
    Console.WriteLine("{0}, {1}", _
      myDRV("CompanyName").ToString(), myDRV("ContactName").ToString())
  Next
However most of the Find examples use something call the CurrencyManager and this examples doesn't MSDN code snippet doesn't show
how to move the CurrencyManager to the first instance found
then gradually increment the CurrencyManager positional index to all susequent finds.

In this example the DataView is set from a DataTable.
You can also set a DataView from a DataSet, which seems a little more complicated.

Theoretically there are ways you could search a DataSet to have a selectively filtered DataView,
but again, haven't found any good examples.

I guess you could even set up a Relationship between a DataTable and a DataSet, according to the code in this post.

However there are issues going down the relationship path as well.

I just want to be able to select a certain column to search thru and using a list that contains no duplicates from that column,
find all matches as the search text is typed out one letter at a time.
This is far far too slow to do using an SQL command to the remote database.
It would take to long for AutoComplete to happen in real time.

I've looked at using RowFilter but it's not set to work around duplicates and haven't found any examples
where if you had multiple search row hits then you could use another column to refine the search.

Both Bound and Unbound mode of the DtaGridView are too slow,
Virtual JIT mode sufferes from "chunkiing syndrome".
It looks like Virtual mode is the only way to go but there are issues
with the DataAdapter not being entirely thread safe to use.

So I'm looking for a way to load a DataSource into a DataSet asynchronously
(maybe using SqlCommand.BeginExecuteNonQuery method or the
SqlCommand.BeginExecuteReader method),
then transfer the DataSet, once the asynchronous loading from the DataSource is complete, to the Form Gui thread
(or somehow reference it between the two -maybe as Public from a Module).

There are issues with the "how do you know when it's completely loaded asynchronously" part of things though, from the research I've been doing...

I think if could get an asynchronous search selection filter going properly then the Virtual-JIT mode for the DataGridView might
be able to handle showing the results fast enough in the Form GUI thread.
But I have no idea about where to even begin coding this notion..

Quote:
edit (a day later):
I was looking through my hard drive and I found something.
If you download the VB.Net 101 samples there are a couple that might prove useful:
Data Access - N-Tier Data Form and Data Layer
Data Access - Sort and Filter with a DataView

Of course the DataAccess sample is hard coded for the ancient (but venerable) MSDE (Microsoft Developer Edition) version of the NorthWind database.
MSDE 2000 doesn't run under Windows 7 x64 and getting Northwind hooked into an SQLEXpress version is not a trivial excercise,
but (handy tip) if you have a remote mSServer with a connectionstring and at least one table name
you can drop them in and the code works (albeit very slowly).
The other option is just to use (tie-in with) the SQLExpress AdventureWorks database.

Last edited by bokeh; 01-09-2013 at 04:44 AM.
Reply With Quote
  #4  
Old 01-10-2013, 04:37 AM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default Pseudo-virtual Mode (?) and other research including DataGridViewAutoFilter class

Quote:
Originally Posted by bokeh
It looks like Virtual mode is the only way to go..
Found something else.

This StackOverFlow thread talks about something called "Pseudo-virtual" mode.
Of course all the code presented in that thread is c#.
Has anyone seen anything like what Kevin McCormick's reply described in terms of a VB.Net code implementation/example?

Also some more pages with info on improving performance of the DataGridView (1, 2, 3, 4, 5, 6).

..and yes I now about:
MSDN: Best Practices for Scaling the Windows Forms DataGridView Control

Helpful information..but it only goes so far toward explaining the internal machinations
of the DataGridView control that make is so slow when scaling things up.

There are a number of issues with the MSDN DataGridView Virtual Mode implementation code listed here.

This line alone adds seconds to the Form Load event and,
even if added to a button after the load event,
still slows the eventual display of data in the DGV control:
Code:
Me.dataGridView1.AutoSizeColumnsMode = _
            DataGridViewAutoSizeColumnsMode.AllCells
The dataGridView1_CellValueNeeded sub code should be working with asynchronously cached data from
a remote SQL server instead just using "faked up" created-at-runtime data..

The accompanying Walkthrough says it:
Quote:
..gives you a basic understanding of the events you must handle to implement virtual mode in the DataGridView control
However since it's part of the Performance tuning section I expected a lot more in the way of performance tweaking.

There's also a potential issue with column sorting when running a DataGridView in Virtual Mode.
This MSDN page says outright:
Quote:
Sorting by unbound columns in bound mode is not supported.
This StackOverFlow thread has some workaround code (C# of course).
This MSDN custom sorting page has a Programmatic Sorting section with some VB.Net code.

There's also a cell change commit timing issue when you have a DataGridView which is partially bound and in virtual mode.
This social.msdn thread has a workaround.

This social.msdn thread spots another Datagridview Virtual Mode CellValuePushed issue and has a workaround at the bottom.

Looking for an asynchrnous loading of data into a listbox I did find:
MSDN: SqlCommand.BeginExecuteXmlReader Method (AsyncCallback, Object)
however the sample code has a number of deficiencies including dealing with truncation, command timeouts, and inadequate callback exception handling.

There's another MSDN page that offers a little better approach:
SqlCommand.BeginExecuteReader Method (AsyncCallback, Object, CommandBehavior), but
still lacks robustness when dealing with Coammdn timeouts as well as no way to update edit changes back asynchronously.

I found something that explained how to load data using a BackgroundWorker thread, but loading is only half the issue.

However the essential data roundabout question is still:
Quote:
What about when you want to be able to edit and push the Update back to the DataSource asynchronously?
Will keep looking..

Quote:
Here's something I found that I didn't know existed:
"BindigSource.Filter expressions" --or maybe this VB.Net sample download page means "BindingSource.Filter expressions".

Anyway a link on that page lead me to this MSDN page which has a "DataGridViewAutoFilter Class Library".
Of course all the code on that page is C#.
You have to go here to get the download that includes the VB.Net code.

Once you download it and try to run it you'll find it's messed up.
It has the Class Library set as the startup project, which of course gives the dreaded:
"A project with an Output Type of Class Library cannot be started directly" error.
Of course it would be nice if it offered to change this to another project in the solution that was not a class library --but it does not.

So what you have to do to set things manually is right click on "Solution - DataGridViewAutoFilter",
at the top of the Solution Explorer window/pane, and select "Set Startup project...".

When the subsequent "Solution - DataGridViewAutoFilter Property Pages" dialog comes up there is a option for "Single Startup Project".

Use the drop down combobox associated with this option button and change from
"DataGridViewAutoFilter" to "DesignerSetupDemo" then hit OK and it should run properly.

I've been up all night on this post (the suns almost up), so I better try to get an hour of sleep before I have to leave for work..

Last edited by bokeh; 01-10-2013 at 06:20 AM.
Reply With Quote
  #5  
Old 01-11-2013, 04:01 PM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default Resolved!

Well it's been a pretty lonely thread, but hey, I guess that means I get all the credit for the solution.

I'm going to go out on a limb and say that there is not another VB.Net demo like this anywhere on the Internet (AFAIK).

After downloading the attachment and pasting in your own connection string,
run the code and press the "Load Data Asynchronously to Listbox" button.

A background Worker thread is started and in less than a second it will hopefully sort through thousands rows of data,
returns dozens of unique values for "VendCode" (Vendor Code or whatever column you choose)
which then populates a multi-column listbox control from the datasource.

The focus then automatically moves to a textbox control where AutoComplete Select and Append is set to True.

As each letter is typed in the textbox the AutoComplete drops down a subset list
of possible matches which decreases as each of the three letters is pressed.

I've also put in a TextChanged routine that moves the selected index in the listbox as the autocomplete is refined which each letter.
It doesn't affect the listbox being the custom autocomplete source for the textbox --it's just neat to watch.

Then when the textbox gets all three letters filled the focus moves to the "Search by VendCode" button
which, when pressed, pulls all parts that use the VendCode represented by the text in the textbox.

Or to put another way - the text string of the textbox is being used as a filter for the datagridview that is showing the selected results.

Composing the Transact SQL ( T-SQL) statement / command from the value of the textbox string is tricky.

It uses a value variable assignment to substitute for the Textbox text.
If you need to validate the value it should be done here.

Of course using parameterized queries would be better to avoid SQL Injection attacks -- if one knows how to do them.

It should be noted that in the attached code all MsServer data retrieval is done by using m_Worker with the DoWork and RunWorkerCompleted subs.

I did three separate sets of BackgroundWorker thread routines (instead of recycling a single Background Worker thread)
so it's clear what is what and which is doing what.

Of course if you want to get fancy I recommend adding progress reporting and support for cancellation via the code in this MSDN Walkthrough page.

I also hope that it can be the jump-off point for adding your own custom callbacks.
This Codeproject deals a little with that.

I wish the VB.Net Express Editions had better multi-threading debugging tools but things are what they are..

The attachment below, even though it resolves the question raised by this thread is only 5%
of what I eventually need for my full Warehouse Correlator application, but it's a good start.

Thanks to all who were at least interested enough to give this thread a "view".
Attached Files
File Type: zip Async_Loader_use_your_own_connection_string.zip (20.3 KB, 24 views)

Last edited by bokeh; 01-11-2013 at 04:12 PM.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data
 
AutoComplete Textbox with multi-column Listbox and filtered table data
AutoComplete Textbox with multi-column Listbox and filtered table data
 
-->