Search through columns in a datagridview using textbox (vb.net)

Question

How do I search through columns in a datagridview using a textbox? I'm using vb.net 2010. I have a Datagridview with a data source. Below is my code for populating my datagridview. The gridview will have 4 columns.

Private Sub LoadProducts()
    Dim CS As String = ConfigurationManager.ConnectionStrings("HRMS.My.MySettings.ResortDBConnectionString").ConnectionString
    Using con As SqlConnection = New SqlConnection(CS)
        Dim da As SqlDataAdapter = New SqlDataAdapter("sp_NET_GetProducts_CompanyID", con)
        da.SelectCommand.CommandType = CommandType.StoredProcedure
        da.SelectCommand.Parameters.AddWithValue("@CompanyID", CInt(ConfigurationManager.AppSettings("CompanyID")))

        Dim ds As DataSet = New DataSet
        da.Fill(ds)
        ds.Tables(0).TableName = "Products"

        dgvProducts.DataSource = ds.Tables("Products")
        dgvProducts.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
        dgvProducts.AllowUserToResizeColumns = True
        dgvProducts.Refresh()
    End Using
End Sub

Requirements: In my form I will have a textbox and button. The textbox will supply the search string. I need a way to highlight the row when a string is found.

I don't want to open another connection just to search for a string on a dataset. Is it possible to search for string values directly in the datagridview?

Solution 1

Here you have a sample code doing what you want:

Dim toSearch As String = "this"
Dim colNum As Integer = 0
Dim res = ds.Tables("Products").AsEnumerable.Where(Function(x) x.Item(colNum).ToString() = toSearch).ToArray
For Each item In res
    Dim curRow As Integer = ds.Tables("Products").Rows.IndexOf(item)
    dgvSuppliers.Rows(curRow).DefaultCellStyle.BackColor = Color.Yellow
Next

The code above looks for the string "this" in the first column of Table "Products" and change the BackColor of the matched rows to Yellow.

NOTE: this answer intends to reply the OP's question in the way usually "searching a term in a datasource" is understood, that is, by relying on a query. Also, people tend to prefer solutions involving a lower number of lines. These two reasons explain why I relied on this approach (this together with the OP's muteness). The OP has decided to answer himself what he has considered better. I personally prefer iterative solutions like the one he posted (although I consider that this approach is evident to anyone using a DataGridView). In any case, nothing can be said a priori about which option is more efficient without knowing the exact conditions (size). The whole point of this note is highlighting that I don't recommend relying on LINQ-based approaches on a regular basis, just wrote what the OP was apparently looking for (unfortunately, I am pretty bad at interpreting the expectations of a persons not explaining clearly what is looking for and avoiding any kind of communication).

Solution 2

You can use BindingSource for your requirement.So your code will looks like below,

• Declare (Public)

  Dim ds As New DataSet
  Dim bndSourceGrid As New BindingSource()
  

• Fill dgvProducts with a BindingSource

  Private Sub LoadProducts()
  Dim CS As String = ConfigurationManager.ConnectionStrings("HRMS.My.MySettings.ResortDBConnectionString").ConnectionString
  
  Using con As SqlConnection = New SqlConnection(CS)
  Dim da As SqlDataAdapter = New SqlDataAdapter("sp_NET_GetProducts_CompanyID", con)
  da.SelectCommand.CommandType = CommandType.StoredProcedure
  da.SelectCommand.Parameters.AddWithValue("@CompanyID", CInt(ConfigurationManager.AppSettings("CompanyID")))
  
  
  da.Fill(ds)
  ds.Tables(0).TableName = "Products"
  
  '/*--------------------------------------------
  bndSourceGrid.DataSource = ds.Tables("Products")
  dgvProducts.DataSource = bndSourceGrid
  '/*--------------------------------------------
  
  dgvProducts.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
  dgvProducts.AllowUserToResizeColumns = True
  dgvProducts.Refresh()
  End Using
  
  End Sub
  

• goto txtboxSerach and on it's TextChanged event

    Private Sub txtboxSeracht_TextChanged(ByVal sender As Object, ByVal e As 
    System.EventArgs) Handles txtSearchCust.TextChanged
        '/*here "Name" is the column that you want filter/search
      bndSourceGrid.Filter = String.Format("{0} LIKE '{1}%'", "Name",   
      txtboxSerach.Text)
        '/* sorting method ascending/descending
      bndSourceGrid.Sort = "Name ASC"
    End Sub
  

• goto txtboxSerach and on it's Validated event

     Private Sub txtboxSerach_Validated(ByVal sender As Object, ByVal e As   
                          System.EventArgs) Handles txtboxSerach.Validated
         If txtboxSerach.Text = String.Empty Then
            bndSourceGrid.RemoveFilter()
         Else
            bndSourceGrid.Filter = String.Format("{0} = '{1}'", "Name", 
            txtboxSerach.Text)
           bndSourceGrid.Sort = "Name ASC"
         End If
     End Sub
  

---

• Result
  my DataGridView looks like below

   ID Name    Other
   ---------------
   0  Abcd    321
   1  Abdc    546
   2  Bcdsf   1005
  

  
  When I start typing letter A in txtBoxSerach

   ID Name    Other
   ---------------
   0  Abcd    321
   1  Abdc    546
  

Comments

[Chad Patrick]

Is it possible to add here a WHERE statement?

[Vivek S.]

@ChadPatrick msdn.microsoft.com/en-us/library/…