Filter listbox items by textbox

[Ultrawhack]

Hi,

I have a listbox filled with items from database. I have a "Quickfind" textbox which user will type in the item they need.

As user types it should filter the items in listbox. This is not an autocomplete function.

Can anyone help me out with this ?

Thanks !

 

[vis781]

Obviously you won't be able to do this with a databound listbox. But i would go about it like this.

You will need to trap the keyup event then store the key that was pressed in a separate variable and also an integer pertaining to the numerical position the character has in the strings order.. Add a separate method to take the character entered and also a number that would be equal to the index of the character pressed. Then iterate through the listbox items collection and match up the character to the listboxitem text. If no match remove it and add it to a separate arraylist for repopulating the listbox later if the textbox text is deleted.

 

[Ultrawhack]

Hmmm. Lemme change the logic a bit. In Access, I did it this way. The Listbox was bound to a query
WHERE [tblMyItems].itemName LIKE me.txtquickfind
txtQuickFind's default value was "*"

That way, initially the listbox showed all items bound to the query.

When users changed txtQuickfind and hit a cmdBtn the query changes and I run a me.Listbox1.requery, thus filtering the listbox.

How would I achieve this in VB2005e using the KeyUp event ?

 

[vis781]

Very little from what i can see. You will Just need a different type of SQL statement. I borrowed this from a post by Kulrom so acknowledgements go to him for this...

strSQL = "SELECT * FROM Table WHERE Field LIKE '" & me.Textbox1.Text & "%'"

Just execute the query on the keyup event of the textbox.

 

[Ultrawhack]

Thanks. How should the listbox be bound to the query results ?

 

[vis781]

You have a choice here.

Option1: You could fill a datatable with the results from the query and bind your listbox to that.

Option2: You could use a datareader to read the results into the list box.

Summary:

Option 2 would be faster but you would have to clear the listbox yourself everytime the query executed, and would become more problematic if you wated to alter those records.

Option 1 is less work/typing but will give you increased flexibility to alter records of you wish, but it's slower

 

[Ultrawhack]

For my purposes, records are never altered from the listbox itself but...

would you be able to point me to some sample code for your option1 ?

Thanks.

 

[vis781]

For option1 all you will need to do is to set the datasource property of the listbox to the datatable you are using. You will also need so set the displaymember property. Then everytime the text i changed the query will run the underlying datatable's rows will change and the changes will automatically be reflected in your listbox.

 

[Paszt]

It is possible to set the dataSource of the listbox to a dataView (the dataTable class includes the DefaultView property which is a dataView), then in the keyup event of the textbox, set the RowFilter property of the dataView to the WHERE clause (without the word where).

This method precludes your app from making trips to the database each time to retrieve a subset of the data you already have, thus improving response time.

 

[Ultrawhack]

Nice. Would you have any sample code for me to refer to ? Thanks !

 

[jmcilhinney]

There's never a need to bind to a DefaultView because when you bind to a DataTable it is the contents of the DefaultView that gets displayed anyway. The only reason to bind to a DataView is if you want more than one view of the same data. In 2005 you should probably be using a BindingSource bewteen the DataTable and the control though.

 

[Paszt]

I thought of the BindingSource after I posted but then I assumed that it was overkill to use a BindingSource for only one control.
By the way if you were to use the BindingSource, you would set it's Filter property to filter the listbox (go figure).
So it's more clear about the DataView when binding to a dataTable: If you bind to the dataTable, you only need to set the RowFilter property of the DefaultView property of the DataTable that the listbox is bound to (as jmcilhinney stated). I didn't realize this so thanks for the info Jim.

Example:
DataSet.DataTable.DefaultView.RowFilter = "field = criteria"
or to use a wildcard filter:
....RowFilter = "Field LIKE '" & me.Textbox1.Text & "%'"

 

[Ultrawhack]

Gentlemen, thanks for the info. There was a lot said so Let me see if I understood correctly and start from scratch.

To filter DataGridView1 from the KeyUp event of my txtQuickFind...

I drop a DataGridView onto my form and Choose Data Source and add a project datasource (myDb.mdb in my case) and edit the columns I want to be able to see.

Now I get confused. In 2005 how do I proceed. I am unable to find the MyDb.DataTable.DefaultView.RowFilter to be able to set the condition.

Any help would be greatly appreciated.

 

[Paszt]

2005 makes it really simple.
Start with an empty form. You've already got the datasource set up right. Have your empty form open in design view and open the Data Sources panel. Open the datset by clicking the plus sign beside it, if it's not already open. The list of available dataTables will be shown. Click the name of the dataTable you want to work with and you be able to access a dropdown. Open that dropdown and make sure DataGridView is selected (more on this later). Now drag the dataTable onto the form designer. The IDE will create a DataSet, BindingSource, TableAdapter, and BindingNavigator whose name will depend on the DataTable you selected. The BindingNavigator is very similar to the navigation buttons you get in Access and you can delete it if you don't have a need for it. Deleting it will have no affect on the other components. The IDE will also create the form's Load event handler with a statement that fills the TableAdapter. At this point you can run the app and the dataGrid will be filled with all the data.
Next add a texbox which will allow the user to type into to filter the datagridview.
Switch to code view and in the dropdown at the upper-left select the textbox, in the dropdown on the upper-right select Keyup. A method signature will be created for the textbox's keyup event. In the method type something like: Me.TableNameBindingSource.Filter = "FieldNametoBeFilter LIKE '" & TextBox1.Text & "%'" where TableNameBindingSource is the BindingSource created and FieldNametoBeFilter is the field you want to filter against.
That's it. It's working and you only had to type one line of code.

Now about the dropdown in the data source panel and selecting DataGridView. If you wanted to create a detailed form view like the standard Access form, you would select Details in that dropdown. Then when you dragged the table over to the form designer it would create different controls for each field in the dataTable. You can even configure what the controls should be by expanding the DataTable in the Data Sources panel and accessing their dropdown to choose a control. Note that the dropdown is only available when you have a form open in design view.

Hope that helps.

 

[Ultrawhack]

Thing of great beauty. Thanks to the moderators of these forums - you have been of immense help. Thanks Paszt for walking me through.

I had to add a few lines of code to the keyup event as it refused to accept
Me.TableNameBindingSource.Filter = "FieldNametoBeFilter LIKE '" & TextBox1.Text & "%'"

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] filtStr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2]filtStr = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].txtQuickFind.Text
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TableNameBindingSource.Filter = [/SIZE][SIZE=2][COLOR=#800000]"[FieldName to be filtered] like '"[/COLOR][/SIZE][SIZE=2] & filtStr & [/SIZE][SIZE=2][COLOR=#800000]"%'"[/COLOR][/SIZE]

And it works great.

Except that it hates strange chars like "it's me" in the textbox. How do I handle special characters like apostrophes in the filter string?






Thanks again