Vb.net – How to do a search-as-you-type textbox in a DataGridView using VB.NET

databasevb.net

I have connected my DataGridView to a database but I can't implement the search function.

The flow of the program would be when I click one column of the DataGridView and I type in the search box, I can only get results from that same column not the other columns beside it.

It should also search letter by letter so basically a TextChanged event.

Best Solution

This is how i would do it

First, to have two variable to store your original datatable from database, and also a string variable to store your selected dgv column headertext (which will be used to do the filter later on).

Private oriDataTable As New DataTable
Private columnToFilter As String = String.Empty

My test on some dummy data

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'dummy datatable
    oriDataTable.Columns.Add(New DataColumn("ID"))
    oriDataTable.Columns.Add(New DataColumn("FirstName"))
    oriDataTable.Columns.Add(New DataColumn("LastName"))
    For i = 0 To 5
        Dim dr As DataRow = oriDataTable.NewRow()
        dr.Item("ID") = i
        dr.Item("FirstName") = "fn type1 " & i
        dr.Item("LastName") = "ln type1 " & i
        oriDataTable.Rows.Add(dr)
    Next
    For i = 6 To 10
        Dim dr As DataRow = oriDataTable.NewRow()
        dr.Item("ID") = i
        dr.Item("FirstName") = "fn type2" & i
        dr.Item("LastName") = "ln type2" & i
        oriDataTable.Rows.Add(dr)
    Next

    'Since you already connected to database 
    'i assume that you could fill a datatable and bind to dgv
    dgvToFilter.DataSource = oriDataTable
    columnToFilter = "ID" 'Assign any default column name
End Sub

Then add a ColumnHeaderMouseClick event handler on your dgv, update the columnToFilter each time when user click on it.

Private Sub dgvToFilter_ColumnHeaderMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles dgvToFilter.ColumnHeaderMouseClick
    Dim clickedColumn As DataGridViewColumn = dgvToFilter.Columns(e.ColumnIndex)

    'Note:HeaderText must match with your datatable column name
    columnToFilter = clickedColumn.HeaderText
    lblHeaderSelected.Text = columnToFilter
End Sub

And lastly the TextChaged Event. Use the DataTable.Select method to filter the datatable and update the result, if any, to the dgv.

Private Sub txtFilterText_TextChanged(sender As Object, e As EventArgs) Handles txtFilterText.TextChanged
    If txtFilterText.Text.Length <= 0 Then dgvToFilter.DataSource = oriDataTable

    Dim filterString = String.Format("{0} LIKE '{1}%'", columnToFilter, txtFilterText.Text)

    Dim dataRows As DataRow() = oriDataTable.Select(filterString)
    'Choose what you wan to do if no row is found. I bind back the oriDataTable.
    dgvToFilter.DataSource = If(dataRows.Count > 0, dataRows.CopyToDataTable(), oriDataTable)
End Sub
Related Question