[Solved] Filtering datagridview with textbox

[1635321]

I am using this code to import an excel sheet into the datagridview

        
        Dim dt As DataTable = New DataTable
        dt.Columns.Add(New DataColumn("PartNumber"))
        bs = New BindingSource
        bs.DataSource = dt
        DataGridView1.DataSource = bs
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        Con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\ExcelWork.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES""")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", Con)
        MyCommand.TableMappings.Add("Table", "Net-informations.com")
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        DataGridView1.DataSource = DtSet.Tables(0)
        Con.Close()

Now how do I filter it using a text box????

[kareninstructor]

Hello,

A simple method, first declare your BindingSource at form level

Public Class Form1
    WithEvents bsData As New BindingSource
...

Loading your data, in this case first row is column headers so the connection string reflects that. The field names are not for you but for a test file I used for this demo.

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim dt As New DataTable

    Using cn As New OleDb.OleDbConnection With {.ConnectionString = "Your connection string"}
        Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
            cmd.CommandText = "SELECT [Branch ID] As ID, [Branch Name] As Name FROM [Sheet1$]"
            cn.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using

    bsData.DataSource = dt
    DataGridView1.DataSource = bsData
End Sub

Now let's day I want to filter on Name column (in sheet1 the header was Branch Name but I am using sql aliasing) for an exact match

bsData.Filter = "Name = '" & TextBox1.Text & "'"

Or perhaps if the text in the text box begins with

bsData.Filter = "Name like '" & TextBox1.Text & "%'"

ID is greater than

bsData.Filter = "ID > 3"

remove filter

bsData.Filter = ""

Another option is filtering from the underlying data view

[jmcilhinney]

Does that code really make sense? You create a DataTable and a BindingSource, bind the DataTable to the BindingSource and the BindingSource to the grid, then you throw all that away by creating a DataSet and binding that to grid. No, it doesn't make sense. Add your BindingSource in the designer, create a DataTable, populate that DataTable, bind it to the BindingSource and bind that to the grid. You can then filter the data by setting the Filter property of the BindingSource.

[1635321]

Can you give an example of this code, I'm obviously inexperienced with vb

[jmcilhinney]

Can you give an example of this code, I'm obviously inexperienced with vb

You've already got it all. You just need to take out the bits that aren't required.

[1635321]

Ok so my import code is

Dim dt As New DataTable

        Using cn As New OleDb.OleDbConnection With {.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\ExcelWork.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"""}
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText = "SELECT * FROM [Sheet1$]"
                cn.Open()
                dt.Load(cmd.ExecuteReader)
            End Using
        End Using

        bsData.DataSource = dt
        DataGridView1.DataSource = bsData

I have the WithEvents thing at the top

And my filtering statement is

bsData.Filter = "PartNumber = '" & txtSearch.Text & "'"

To filter the PartNumber Column for the text in txtSearch


When I filter the filter only works if I type the EXACT text (which is a long part number) in the text box. Shouldn't it just reduce the results so I don't have to type the whole thing

[1635321]

Because this is not related to the original post I have moved this thread to here:
http://www.vbforums.com/showthread.p...39#post4714339

[Shaggy Hiker]

Not like that it shouldn't. The query you have given it is PartNumber equals X. That will ONLY return those records where the PartNumber really does equal X, not records where the PartNumber kind of resembles X, which sounds like what you want.

There are a couple ways to get to what you want. The simplest is probably to use Like rather than =, then use a wildcard in the search string. I forget what wildcard is used in that situation, but it may look like this:

bsData.Filter = "PartNumber LIKE '" & txtSearch.Text & " %'"

The % would be the wildcard, but % may be the wrong character to use there.

The drawback of this type of filtering is that it means that the PartNumber has to start with the text from the search. If you put a wildcard at the beginning of the search as well as the end, then it would filter on any PartNumber that contains the text from the search. In both cases, you have to get the PartNumber pretty much right, and all you will see is the filtered set of records.

A different approach is to have the datatable ordered by the PartNumber (sorted on PartNumber). Then, you could have a textbox that, as you typed, would scroll the datagridview to show the first match at the top of the grid, but wouldn't reduce the set of results in any way. This is a more complicated way to go about it, but does have some advantages when searching, in some cases.

[1635321]

When I use this, the datagridview is filtered to nothing, even with the exact partnumber typed. Not sure how to find the character to replace the % with.

[Shaggy Hiker]

That makes it sound like I have the wildcard symbol wrong. If % is not the wildcard symbol and is used anyways, then there certainly won't be any matches, because it won't be matching PartNumber X, but PartNumber X%. You might try * as the wildcard symbol, or even ?. If those don't work, I think Google might be the way to go. I don't remember the wildcard symbol.

[1635321]

YAY i figured it out. I used this statement

bsData.Filter = String.Format("PartNumber Like '%{0}%'", txtSearch.Text)

Thanks alot

[kareninstructor]

YAY i figured it out. I used this statement

bsData.Filter = String.Format("PartNumber Like '%{0}%'", txtSearch.Text)

Thanks alot

Exactly what I suggested.

[dday9]

YAY i figured it out. I used this statement

bsData.Filter = String.Format("PartNumber Like '%{0}%'", txtSearch.Text)

Thanks alot

Don't know if it's right for you to say that YOU figured it out whenever I posted that same exact answer here.

In the future, limit yourself one thread per question.