CODEWITHSUNDEEP
databasevb.net
iamlawrencev
iamlawrencev

Reputation: 125

How to do a search-as-you-type textbox in a DataGridView using VB.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.

Upvotes: 3

Views: 7694

Answers (2)

HengChin
HengChin

Reputation: 593

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

Upvotes: 3

Jake Harry Chavez
Jake Harry Chavez

Reputation: 40

You can try this.

 Private Sub txtUname_TextChanged(sender As Object, e As EventArgs) Handles txtUname.TextChanged
    dtaAdap = New SqlDataAdapter("Select * from tbl_user where Fname like '%" & txtUname.Text & "%'" & vbCrLf &
                                 " OR  Lname like '%" & txtUname.Text & "%'", con)
    dt = New DataTable
    dtaAdap.Fill(dt)
    DataGridView1.DataSource = dt
End Sub

The query in SQLAdapter goes a little something like this:

Select * from <tbl_name> where <firstparametercolumnname> like '%"& <your searchtexboxname.text here> &"%' 
OR <secondparametercolumnname> like '%"& <your searchtexboxname.text here> &"%'

and so on depending on the number of fields you want to look at. Note: "con" is my SQLConnection.

This whole code snippet will fill your DatagridView with the result of the query everytime the user key in something on your searchtextbox.

Upvotes: 1

Related Questions