none
Syntax error near expression VB.Net RRS feed

  • Question

  • i want to filter my datagridview. Here's a code:

        Private Sub Contact_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With mydt
                Dim cmd As New SqlCommand("Select * from Contacts", con)
                Dim adapter As New SqlDataAdapter(cmd)
                adapter.Fill(mydt)
                .DefaultView.RowFilter = "([Unique id],Prefix) values(@uid,@prefix) LIKE '*'"
            End With

    this code gives me an exception error in the defalutview.rowfilter line.Now, on other forums, people told me that i need to write a WHERE statement in the rowfilter to filter data from multiple columns.Can anyone tell me how should i use the WHERE statement ? can anyone give me the fixed code ?

    Sunday, October 29, 2017 11:43 AM

All replies

  • Aosaf,

    I never heard of Where statement in the Rowfilter. I know that there are persons who think that everything is SQL transact code where the "where" statement exist. 

    However, I can not wee how you did brew your filter expression. 

    Here the link to the correct way to use that. 

    https://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression(v=vs.110).aspx


    Success
    Cor

    Sunday, October 29, 2017 12:42 PM
  • hey, when using this code in the textchange event :

             

     Private Sub searcgcon_TextChanged(sender As Object, e As EventArgs) Handles searcgcon.TextChanged
                mydt.DefaultView.RowFilter = "[Unique ID] LIKE'" & searcgcon.Text & "*'"

    i get an error saying that can't find caolumn [Unique id] . It is the first column of my table !!How to fix dat?

    Sunday, October 29, 2017 12:49 PM
  • i have a dgvw which loads data from an sql database.The table name is "Contacs" which has a lot of columns e.g.[first name],[last name].I have a textbox in my winform to search/filter the dgvw.What i want is that the dgvw will filter as soon as i start typing in the textbox.I mean i want to add the filtering code in the textbox textchange event.I have tried a lot of ways but each one had different problems. I must add that i have a checkbox column in the dgvw. All the codes i tried messed up with the checkbox column.E.g. Suppose i check a row's checkbox and then try to filter the dgvw,so as soon as i start filtering the checked checkboxes become unchecked.So please give me some code/way to achieve my goal !
    • Merged by KareninstructorMVP, Moderator Sunday, October 29, 2017 2:57 PM Same topic so its considered a duplicate post which is frowned upon here
    Sunday, October 29, 2017 12:56 PM
  • Hi

    Not sure, but maybe this stand alone code example will help. It doesn't use a row filter in this case.

    It does do a 'search' on a DGV on TextBox text changed.

    This is an image of the layout I have:

    and this is the code in Form1

    ' This example needs a Form1 with
    ' a blank DataGridView1, TextBox1,
    ' Button1, Button2, Button3
    ' and ListBox1
    Option Strict On
    Option Explicit On
    Public Class Form1
        ' set path for example save/load data
        Dim DataPath As String = My.Computer.FileSystem.SpecialDirectories.Desktop & "\MyData.xml"
    
        Dim myTable As New DataTable("Freddy")
        Dim view As New DataView(myTable)
        Dim Prs As New List(Of Pr)
        Dim Hcol As Color = Color.DarkRed
        Dim Hfnt As Font = New Font("Arial", 12, FontStyle.Bold)
        Dim defCol As Color = Color.Black
        Dim defFnt As Font = Nothing
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            '  add some dummy data 
            ' (not needed once a Save has been done)
            With myTable
                .Columns.Add("Name", GetType(String))
                .Columns.Add("Roll Number", GetType(String))
                .Columns.Add("Subject1", GetType(String))
                .Columns.Add("Subject2", GetType(String))
                .Columns.Add("Score", GetType(String))
                .Rows.Add("N1", 5, "Math", "Phy", 20.7456D)
                .Rows.Add("N2", 3, "Social", "Chem", 15.5D)
                .Rows.Add("N1", 1, "Math", "Phy", 11.5D)
                .Rows.Add("N2", 3, "Social", "Math", 25.7D)
                .Rows.Add("N1", 2, "Math", "Phy", 20.9D)
                .Rows.Add("N2", 1, "Social", "Math", 18.5D)
                .Rows.Add("N1", 2, "English", "Phy", 23.5D)
                .Rows.Add("N2", 3, "Social", "Math", 25.7D)
            End With
            ' adjust DGV properties
            With DataGridView1
                .DataSource = view
                .MultiSelect = False
                .RowHeadersWidth = 24
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.True
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                For i As Integer = 0 To 4
                    .Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                Next
            End With
            ' grab initial font
            defFnt = DataGridView1.DefaultCellStyle.Font
        End Sub
        Sub Srch(s As String)
            ListBox1.Items.Clear()
            Prs.Clear()
            DataGridView1.ClearSelection()
            For Each r As DataGridViewRow In DataGridView1.Rows
                If Not r.Index = DataGridView1.NewRowIndex Then
                    For Each c As DataGridViewCell In r.Cells
                        If s.Length > 0 AndAlso c.Value.ToString.ToLower.Contains(s.ToLower) Then
                            c.Style.ForeColor = Hcol
                            c.Style.Font = Hfnt
                            ListBox1.Items.Add("Row " & c.RowIndex.ToString & " Col " & c.ColumnIndex.ToString & " Title " & c.OwningColumn.HeaderText)
                            Prs.Add(New Pr With {.Row = c.RowIndex, .Column = c.ColumnIndex})
                        Else
                            c.Style.ForeColor = defCol
                            c.Style.Font = defFnt
                        End If
                    Next
                End If
            Next
        End Sub
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
            Srch(Trim(TextBox1.Text))
        End Sub
        Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged
            Dim lb As ListBox = DirectCast(sender, ListBox)
            DataGridView1.ClearSelection()
            DataGridView1(Prs(ListBox1.SelectedIndex).Column, Prs(ListBox1.SelectedIndex).Row).Selected = True
            TextBox1.Select()
        End Sub
        Class Pr
            Property Row As Integer
            Property Column As Integer
        End Class
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            ' save all the data
            myTable.WriteXml(DataPath)
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            ' load all data
            myTable.ReadXml(DataPath)
        End Sub
    
        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            ' clear out all data
            myTable.Clear()
        End Sub
    End Class


    Regards Les, Livingston, Scotland

    Sunday, October 29, 2017 1:17 PM
  • Hi

    Not sure, but maybe this stand alone code example will help. It doesn't use a row filter in this case.

    It does do a 'search' on a DGV on TextBox text changed.

    This is an image of the layout I have:

    and this is the code in Form1

    ' This example needs a Form1 with
    ' a blank DataGridView1, TextBox1,
    ' Button1, Button2, Button3
    ' and ListBox1
    Option Strict On
    Option Explicit On
    Public Class Form1
        ' set path for example save/load data
        Dim DataPath As String = My.Computer.FileSystem.SpecialDirectories.Desktop & "\MyData.xml"
    
        Dim myTable As New DataTable("Freddy")
        Dim view As New DataView(myTable)
        Dim Prs As New List(Of Pr)
        Dim Hcol As Color = Color.DarkRed
        Dim Hfnt As Font = New Font("Arial", 12, FontStyle.Bold)
        Dim defCol As Color = Color.Black
        Dim defFnt As Font = Nothing
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            '  add some dummy data 
            ' (not needed once a Save has been done)
            With myTable
                .Columns.Add("Name", GetType(String))
                .Columns.Add("Roll Number", GetType(String))
                .Columns.Add("Subject1", GetType(String))
                .Columns.Add("Subject2", GetType(String))
                .Columns.Add("Score", GetType(String))
                .Rows.Add("N1", 5, "Math", "Phy", 20.7456D)
                .Rows.Add("N2", 3, "Social", "Chem", 15.5D)
                .Rows.Add("N1", 1, "Math", "Phy", 11.5D)
                .Rows.Add("N2", 3, "Social", "Math", 25.7D)
                .Rows.Add("N1", 2, "Math", "Phy", 20.9D)
                .Rows.Add("N2", 1, "Social", "Math", 18.5D)
                .Rows.Add("N1", 2, "English", "Phy", 23.5D)
                .Rows.Add("N2", 3, "Social", "Math", 25.7D)
            End With
            ' adjust DGV properties
            With DataGridView1
                .DataSource = view
                .MultiSelect = False
                .RowHeadersWidth = 24
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.True
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                For i As Integer = 0 To 4
                    .Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                Next
            End With
            ' grab initial font
            defFnt = DataGridView1.DefaultCellStyle.Font
        End Sub
        Sub Srch(s As String)
            ListBox1.Items.Clear()
            Prs.Clear()
            DataGridView1.ClearSelection()
            For Each r As DataGridViewRow In DataGridView1.Rows
                If Not r.Index = DataGridView1.NewRowIndex Then
                    For Each c As DataGridViewCell In r.Cells
                        If s.Length > 0 AndAlso c.Value.ToString.ToLower.Contains(s.ToLower) Then
                            c.Style.ForeColor = Hcol
                            c.Style.Font = Hfnt
                            ListBox1.Items.Add("Row " & c.RowIndex.ToString & " Col " & c.ColumnIndex.ToString & " Title " & c.OwningColumn.HeaderText)
                            Prs.Add(New Pr With {.Row = c.RowIndex, .Column = c.ColumnIndex})
                        Else
                            c.Style.ForeColor = defCol
                            c.Style.Font = defFnt
                        End If
                    Next
                End If
            Next
        End Sub
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
            Srch(Trim(TextBox1.Text))
        End Sub
        Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged
            Dim lb As ListBox = DirectCast(sender, ListBox)
            DataGridView1.ClearSelection()
            DataGridView1(Prs(ListBox1.SelectedIndex).Column, Prs(ListBox1.SelectedIndex).Row).Selected = True
            TextBox1.Select()
        End Sub
        Class Pr
            Property Row As Integer
            Property Column As Integer
        End Class
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            ' save all the data
            myTable.WriteXml(DataPath)
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            ' load all data
            myTable.ReadXml(DataPath)
        End Sub
    
        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            ' clear out all data
            myTable.Clear()
        End Sub
    End Class


    Regards Les, Livingston, Scotland

    this can't be used in my case.Besides as i mentioned, i have a checkbox column.So if i use your code i will face the same problem.I mean if i check a checkbox then try to filter/search , the checkbox will lose it's checkstate...any other solution ?
    Sunday, October 29, 2017 1:42 PM
  • '" ?????

    Success
    Cor

    Sunday, October 29, 2017 2:32 PM
  • Hi

    Here is some more code, this example has a CheckBox column and filters based on the checkbox being checked.

    ' This example needs a Form1 with
    ' a blank DataGridView1, 
    ' TextBox1 and Label1
    Option Strict On
    Option Explicit On
    Public Class Form1
        Dim myTable As New DataTable("Freddy")
        Dim view As New DataView(myTable)
        Dim rand As New Random
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With myTable
                ' add some random columns
                .Columns.Add("Check", GetType(Boolean))
                .Columns.Add("ID", GetType(Integer))
                .Columns.Add("Name", GetType(String))
                .Columns.Add("Roll Number", GetType(Integer))
                .Columns.Add("Subject1", GetType(String))
                .Columns.Add("Subject2", GetType(String))
                .Columns.Add("Score", GetType(String))
    
                ' just some test data
                Dim r1() As String = {"N1", "N2", "N3", "N4"}
                Dim r2() As String = {"Math", "Social", "English", "Scottish"}
                For i As Integer = 0 To 999
                    .Rows.Add(IIf(i Mod 3 = 0, "True", "False"), i, r1(rand.Next(4)), rand.Next(111), r2(rand.Next(4)), r2(rand.Next(4)), rand.NextDouble() * 999)
                Next
                .Rows.Add(True, 3333, "N5", 999, "Sciences", "Scottish", 87.6)
                .Rows.Add(True, 3333, "N3", 999, "Math", "Math", 2)
            End With
    
            ' just some formatting of DGV
            With DataGridView1
                .DataSource = myTable
                .MultiSelect = False
                .RowHeadersWidth = 24
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.True
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                For i As Integer = 0 To 4
                    .Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                Next
            End With
            DataGridView1.DataSource = view
            Label1.Text = "Rows =" & (DataGridView1.RowCount - 1).ToString
        End Sub
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
            Dim t As String = Trim(TextBox1.Text)
            If t.Length < 1 Then
                view.RowFilter = Nothing
                Label1.Text = "Rows = " & view.Count.ToString
                Exit Sub
            End If
            Dim v As Integer = -1
            Dim additional As String = Nothing
            If Integer.TryParse(t, v) Then
                additional = "or ID = '" & v & "'"
            End If
            Try
                view.RowFilter = "Check = True and Name like '" & t & "' Or Check = True and Subject1 like '" & t & "'  Or Check = True and [Roll Number] = '" & t & "'Or Check = True and  Subject2 like ' or " & t & "' or Check = True and Score like '" & t & "'" & additional
    
            Catch ex As Exception
                MessageBox.Show("ERROR: " & ex.Message)
            End Try
            Label1.Text = "Rows = " & view.Count.ToString
        End Sub
    End Class


    Regards Les, Livingston, Scotland

    Sunday, October 29, 2017 2:38 PM
  • What exactly do you want to filter on, one column, multiple columns?

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, October 29, 2017 2:53 PM
    Moderator
  • Hi

    Here is some more code, this example has a CheckBox column and filters based on the checkbox being checked.

    ' This example needs a Form1 with
    ' a blank DataGridView1, 
    ' TextBox1 and Label1
    Option Strict On
    Option Explicit On
    Public Class Form1
        Dim myTable As New DataTable("Freddy")
        Dim view As New DataView(myTable)
        Dim rand As New Random
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With myTable
                ' add some random columns
                .Columns.Add("Check", GetType(Boolean))
                .Columns.Add("ID", GetType(Integer))
                .Columns.Add("Name", GetType(String))
                .Columns.Add("Roll Number", GetType(Integer))
                .Columns.Add("Subject1", GetType(String))
                .Columns.Add("Subject2", GetType(String))
                .Columns.Add("Score", GetType(String))
    
                ' just some test data
                Dim r1() As String = {"N1", "N2", "N3", "N4"}
                Dim r2() As String = {"Math", "Social", "English", "Scottish"}
                For i As Integer = 0 To 999
                    .Rows.Add(IIf(i Mod 3 = 0, "True", "False"), i, r1(rand.Next(4)), rand.Next(111), r2(rand.Next(4)), r2(rand.Next(4)), rand.NextDouble() * 999)
                Next
                .Rows.Add(True, 3333, "N5", 999, "Sciences", "Scottish", 87.6)
                .Rows.Add(True, 3333, "N3", 999, "Math", "Math", 2)
            End With
    
            ' just some formatting of DGV
            With DataGridView1
                .DataSource = myTable
                .MultiSelect = False
                .RowHeadersWidth = 24
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.True
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                For i As Integer = 0 To 4
                    .Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                Next
            End With
            DataGridView1.DataSource = view
            Label1.Text = "Rows =" & (DataGridView1.RowCount - 1).ToString
        End Sub
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
            Dim t As String = Trim(TextBox1.Text)
            If t.Length < 1 Then
                view.RowFilter = Nothing
                Label1.Text = "Rows = " & view.Count.ToString
                Exit Sub
            End If
            Dim v As Integer = -1
            Dim additional As String = Nothing
            If Integer.TryParse(t, v) Then
                additional = "or ID = '" & v & "'"
            End If
            Try
                view.RowFilter = "Check = True and Name like '" & t & "' Or Check = True and Subject1 like '" & t & "'  Or Check = True and [Roll Number] = '" & t & "'Or Check = True and  Subject2 like ' or " & t & "' or Check = True and Score like '" & t & "'" & additional
    
            Catch ex As Exception
                MessageBox.Show("ERROR: " & ex.Message)
            End Try
            Label1.Text = "Rows = " & view.Count.ToString
        End Sub
    End Class


    Regards Les, Livingston, Scotland

    if i use your code, how do i connect the datatable/dataview with sql db ?

    Sunday, October 29, 2017 4:20 PM
  • What exactly do you want to filter on, one column, multiple columns?

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    multiple columns mam
    Sunday, October 29, 2017 4:20 PM
  • Are all the columns type string or a mixture of different types?

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, October 29, 2017 4:33 PM
    Moderator
  • Are all the columns type string or a mixture of different types?

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    all of em are string except for one which is an image column but it's obvious i don't want to search an image LOL...anyway...what to do ?
    Sunday, October 29, 2017 4:37 PM
  • Hi

    Here is some more code, this example has a CheckBox column and filters based on the checkbox being checked.

    ' This example needs a Form1 with
    ' a blank DataGridView1, 
    ' TextBox1 and Label1
    Option Strict On
    Option Explicit On
    Public Class Form1
        Dim myTable As New DataTable("Freddy")
        Dim view As New DataView(myTable)
        Dim rand As New Random
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With myTable
                ' add some random columns
                .Columns.Add("Check", GetType(Boolean))
                .Columns.Add("ID", GetType(Integer))
                .Columns.Add("Name", GetType(String))
                .Columns.Add("Roll Number", GetType(Integer))
                .Columns.Add("Subject1", GetType(String))
                .Columns.Add("Subject2", GetType(String))
                .Columns.Add("Score", GetType(String))
    
                ' just some test data
                Dim r1() As String = {"N1", "N2", "N3", "N4"}
                Dim r2() As String = {"Math", "Social", "English", "Scottish"}
                For i As Integer = 0 To 999
                    .Rows.Add(IIf(i Mod 3 = 0, "True", "False"), i, r1(rand.Next(4)), rand.Next(111), r2(rand.Next(4)), r2(rand.Next(4)), rand.NextDouble() * 999)
                Next
                .Rows.Add(True, 3333, "N5", 999, "Sciences", "Scottish", 87.6)
                .Rows.Add(True, 3333, "N3", 999, "Math", "Math", 2)
            End With
    
            ' just some formatting of DGV
            With DataGridView1
                .DataSource = myTable
                .MultiSelect = False
                .RowHeadersWidth = 24
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.True
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                For i As Integer = 0 To 4
                    .Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                Next
            End With
            DataGridView1.DataSource = view
            Label1.Text = "Rows =" & (DataGridView1.RowCount - 1).ToString
        End Sub
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
            Dim t As String = Trim(TextBox1.Text)
            If t.Length < 1 Then
                view.RowFilter = Nothing
                Label1.Text = "Rows = " & view.Count.ToString
                Exit Sub
            End If
            Dim v As Integer = -1
            Dim additional As String = Nothing
            If Integer.TryParse(t, v) Then
                additional = "or ID = '" & v & "'"
            End If
            Try
                view.RowFilter = "Check = True and Name like '" & t & "' Or Check = True and Subject1 like '" & t & "'  Or Check = True and [Roll Number] = '" & t & "'Or Check = True and  Subject2 like ' or " & t & "' or Check = True and Score like '" & t & "'" & additional
    
            Catch ex As Exception
                MessageBox.Show("ERROR: " & ex.Message)
            End Try
            Label1.Text = "Rows = " & view.Count.ToString
        End Sub
    End Class


    Regards Les, Livingston, Scotland

    if i use your code, how do i connect the datatable/dataview with sql db ?

    this is my textbox textchange code ..it gives me MISSING OPERAND  BEFORE = OPERATOR 

          Dim t As String = Trim(searcgcon.Text)
            If t.Length < 1 Then
                dv.RowFilter = Nothing
                Label1.Text = "Rows = " & dv.Count.ToString
                Exit Sub
            End If
            Dim v As Integer = -1
            Dim additional As String = Nothing
            If Integer.TryParse(t, v) Then
                additional = "or ID = '" & v & "'"
            End If

            dv.RowFilter = "Check = True and [Unique ID] like '" & t & "' Or Check = True and [First name] like '" & t & "'  Or Check = True and [Last name] like = '" & t & "'Or Check = True and  gender like ' or " & t & "' or Check = True and [b.email] like '" & t & "' or Check = True and [p.email] like '" & t & "'" & additional

    Sunday, October 29, 2017 4:59 PM
  • Okay,

    The best way to think of this is to first work it out in the database via SQL e.g.

    USE ForumExamples;
    -- this would be the value to filter on when
    -- doing this for the DataView Filter
    DECLARE @SeekValue AS NVARCHAR = '%en%';
    
    SELECT  id ,
            FirstName ,
            LastName ,
            GenderIdentifier ,
            IsDeleted
    FROM    Persons1
    WHERE   FirstName LIKE @SeekValue
            OR LastName LIKE @SeekValue;

    We then see about moving this to code e.g.

    ' conceptual example that would be used in an event of the TextBox
    ' does not account for embedded apostrophes which must be escaped
    ' e.g. txtSearch.Text.Replace("'","''")
    Dim dt As New DataTable
    Dim dv As DataView = dt.DefaultView
    dv.RowFilter = $"FirstName LIKE '%{txtSearch.Text}%' OR LastName LIKE '%{txtSearch.Text}%'"
    Once we know the above you would then apply it to your code and then test it, add assertion as needed e.g. if the TextBox is empty remove the filter e.g. RowFilter = "" etc.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, October 29, 2017 6:00 PM
    Moderator
  • ' This example needs a Form1 with
    ' a blank DataGridView1, 
    ' TextBox1 and Label1
    Option Strict On
    Option Explicit On
    Public Class Form1
        Dim myTable As New DataTable("Freddy")
        Dim view As New DataView(myTable)
        Dim rand As New Random
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With myTable
                ' add some random columns
                .Columns.Add("Check", GetType(Boolean))
                .Columns.Add("ID", GetType(Integer))
                .Columns.Add("Name", GetType(String))
                .Columns.Add("Roll Number", GetType(Integer))
                .Columns.Add("Subject1", GetType(String))
                .Columns.Add("Subject2", GetType(String))
                .Columns.Add("Score", GetType(String))
    
                ' just some test data
                Dim r1() As String = {"N1", "N2", "N3", "N4"}
                Dim r2() As String = {"Math", "Social", "English", "Scottish"}
                For i As Integer = 0 To 999
                    .Rows.Add(IIf(i Mod 3 = 0, "True", "False"), i, r1(rand.Next(4)), rand.Next(111), r2(rand.Next(4)), r2(rand.Next(4)), rand.NextDouble() * 999)
                Next
                .Rows.Add(True, 3333, "N5", 999, "Sciences", "Scottish", 87.6)
                .Rows.Add(True, 3333, "N3", 999, "Math", "Math", 2)
            End With
    
            ' just some formatting of DGV
            With DataGridView1
                .DataSource = myTable
                .MultiSelect = False
                .RowHeadersWidth = 24
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.True
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                For i As Integer = 0 To 4
                    .Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                Next
            End With
            DataGridView1.DataSource = view
            Label1.Text = "Rows =" & (DataGridView1.RowCount - 1).ToString
        End Sub
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
            Dim t As String = Trim(TextBox1.Text)
            If t.Length < 1 Then
                view.RowFilter = Nothing
                Label1.Text = "Rows = " & view.Count.ToString
                Exit Sub
            End If
            Dim v As Integer = -1
            Dim additional As String = Nothing
            If Integer.TryParse(t, v) Then
                additional = "or ID = '" & v & "'"
            End If
            Try
                view.RowFilter = "Check = True and Name like '" & t & "' Or Check = True and Subject1 like '" & t & "'  Or Check = True and [Roll Number] = '" & t & "'Or Check = True and  Subject2 like ' or " & t & "' or Check = True and Score like '" & t & "'" & additional
    
            Catch ex As Exception
                MessageBox.Show("ERROR: " & ex.Message)
            End Try
            Label1.Text = "Rows = " & view.Count.ToString
        End Sub
    End Class


    Regards Les, Livingston, Scotland

    if i use your code, how do i connect the datatable/dataview with sql db ?

    this is my textbox textchange code ..it gives me MISSING OPERAND  BEFORE = OPERATOR 

          Dim t As String = Trim(searcgcon.Text)
            If t.Length < 1 Then
                dv.RowFilter = Nothing
                Label1.Text = "Rows = " & dv.Count.ToString
                Exit Sub
            End If
         
            dv.RowFilter = "Check = True and [Unique ID] like '" & t & "' Or Check = True and [First name] like '" & t & "'  Or Check = True and [Last name] like = '" & t & "'Or Check = True and  gender like ' or " & t & "' or Check = True and [b.email] like '" & t & "' or Check = True and [p.email] like '" & t & "'" & additional

    Hi

    Try replacing the dv.RowFilter line with

     dv.RowFilter = "Check = True and [Unique ID] = '" & t & "' Or Check = True and [First name] like '" & t & "'  Or Check = True and [Last name] like '" & t & "' Or Check = True and gender like '" & t & "' or Check = True and [b.email] like '" & t & "' or Check = True and [p.email] like '" & t & "'"
    the bold text above might have been the problem.


    Regards Les, Livingston, Scotland




    • Edited by leshay Sunday, October 29, 2017 6:24 PM
    Sunday, October 29, 2017 6:20 PM
  • ' This example needs a Form1 with
    ' a blank DataGridView1, 
    ' TextBox1 and Label1
    Option Strict On
    Option Explicit On
    Public Class Form1
        Dim myTable As New DataTable("Freddy")
        Dim view As New DataView(myTable)
        Dim rand As New Random
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With myTable
                ' add some random columns
                .Columns.Add("Check", GetType(Boolean))
                .Columns.Add("ID", GetType(Integer))
                .Columns.Add("Name", GetType(String))
                .Columns.Add("Roll Number", GetType(Integer))
                .Columns.Add("Subject1", GetType(String))
                .Columns.Add("Subject2", GetType(String))
                .Columns.Add("Score", GetType(String))
    
                ' just some test data
                Dim r1() As String = {"N1", "N2", "N3", "N4"}
                Dim r2() As String = {"Math", "Social", "English", "Scottish"}
                For i As Integer = 0 To 999
                    .Rows.Add(IIf(i Mod 3 = 0, "True", "False"), i, r1(rand.Next(4)), rand.Next(111), r2(rand.Next(4)), r2(rand.Next(4)), rand.NextDouble() * 999)
                Next
                .Rows.Add(True, 3333, "N5", 999, "Sciences", "Scottish", 87.6)
                .Rows.Add(True, 3333, "N3", 999, "Math", "Math", 2)
            End With
    
            ' just some formatting of DGV
            With DataGridView1
                .DataSource = myTable
                .MultiSelect = False
                .RowHeadersWidth = 24
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.True
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                For i As Integer = 0 To 4
                    .Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                Next
            End With
            DataGridView1.DataSource = view
            Label1.Text = "Rows =" & (DataGridView1.RowCount - 1).ToString
        End Sub
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
            Dim t As String = Trim(TextBox1.Text)
            If t.Length < 1 Then
                view.RowFilter = Nothing
                Label1.Text = "Rows = " & view.Count.ToString
                Exit Sub
            End If
            Dim v As Integer = -1
            Dim additional As String = Nothing
            If Integer.TryParse(t, v) Then
                additional = "or ID = '" & v & "'"
            End If
            Try
                view.RowFilter = "Check = True and Name like '" & t & "' Or Check = True and Subject1 like '" & t & "'  Or Check = True and [Roll Number] = '" & t & "'Or Check = True and  Subject2 like ' or " & t & "' or Check = True and Score like '" & t & "'" & additional
    
            Catch ex As Exception
                MessageBox.Show("ERROR: " & ex.Message)
            End Try
            Label1.Text = "Rows = " & view.Count.ToString
        End Sub
    End Class


    Regards Les, Livingston, Scotland

    if i use your code, how do i connect the datatable/dataview with sql db ?

    this is my textbox textchange code ..it gives me MISSING OPERAND  BEFORE = OPERATOR 

          Dim t As String = Trim(searcgcon.Text)
            If t.Length < 1 Then
                dv.RowFilter = Nothing
                Label1.Text = "Rows = " & dv.Count.ToString
                Exit Sub
            End If
         
            dv.RowFilter = "Check = True and [Unique ID] like '" & t & "' Or Check = True and [First name] like '" & t & "'  Or Check = True and [Last name] like = '" & t & "'Or Check = True and  gender like ' or " & t & "' or Check = True and [b.email] like '" & t & "' or Check = True and [p.email] like '" & t & "'" & additional

    Hi

    Try replacing the dv.RowFilter line with

     dv.RowFilter = "Check = True and [Unique ID] = '" & t & "' Or Check = True and [First name] like '" & t & "'  Or Check = True and [Last name] like '" & t & "' Or Check = True and gender like '" & t & "' or Check = True and [b.email] like '" & t & "' or Check = True and [p.email] like '" & t & "'"
    the bold text above might have been the problem.


    Regards Les, Livingston, Scotland




    When i type anything in the textbox, it says CAN'T FIND COLUMN CHECK.Check is my first column in the dgvw..I even changed it's name and header text to check but still same error !
    Monday, October 30, 2017 4:08 AM
  • Okay,

    The best way to think of this is to first work it out in the database via SQL e.g.

    USE ForumExamples;
    -- this would be the value to filter on when
    -- doing this for the DataView Filter
    DECLARE @SeekValue AS NVARCHAR = '%en%';
    
    SELECT  id ,
            FirstName ,
            LastName ,
            GenderIdentifier ,
            IsDeleted
    FROM    Persons1
    WHERE   FirstName LIKE @SeekValue
            OR LastName LIKE @SeekValue;

    We then see about moving this to code e.g.

    ' conceptual example that would be used in an event of the TextBox
    ' does not account for embedded apostrophes which must be escaped
    ' e.g. txtSearch.Text.Replace("'","''")
    Dim dt As New DataTable
    Dim dv As DataView = dt.DefaultView
    dv.RowFilter = $"FirstName LIKE '%{txtSearch.Text}%' OR LastName LIKE '%{txtSearch.Text}%'"
    Once we know the above you would then apply it to your code and then test it, add assertion as needed e.g. if the TextBox is empty remove the filter e.g. RowFilter = "" etc.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    maam , i tried this solution long ago.The problem remains.If i check a row's checkbox,then try to filter , it becomes unchecked...How to maintain the checkbox check state?

    Monday, October 30, 2017 4:59 AM
  • ' This example needs a Form1 with
    ' a blank DataGridView1, 
    ' TextBox1 and Label1
    Option Strict On
    Option Explicit On
    Public Class Form1
        Dim myTable As New DataTable("Freddy")
        Dim view As New DataView(myTable)
        Dim rand As New Random
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With myTable
                ' add some random columns
                .Columns.Add("Check", GetType(Boolean))
                .Columns.Add("ID", GetType(Integer))
                .Columns.Add("Name", GetType(String))
                .Columns.Add("Roll Number", GetType(Integer))
                .Columns.Add("Subject1", GetType(String))
                .Columns.Add("Subject2", GetType(String))
                .Columns.Add("Score", GetType(String))
    
                ' just some test data
                Dim r1() As String = {"N1", "N2", "N3", "N4"}
                Dim r2() As String = {"Math", "Social", "English", "Scottish"}
                For i As Integer = 0 To 999
                    .Rows.Add(IIf(i Mod 3 = 0, "True", "False"), i, r1(rand.Next(4)), rand.Next(111), r2(rand.Next(4)), r2(rand.Next(4)), rand.NextDouble() * 999)
                Next
                .Rows.Add(True, 3333, "N5", 999, "Sciences", "Scottish", 87.6)
                .Rows.Add(True, 3333, "N3", 999, "Math", "Math", 2)
            End With
    
            ' just some formatting of DGV
            With DataGridView1
                .DataSource = myTable
                .MultiSelect = False
                .RowHeadersWidth = 24
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.True
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                For i As Integer = 0 To 4
                    .Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                Next
            End With
            DataGridView1.DataSource = view
            Label1.Text = "Rows =" & (DataGridView1.RowCount - 1).ToString
        End Sub
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
            Dim t As String = Trim(TextBox1.Text)
            If t.Length < 1 Then
                view.RowFilter = Nothing
                Label1.Text = "Rows = " & view.Count.ToString
                Exit Sub
            End If
            Dim v As Integer = -1
            Dim additional As String = Nothing
            If Integer.TryParse(t, v) Then
                additional = "or ID = '" & v & "'"
            End If
            Try
                view.RowFilter = "Check = True and Name like '" & t & "' Or Check = True and Subject1 like '" & t & "'  Or Check = True and [Roll Number] = '" & t & "'Or Check = True and  Subject2 like ' or " & t & "' or Check = True and Score like '" & t & "'" & additional
    
            Catch ex As Exception
                MessageBox.Show("ERROR: " & ex.Message)
            End Try
            Label1.Text = "Rows = " & view.Count.ToString
        End Sub
    End Class


    Regards Les, Livingston, Scotland

    if i use your code, how do i connect the datatable/dataview with sql db ?

    this is my textbox textchange code ..it gives me MISSING OPERAND  BEFORE = OPERATOR 

          Dim t As String = Trim(searcgcon.Text)
            If t.Length < 1 Then
                dv.RowFilter = Nothing
                Label1.Text = "Rows = " & dv.Count.ToString
                Exit Sub
            End If
         
            dv.RowFilter = "Check = True and [Unique ID] like '" & t & "' Or Check = True and [First name] like '" & t & "'  Or Check = True and [Last name] like = '" & t & "'Or Check = True and  gender like ' or " & t & "' or Check = True and [b.email] like '" & t & "' or Check = True and [p.email] like '" & t & "'" & additional

    Hi

    Try replacing the dv.RowFilter line with

     dv.RowFilter = "Check = True and [Unique ID] = '" & t & "' Or Check = True and [First name] like '" & t & "'  Or Check = True and [Last name] like '" & t & "' Or Check = True and gender like '" & t & "' or Check = True and [b.email] like '" & t & "' or Check = True and [p.email] like '" & t & "'"
    the bold text above might have been the problem.


    Regards Les, Livingston, Scotland




    When i type anything in the textbox, it says CAN'T FIND COLUMN CHECK.Check is my first column in the dgvw..I even changed it's name and header text to check but still same error !
    ow forgot to mention...my first column's header text is empty (just some spaces).The reason for this i s added a checkbox to the header using some code,so any fix?
    Monday, October 30, 2017 5:03 AM
  • Hi Aousaf,

    Based on your description, you want to use DataGrid to filter data in datagridview, I find one simple that you can refer to:

    Dim dt As New DataTable
        Dim source1 As New BindingSource()
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            dt.Columns.Add("Column1", GetType(Int32))
            dt.Columns.Add("Column2", GetType(String))
            dt.Columns.Add("Column3", GetType(String))
            dt.Columns.Add("Column4", GetType(String))
    
            dt.Rows.Add(1, "Apple", "AA", "AAA")
            dt.Rows.Add(2, "Bule", "BB", "AAA")
            dt.Rows.Add(3, "Color", "CC", "AAA")
            dt.Rows.Add(4, "Due", "DD", "AAA")
            dt.Rows.Add(5, "Eve", "EE", "AAA")
            dt.Rows.Add(6, "Filter", "FF", "AAA")
            dt.Rows.Add(7, "Green", "GG", "AAA")
            dt.Rows.Add(8, "High", "HH", "AAA")
            dt.Rows.Add(9, "Ill", "II", "AAA")
            dt.Rows.Add(10, "Jillter", "JJ", "AAA")
            Dim view1 As New DataView(dt)
            source1.DataSource = view1
            DataGridView1.DataSource = view1
            DataGridView1.Refresh()
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            source1.Filter = "Column2 = '" & TextBox1.Text & "'"
            DataGridView1.Refresh()
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            source1.Filter = "Column2 like '%" & TextBox1.Text & "%'"
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 30, 2017 9:53 AM
    Moderator
  • Hi Aousaf,

    Based on your description, you want to use DataGrid to filter data in datagridview, I find one simple that you can refer to:

    Dim dt As New DataTable
        Dim source1 As New BindingSource()
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            dt.Columns.Add("Column1", GetType(Int32))
            dt.Columns.Add("Column2", GetType(String))
            dt.Columns.Add("Column3", GetType(String))
            dt.Columns.Add("Column4", GetType(String))
    
            dt.Rows.Add(1, "Apple", "AA", "AAA")
            dt.Rows.Add(2, "Bule", "BB", "AAA")
            dt.Rows.Add(3, "Color", "CC", "AAA")
            dt.Rows.Add(4, "Due", "DD", "AAA")
            dt.Rows.Add(5, "Eve", "EE", "AAA")
            dt.Rows.Add(6, "Filter", "FF", "AAA")
            dt.Rows.Add(7, "Green", "GG", "AAA")
            dt.Rows.Add(8, "High", "HH", "AAA")
            dt.Rows.Add(9, "Ill", "II", "AAA")
            dt.Rows.Add(10, "Jillter", "JJ", "AAA")
            Dim view1 As New DataView(dt)
            source1.DataSource = view1
            DataGridView1.DataSource = view1
            DataGridView1.Refresh()
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            source1.Filter = "Column2 = '" & TextBox1.Text & "'"
            DataGridView1.Refresh()
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            source1.Filter = "Column2 like '%" & TextBox1.Text & "%'"
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    What is button 2 and button 1 for ? and how do i search in multiple columns ?
    Monday, October 30, 2017 11:54 AM

  • When i type anything in the textbox, it says CAN'T FIND COLUMN CHECK.Check is my first column in the dgvw..I even changed it's name and header text to check but still same error !

    ow forgot to mention...my first column's header text is empty (just some spaces).The reason for this i s added a checkbox to the header using some code,so any fix?

    Hi

    Of course, getting the names right is essential.

    Please post the column NAMES of those you want included in the filter, and the types of the data in those columns(string / integer etc). Assuming you are using a DataTable as the source.

    NOTE: the NAMES, are not necessarily the same as the header text if you are not using a datasource.


    Regards Les, Livingston, Scotland

    Monday, October 30, 2017 12:26 PM

  • When i type anything in the textbox, it says CAN'T FIND COLUMN CHECK.Check is my first column in the dgvw..I even changed it's name and header text to check but still same error !

    ow forgot to mention...my first column's header text is empty (just some spaces).The reason for this i s added a checkbox to the header using some code,so any fix?

    Hi

    Of course, getting the names right is essential.

    Please post the column NAMES of those you want included in the filter, and the types of the data in those columns(string / integer etc). Assuming you are using a DataTable as the source.

    NOTE: the NAMES, are not necessarily the same as the header text if you are not using a datasource.


    Regards Les, Livingston, Scotland

    the column names are [Unique ID],[First name],[Last name].gender and other...These columns are already in the sql database.But the checkbox column is added manually in the dgvw...any solutioon ?


    Monday, October 30, 2017 12:36 PM
  • Hi Aousaf,

    Based on your description, you want to use DataGrid to filter data in datagridview, I find one simple that you can refer to:

    Dim dt As New DataTable
        Dim source1 As New BindingSource()
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            dt.Columns.Add("Column1", GetType(Int32))
            dt.Columns.Add("Column2", GetType(String))
            dt.Columns.Add("Column3", GetType(String))
            dt.Columns.Add("Column4", GetType(String))
    
            dt.Rows.Add(1, "Apple", "AA", "AAA")
            dt.Rows.Add(2, "Bule", "BB", "AAA")
            dt.Rows.Add(3, "Color", "CC", "AAA")
            dt.Rows.Add(4, "Due", "DD", "AAA")
            dt.Rows.Add(5, "Eve", "EE", "AAA")
            dt.Rows.Add(6, "Filter", "FF", "AAA")
            dt.Rows.Add(7, "Green", "GG", "AAA")
            dt.Rows.Add(8, "High", "HH", "AAA")
            dt.Rows.Add(9, "Ill", "II", "AAA")
            dt.Rows.Add(10, "Jillter", "JJ", "AAA")
            Dim view1 As New DataView(dt)
            source1.DataSource = view1
            DataGridView1.DataSource = view1
            DataGridView1.Refresh()
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            source1.Filter = "Column2 = '" & TextBox1.Text & "'"
            DataGridView1.Refresh()
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            source1.Filter = "Column2 like '%" & TextBox1.Text & "%'"
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    What is button 2 and button 1 for ? and how do i search in multiple columns ?
    tried ur solution , still the same problem.The checkbox column loses it's checkstate
    Monday, October 30, 2017 12:55 PM

  • When i type anything in the textbox, it says CAN'T FIND COLUMN CHECK.Check is my first column in the dgvw..I even changed it's name and header text to check but still same error !

    ow forgot to mention...my first column's header text is empty (just some spaces).The reason for this i s added a checkbox to the header using some code,so any fix?

    Hi

    Of course, getting the names right is essential.

    Please post the column NAMES of those you want included in the filter, and the types of the data in those columns(string / integer etc). Assuming you are using a DataTable as the source.

    NOTE: the NAMES, are not necessarily the same as the header text if you are not using a datasource.


    Regards Les, Livingston, Scotland

    the column names are [Unique ID],[First name],[Last name].gender and other...There columns are already in the sql database.But the checkbox column is added manually in the dgvw...any solutioon ?

    Hi

    Here is some amended code (from previously posted).

    This code uses the column names fromyour last reply, PLUS a checkbox column (that you mentioned earlier - but failed to mentionlast reply?).

    The filter will apply to all columns. If user enters "true" or "false" then only the checkbox column is filtered. Any other entry is applied to all columns where "Check" column is Checked. (that is something not explained but assumed by me. This column may need to be dealt with differently in your project.

    This thread further shows that the quality of an answer depends entirely on the quality of the question.

    Here is the amended code. Try it out first as a stand alone project to see if it does anything like you want.

    ' This example needs a Form1 with
    ' a blank DataGridView1, 
    ' TextBox1 and Label1
    Option Strict On
    Option Explicit On
    Public Class Form1
        Dim myTable As New DataTable("Freddy")
        Dim view As New DataView(myTable)
        Dim rand As New Random
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With myTable
                ' add some random columns
                .Columns.Add("Check", GetType(Boolean))
                .Columns.Add("Unique ID", GetType(Integer))
                .Columns.Add("First name", GetType(String))
                .Columns.Add("Last name", GetType(String))
                .Columns.Add("Gender", GetType(String))
    
                ' just some test data
                Dim r1() As String = {"Name1", "Name2", "Name3", "Name4", "Name5", "Name6", "Name7", "Name8", "Name9", "Name10"}
                Dim r2() As String = {"Male", "Female", "None"}
                For i As Integer = 0 To 999
                    .Rows.Add(IIf(rand.Next(2) = 0, "True", "False"), i, r1(rand.Next(r1.Length)), r1(rand.Next(r1.Length)), r2(rand.Next(r2.Length)))
                Next
                .Rows.Add(True, 3333, "Freddy", "MacHine", "Male")
                .Rows.Add(True, 3334, "Mary", "Smith", "Female")
                .Rows.Add(True, 3335, "George", "Mathews", "undeclared")
            End With
    
            ' just some formatting of DGV
            With DataGridView1
                .DataSource = myTable
                .MultiSelect = False
                .RowHeadersWidth = 24
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.True
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                For i As Integer = 0 To 4
                    .Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                Next
            End With
            DataGridView1.DataSource = view
            Label1.Text = "Rows =" & (DataGridView1.RowCount - 1).ToString
        End Sub
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
            Dim t As String = Trim(TextBox1.Text)
            If t.Length < 1 Then
                view.RowFilter = Nothing
                Label1.Text = "Rows = " & view.Count.ToString
                Exit Sub
            End If
            If t.ToLower = "true" Or t.ToLower = "false" Then
                view.RowFilter = "Check = '" & t & "'"
            Else
                Dim v As Integer = -1
                Dim intField1 As String = Nothing
                If Integer.TryParse(t, v) Then
                    intField1 = "[Unique ID] = '" & t & "' or "
                End If
                Try
                    view.RowFilter = intField1 & "Check = True and [First name] like '" & t & "' Or Check = True and [Last name] like '" & t & "'" & " Or Check = True and Gender like '" & t & "'"
                Catch ex As Exception
                    MessageBox.Show("ERROR: " & ex.Message)
                End Try
            End If
            Label1.Text = "Rows = " & view.Count.ToString
        End Sub
    End Class


    Regards Les, Livingston, Scotland


    • Edited by leshay Monday, October 30, 2017 1:14 PM
    Monday, October 30, 2017 1:10 PM

  • When i type anything in the textbox, it says CAN'T FIND COLUMN CHECK.Check is my first column in the dgvw..I even changed it's name and header text to check but still same error !

    ow forgot to mention...my first column's header text is empty (just some spaces).The reason for this i s added a checkbox to the header using some code,so any fix?

    Hi

    Of course, getting the names right is essential.

    Please post the column NAMES of those you want included in the filter, and the types of the data in those columns(string / integer etc). Assuming you are using a DataTable as the source.

    NOTE: the NAMES, are not necessarily the same as the header text if you are not using a datasource.


    Regards Les, Livingston, Scotland

    the column names are [Unique ID],[First name],[Last name].gender and other...There columns are already in the sql database.But the checkbox column is added manually in the dgvw...any solutioon ?

    Hi

    Here is some amended code (from previously posted).

    This code uses the column names fromyour last reply, PLUS a checkbox column (that you mentioned earlier - but failed to mentionlast reply?).

    The filter will apply to all columns. If user enters "true" or "false" then only the checkbox column is filtered. Any other entry is applied to all columns where "Check" column is Checked. (that is something not explained but assumed by me. This column may need to be dealt with differently in your project.

    This thread further shows that the quality of an answer depends entirely on the quality of the question.

    Here is the amended code. Try it out first as a stand alone project to see if it does anything like you want.

    ' This example needs a Form1 with
    ' a blank DataGridView1, 
    ' TextBox1 and Label1
    Option Strict On
    Option Explicit On
    Public Class Form1
        Dim myTable As New DataTable("Freddy")
        Dim view As New DataView(myTable)
        Dim rand As New Random
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With myTable
                ' add some random columns
                .Columns.Add("Check", GetType(Boolean))
                .Columns.Add("Unique ID", GetType(Integer))
                .Columns.Add("First name", GetType(String))
                .Columns.Add("Last name", GetType(String))
                .Columns.Add("Gender", GetType(String))
    
                ' just some test data
                Dim r1() As String = {"Name1", "Name2", "Name3", "Name4", "Name5", "Name6", "Name7", "Name8", "Name9", "Name10"}
                Dim r2() As String = {"Male", "Female", "None"}
                For i As Integer = 0 To 999
                    .Rows.Add(IIf(rand.Next(2) = 0, "True", "False"), i, r1(rand.Next(r1.Length)), r1(rand.Next(r1.Length)), r2(rand.Next(r2.Length)))
                Next
                .Rows.Add(True, 3333, "Freddy", "MacHine", "Male")
                .Rows.Add(True, 3334, "Mary", "Smith", "Female")
                .Rows.Add(True, 3335, "George", "Mathews", "undeclared")
            End With
    
            ' just some formatting of DGV
            With DataGridView1
                .DataSource = myTable
                .MultiSelect = False
                .RowHeadersWidth = 24
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.True
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                For i As Integer = 0 To 4
                    .Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                Next
            End With
            DataGridView1.DataSource = view
            Label1.Text = "Rows =" & (DataGridView1.RowCount - 1).ToString
        End Sub
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
            Dim t As String = Trim(TextBox1.Text)
            If t.Length < 1 Then
                view.RowFilter = Nothing
                Label1.Text = "Rows = " & view.Count.ToString
                Exit Sub
            End If
            If t.ToLower = "true" Or t.ToLower = "false" Then
                view.RowFilter = "Check = '" & t & "'"
            Else
                Dim v As Integer = -1
                Dim intField1 As String = Nothing
                If Integer.TryParse(t, v) Then
                    intField1 = "[Unique ID] = '" & t & "' or "
                End If
                Try
                    view.RowFilter = intField1 & "Check = True and [First name] like '" & t & "' Or Check = True and [Last name] like '" & t & "'" & " Or Check = True and Gender like '" & t & "'"
                Catch ex As Exception
                    MessageBox.Show("ERROR: " & ex.Message)
                End Try
            End If
            Label1.Text = "Rows = " & view.Count.ToString
        End Sub
    End Class


    Regards Les, Livingston, Scotland


    wait!! I said....suppose a checkbox is checked and then the user types something in the textbox to search...i want the checkbox to keep it's checkstate no matter what the user types in the textbox..i have said it for a couple o' times already....are u sure ur one is the perfect solution ?
    Monday, October 30, 2017 1:33 PM
  • wait!! I said....suppose a checkbox is checked and then the user types something in the textbox to search...i want the checkbox to keep it's checkstate no matter what the user types in the textbox..i have said it for a couple o' times already....are u sure ur one is the perfect solution ?

    Hi

    The code I posted changes NOTHING in the data, it ONLY performs the filter.

    The checkbox(es) DO keep their state. It matters not a jot how many times you said it.

    I am definately NOT sure my offering is a perfect solution. That is up to you to decide. So far, you have not mentioned whether or not the code I provided has successfully run as a stand alone project.


    Regards Les, Livingston, Scotland

    Monday, October 30, 2017 1:44 PM
  • wait!! I said....suppose a checkbox is checked and then the user types something in the textbox to search...i want the checkbox to keep it's checkstate no matter what the user types in the textbox..i have said it for a couple o' times already....are u sure ur one is the perfect solution ?

    Hi

    The code I posted changes NOTHING in the data, it ONLY performs the filter.

    The checkbox(es) DO keep their state. It matters not a jot how many times you said it.

    I am definately NOT sure my offering is a perfect solution. That is up to you to decide. So far, you have not mentioned whether or not the code I provided has successfully run as a stand alone project.


    Regards Les, Livingston, Scotland

    okey i will apply your code...just gimme a minute
    Monday, October 30, 2017 1:49 PM
  • wait!! I said....suppose a checkbox is checked and then the user types something in the textbox to search...i want the checkbox to keep it's checkstate no matter what the user types in the textbox..i have said it for a couple o' times already....are u sure ur one is the perfect solution ?

    Hi

    The code I posted changes NOTHING in the data, it ONLY performs the filter.

    The checkbox(es) DO keep their state. It matters not a jot how many times you said it.

    I am definately NOT sure my offering is a perfect solution. That is up to you to decide. So far, you have not mentioned whether or not the code I provided has successfully run as a stand alone project.


    Regards Les, Livingston, Scotland

    and hold on a sec...u e manually adding columns to myTable...but as i said i am retrieving data from a sql database..so why would i add rows manually ? i am using sql dataadapter instead ....so...is the sqldataadapter causing the prob ?
    Monday, October 30, 2017 1:51 PM
  • ....... So far, you have not mentioned whether or not the code I provided has successfully run as a stand alone project.


    Regards Les, Livingston, Scotland

    and hold on a sec...u e manually adding columns to myTable...but as i said i am retrieving data from a sql database..so why would i add rows manually ? i am using sql dataadapter instead ....so...is the sqldataadapter causing the prob ?

    Hi

    The word you are looking for is 'you'- it is only two letters more to type.

    As far as 'u e manually adding columns' is concerned. I add NOTHING to your data.

    *

    I will ask yet again. Have you tried to run the code I gave as a stand alone project?


    Regards Les, Livingston, Scotland

    Monday, October 30, 2017 1:56 PM
  • wait!! I said....suppose a checkbox is checked and then the user types something in the textbox to search...i want the checkbox to keep it's checkstate no matter what the user types in the textbox..i have said it for a couple o' times already....are u sure ur one is the perfect solution ?

    Hi

    The code I posted changes NOTHING in the data, it ONLY performs the filter.

    The checkbox(es) DO keep their state. It matters not a jot how many times you said it.

    I am definately NOT sure my offering is a perfect solution. That is up to you to decide. So far, you have not mentioned whether or not the code I provided has successfully run as a stand alone project.


    Regards Les, Livingston, Scotland

    and hold on a sec...u e manually adding columns to myTable...but as i said i am retrieving data from a sql database..so why would i add rows manually ? i am using sql dataadapter instead ....so...is the sqldataadapter causing the prob ?

    it's again saying CANT FIND COLUMN [CHECK]???  CHECK is the first column of my dgvw !! Why can't it find it ??????? in your code , u added a checkbox column using code to the data table.... and i added it from the dgvw properies... here's my code :

       Public Class Contact

    Dim dv As New DataView
        Dim ds As New DataSet

      Private Sub Contact_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim sql As String = "Select Pic,[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] from Contacts"
            Dim adapter As New SqlDataAdapter

            adapter.SelectCommand = New SqlCommand(sql, con)
            adapter.Fill(ds, "Contacts")
            dv = ds.Tables(0).DefaultView
            source1.DataSource = dv
            userdatagrid.DataSource = dv

    so as u can see i am not using a datatable...what can i do ? 

    Monday, October 30, 2017 2:02 PM
  • Hi

    The view.rowfilter is filtering rows of the datatable, not the datagroidview.

    As commented in the code I posted, the example needs a BLANK datagridview. The DataGridView is ONLY the display element of the data.

    If you are adding a column to the datagridview, the the filter will not work.

    Since you say 'CANT FIND COLUMN [CHECK]???  CHECK is the first column of my dgvw', I think you are not running the code I posted as a stand alone project.

    Kareninstructor posted about using a database, perhaps you need to follow up with Karen now.


    Regards Les, Livingston, Scotland

    Monday, October 30, 2017 2:37 PM
  • Hi

    The view.rowfilter is filtering rows of the datatable, not the datagroidview.

    As commented in the code I posted, the example needs a BLANK datagridview. The DataGridView is ONLY the display element of the data.

    If you are adding a column to the datagridview, the the filter will not work.

    Since you say 'CANT FIND COLUMN [CHECK]???  CHECK is the first column of my dgvw', I think you are not running the code I posted as a stand alone project.

    Kareninstructor posted about using a database, perhaps you need to follow up with Karen now.


    Regards Les, Livingston, Scotland

    so...if i use a db,ur code wouldn't work ? any solution while using a db ? I posted a similar question and karen is suggesting the same thing! please, i need help wid the db thing!
    Monday, October 30, 2017 2:42 PM
  • Hi

    Karen (and others) are expert database coders. Be patient and I suggest you now close this thread and concentrate on the newer thread.

    As an exercise, you could try the following as a stand alone test where I have removed all references to the Check column, and see if you can at least get it to work.

    ' This example needs a Form1 with
    ' a blank DataGridView1, 
    ' TextBox1 and Label1
    Option Strict On
    Option Explicit On
    Public Class Form1
        Dim myTable As New DataTable("Freddy")
        Dim view As New DataView(myTable)
        Dim rand As New Random
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With myTable
                ' add some random columns
                .Columns.Add("Unique ID", GetType(Integer))
                .Columns.Add("First name", GetType(String))
                .Columns.Add("Last name", GetType(String))
                .Columns.Add("Gender", GetType(String))
    
                ' just some test data
                Dim r1() As String = {"Name1", "Name2", "Name3", "Name4", "Name5", "Name6", "Name7", "Name8", "Name9", "Name10"}
                Dim r2() As String = {"Male", "Female", "None"}
                For i As Integer = 0 To 999
                    .Rows.Add(i, r1(rand.Next(r1.Length)), r1(rand.Next(r1.Length)), r2(rand.Next(r2.Length)))
                Next
                .Rows.Add(3333, "Freddy", "MacHine", "Male")
                .Rows.Add(3334, "Mary", "Smith", "Female")
                .Rows.Add(3335, "George", "Mathews", "undeclared")
            End With
    
            ' just some formatting of DGV
            With DataGridView1
                .DataSource = myTable
                .MultiSelect = False
                .RowHeadersWidth = 24
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.True
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                'For i As Integer = 0 To 4
                '    .Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                'Next
            End With
            DataGridView1.DataSource = view
            Label1.Text = "Rows =" & (DataGridView1.RowCount - 1).ToString
        End Sub
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
            Dim t As String = Trim(TextBox1.Text)
            If t.Length < 1 Then
                view.RowFilter = Nothing
                Label1.Text = "Rows = " & view.Count.ToString
                Exit Sub
            End If
            Dim v As Integer = -1
            Dim intField1 As String = Nothing
                If Integer.TryParse(t, v) Then
                    intField1 = "[Unique ID] = '" & t & "' or "
                End If
                Try
                view.RowFilter = intField1 & "[First name] like '" & t & "' Or [Last name] like '" & t & "'" & " Or Gender like '" & t & "'"
            Catch ex As Exception
                    MessageBox.Show("ERROR: " & ex.Message)
                End Try
            Label1.Text = "Rows = " & view.Count.ToString
        End Sub
    End Class


    Regards Les, Livingston, Scotland

    Monday, October 30, 2017 2:52 PM