CODEWITHSUNDEEP
vb.netdatagridview
Aousaf Rashid
Aousaf Rashid

Reputation: 31

Filtering DataGridView

I have a DataGridView in my winform along with a TextBox to search in the DataGridView. The DataGridView has a CheckBox column. This is how my code looks: 

 Imports System.ComponentModel
 imports system.Data.sqlclient

 Public Class MyForm
    Dim con As New SqlConnection("Data source=" & My.Settings.sqlserver & ", " & My.Settings.sqlport & ";Network Library=DBMSSOCN;initial catalog=" & My.Settings.dbname & ";User id=" & My.Settings.Username & ";Password=" & My.Settings.Password & ";")

    Dim ds As New DataSet

    Private Sub filterdata(valuetosearch As String)
        Dim srq As String = "Select * from Contacts where CONCAT([Unique id],Prefix,[First name],[Last name],Gender,Title,Company,Phone,Mobile,Fax,[b.email],[p.email],Reference,Address,[Address 2],Country,City,Zip,Facebook,GooglePlus,Instagram,Twitter,Website,Salary,Currency,[Group],[Id/Status],Note,[Added by]) like '%" & valuetosearch & "%' "
         Dim adapter As New SqlDataAdapter(srq, con)
         adapter.Fill(ds, "Contacts_table")
         userdatagrid.DataSource = ds.Tables(0)
   End Sub
   Private Sub searcgcon_TextChanged(sender As Object, e As EventArgs) Handles searcgcon.TextChanged
       If searcgcon.Text = "" Then
           filterdata("")
           entrylabel.Text = "There are/is " & userdatagrid.Rows.Count & " contact entries"
      Else
         filterdata(searcgcon.Text)
         entrylabel.Text = "There are/is " & userdatagrid.Rows.Count & " contact entries that contain your query :" & searcgcon.Text
      End If
  End Sub

  Private sub refresh_click()handles refresh.click
      filterdata("")
  end sub
 End class

I have corrected an earlier problem (see How to maintain datagridview checkstate when filtering VB.net).

Now the problem I have is when I press a button that triggers "filterdata", e.g. the refresh button or even when I click/type something in the TextBox (the one used to filter), the DataGridView keeps adding rows of the same data. Please tell me where I'm wrong.

I posted this on other sites. One guy said that my filterdata's code should be in the form's Load event. If i do that then I would face some problems like :

  1. I wouldn't have a sub called filterdata

  2. If I don't have that sub, how am i supposed to use it in the TextBox TextChanged event?

If i am still wrong, please correct me. I am just a student!

(P.S. The database contains an Image column which i can't include in the srq string as it gives me an error on adapter.fill saying "Operand type clash : Image is incompatible with nvarchar")

Upvotes: 0

Views: 549

Answers (1)

Sandy
Sandy

Reputation: 760

Update your filterdata method with below code - 

Private Sub filterdata(valuetosearch As String)
    userdatagrid.DataSource = Nothing
    Dim ds As New DataSet
    Dim srq As String = "Select * from Contacts where CONCAT([Unique id],Prefix,[First name],[Last name],Gender,Title,Company,Phone,Mobile,Fax,[b.email],[p.email],Reference,Address,[Address 2],Country,City,Zip,Facebook,GooglePlus,Instagram,Twitter,Website,Salary,Currency,[Group],[Id/Status],Note,[Added by]) like '%" & valuetosearch & "%' "
    Dim adapter As New SqlDataAdapter(srq, con)
    adapter.Fill(ds, "Contacts_table")
    userdatagrid.DataSource = ds.Tables(0)
End Sub

You have to reset you Dataset and Grid Datasource on every text change.

Upvotes: 1

Related Questions