Filtering data before output

[edolf8]

Hi Everyone,

I am pulling different information from the same file at two different times:
1) The first time, I pull information that is inserted into a combobox. I need to use the combobox as a filter for when I pull the information a second time. 2) I pull different information from the same file, and it is outputted into a datagridview table. I already have a filter in place when the information is pulled the second time, and I was wondering if I could add to that filter or if I have to make another filter?
The filter (or filters) will be used to filter the information in the datagridview before it is outputted in a chart. Let me know if any of this was confusing!

This is the code for binding the combobox to the information

        Dim cnRange As String = "provider=Microsoft.Jet.OLEDB.4.0; " & _
            "data source='" & strFileName & "';" & _
            "Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""
        'moApp.Visible = True

        Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
            MyConnection.Open()

            Dim da As New OleDbDataAdapter( _
            "SELECT DISTINCT * FROM [Sheet1$O5:O]", MyConnection)

            Dim dt As New DataTable
            da.Fill(dt)

            dateComboBox.DisplayMember = dt.Columns(0).ColumnName
            dateComboBox.DataSource = dt
            ediDate = dateComboBox.SelectedItem.ToString()
        End Using

Here is the code for the 2nd information pull where I need the filter?

Private Sub dataButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dataButton.Click
        Dim cnRange As String = "provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source='" & strFileName & "';" & _
    "Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""
        'moApp.Visible = True

        Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
            MyConnection.Open()

            Dim cmd As OleDbCommand = New OleDbCommand( _
            "SELECT * FROM [Sheet1$J4:O]", MyConnection)
            Dim dr As System.Data.IDataReader = cmd.ExecuteReader
            Dim dt As New DataTable
            dt.Load(dr)
            DataGridView1.DataSource = dt

            Me.BindingSource1.DataSource = dt
            Me.BindingSource1.Filter = String.Format("F1 = '76812SNE      
                A010M2'".ToString)
            Chart1.DataSource = BindingSource1

            Chart1.Series("Series1").XValueMember = "F3"
            Chart1.Series("Series1").YValueMembers = "F4"

            Chart1.DataBind()
        End Using
    End Sub

Thanks for your help!

[kareninstructor]

The following returns one column from a sheet into a DataTable followed by populating a ComboBox with distinct items from the column for filtering.

In Button1 click event the current filter is stored in CurrentFilter variable. I simply change the BindingSource filter according to the ComboBox text, is this what you want? Otherwise if you want to append a filter instead of changing the filter you can still do that perhaps as shown in the second code block

    WithEvents BindingSource1 As New BindingSource
    Private NoHeaderConnection As String = _
        "provider=Microsoft.Jet.OLEDB.4.0; data source='{0}';Extended Properties=""Excel 8.0; HDR=No;"""
    Private Sub ReadRangeFruit()
        Dim cnRange As String = String.Format(NoHeaderConnection, "test.xls")

        Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
            MyConnection.Open()
            Dim cmd As OleDbCommand = New OleDbCommand( _
                "SELECT * FROM [Sheet1$J4:J10]", MyConnection)

            Dim dr As System.Data.IDataReader = cmd.ExecuteReader
            Dim dt As New DataTable With {.TableName = "fruit"}
            dt.Load(dr)

            BindingSource1.DataSource = dt
            DataGridView1.DataSource = BindingSource1
            ComboBox1.Items.Add("All")

            Dim Fruit = (From F In dt.Rows.Cast(Of DataRow)() _
                         Select Item = F.Field(Of String)("F1") _
                         Distinct Order By Item).ToArray

            For Each item In Fruit
                ComboBox1.Items.Add(item)
            Next
            ComboBox1.SelectedIndex = 0
        End Using
    End Sub
    Private Sub Button1_Click() Handles Button1.Click
        Dim CurrentFilter As String = BindingSource1.Filter

        Console.WriteLine("Current filter is [{0}]", _
                          If(CurrentFilter = "", "no filter", _
                             BindingSource1.Filter))

        If ComboBox1.Text = "All" Then
            BindingSource1.Filter = ""
        Else
            BindingSource1.Filter = "F1='" & ComboBox1.Text & "'"
        End If

        Console.WriteLine("Current filter is [{0}] after updated", _
                          If(BindingSource1.Filter = "", "no filter", _
                             BindingSource1.Filter))
    End Sub

Private BindingSourceFilter As String = "F1='{0}' AND F2='{1}'"
Private Sub Button2_Click() Handles Button2.Click
    BindingSource1.Filter = String.Format(BindingSourceFilter, _
                                          "F1 value to filter on", _
                                          "F2 value to filter on")
End Sub

[edolf8]

Hi Kevin,

Thanks for the quick reply. I actually already have the combobox and datagridview populated with what I need. I am trying to take the value that is selected from the combobox, and use that as a filter for the datagridview.

Private Sub dataButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dataButton.Click
        Dim cnRange As String = "provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source='" & strFileName & "';" & _
    "Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""
        'moApp.Visible = True

        Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
            MyConnection.Open()

            Dim cmd As OleDbCommand = New OleDbCommand( _
            "SELECT * FROM [Sheet1$J4:O]", MyConnection)
            Dim dr As System.Data.IDataReader = cmd.ExecuteReader
            Dim dt As New DataTable
            dt.Load(dr)
            DataGridView1.DataSource = dt

            Me.BindingSource1.DataSource = dt

            'This filter works to display only rows with "76812SNE A010M2"
            Me.BindingSource1.Filter = String.Format("F1 = '76812SNE      
                A010M2'".ToString)
            'Is it possible to either add on to this filter?  Or do I have to create another filter for the ediDate variable
            'ediDate = dateComboBox.SelectedItem.ToString()

            Chart1.DataSource = BindingSource1

            Chart1.Series("Series1").XValueMember = "F3"
            Chart1.Series("Series1").YValueMembers = "F4"

            Chart1.DataBind()
        End Using
    End Sub

[kareninstructor]

If you look at my code it shows how to change the filter, all you need to do is review the code and implement it.


Code to filter from ComboBox from my original reply minus some fluff

Private Sub Button1_Click() Handles Button1.Click
    If ComboBox1.Text = "All" Then
        BindingSource1.Filter = ""
    Else
        BindingSource1.Filter = "F1='" & ComboBox1.Text & "'"
    End If
End Sub

Also your code to filter

Me.BindingSource1.Filter = String.Format("F1 = '76812SNEA010M2'".ToString)

Does not need the String.Format, this is how it should look

Me.BindingSource1.Filter = "F1 = '76812SNEA010M2'"

If you want to use different values for the filter

   ''' <summary>
   ''' Used to filter BindingSource1
   ''' </summary>
   ''' <param name="ValueForFilter"></param>
   ''' <remarks></remarks>
   Public Sub FilterDemo(ByVal ValueForFilter As String)
      Me.BindingSource1.Filter = String.Format("F1 = '{0}'", _
                                                 ValueForFilter)
   End Sub