[RESOLVED] How to filter 2 columns

[wiadus]

Hi guys could anyone help me with how I can filter BindingSource base on 2 columns. Example data starting with "s" in column A & column B must be filtered. Thanks

[jmcilhinney]

Why do people refuse to read the documentation? This is from the documentation for BindingSource.Filter property:

Private Sub PopulateDataViewAndFilter() 
    Dim set1 As New DataSet()

    ' Some xml data to populate the DataSet with.
    Dim musicXml As String = "<?xml version='1.0' encoding='UTF-8'?>" & _
        "<music>" & _
        "<recording><artist>Coldplay</artist><cd>X&amp;Y</cd></recording>" & _
        "<recording><artist>Dave Matthews</artist><cd>Under the Table and Dreaming</cd></recording>" & _
        "<recording><artist>Dave Matthews</artist><cd>Live at Red Rocks</cd></recording>" & _
        "<recording><artist>Natalie Merchant</artist><cd>Tigerlily</cd></recording>" & _
        "<recording><artist>U2</artist><cd>How to Dismantle an Atomic Bomb</cd></recording>" & _
        "</music>"

    ' Read the xml.
    Dim reader As New StringReader(musicXml)
    set1.ReadXml(reader)

    ' Get a DataView of the table contained in the dataset.
    Dim tables As DataTableCollection = set1.Tables
    Dim view1 As New DataView(tables(0))

    ' Create a DataGridView control and add it to the form.
    Dim datagridview1 As New DataGridView()
    datagridview1.AutoGenerateColumns = True
    Me.Controls.Add(datagridview1)

    ' Create a BindingSource and set its DataSource property to
    ' the DataView.
    Dim source1 As New BindingSource()
    source1.DataSource = view1

    ' Set the data source for the DataGridView.
    datagridview1.DataSource = source1

    ' The Filter string can include Boolean expressions.
    source1.Filter = "artist = 'Dave Matthews' OR cd = 'Tigerlily'"

End Sub

It should be clear that, if you can use OR, you can use AND. If it's not clear, that same documentation also says this:

To form a filter value, specify the name of a column followed by an operator and a value to filter on. The accepted filter syntax depends on the underlying data source. If the underlying data source is a DataSet, DataTable, or DataView, you can specify Boolean expressions using the syntax documented for the DataColumn.Expression property.

If you follow the link as instructed then you arrive at a topic that includes this:

Concatenation is allowed using Boolean AND, OR, and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:

(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

So, in conclusion, which would you find preferable: read the documentation and answer your own question in a matter of minutes, if not seconds, or post on a forum and wait 2 1/2 hours or more for someone else? Forums are great for the questions that are to complicated to answer for yourself or you can't find the answer to, but they shouldn't be required for those questions that you can answer with 30 seconds of reading the documentation for the one and only type or member involved.

[wiadus]

I tried to sort BindingSource where the selected item is within two columns but when I use the code below I get error, any help?

 dtView.Filter = "FirstActor = '" + CStr(lstMovieList.SelectedItem) + "'" Or dtView.Filter = "SecondActor = '" + CStr(lstMovieList.SelectedItem) + "'"

The error is " 'Conversion from string "FirstActor = 'Chuck Norris'" to type 'Boolean' is not valid." Chuck Norris was the selected item.
Thanks

[billboy]

try

dtView.Filter = "FirstActor ' = " & 1stMovieList.SelectedText & " Or "SecondActor ' = " & 1stMovieList.SelectedText & " "

Havent tested it

[jmcilhinney]

This would be the "proper" way:

dtView.Filter = String.Format("FirstActor = '{0}' OR SecondActor = '{0}'", lstMovieList.SelectedItem)

[wiadus]

thanks all