[RESOLVED] filtering a datagridview

[dlscott56]

I have a datagridview that is filled as follows:

Private Sub cmdExecuteSproc(ByVal SO_Number As String, ByVal Prev_SO_Number As String)
        Dim spSteps As New SqlCommand("sproc_shop_order_get_recipe1", cnPMSQL)
        spSteps.Parameters.AddWithValue("@int_shop_order", SO_Number)
        spSteps.Parameters.AddWithValue("@int_prev_so", Prev_SO_Number)
        spSteps.CommandType = CommandType.StoredProcedure

            Dim StepsAdapter As SqlDataAdapter = New SqlDataAdapter(spSteps)
            tblSteps.Clear()
            StepsAdapter.Fill(tblSteps)
    End Sub 'cmdExecuteSproc()

I'd like to have some checkboxes to determine what rows are visible based on criteria set for certain fields. Any suggestions are greatly appreciated.

[jmcilhinney]

If you haven't already, bind your DataTable to a BindingSource and then bind that to your grid. You can then set the Filter property of that BindingSource based on whatever criteria you like. I'd suggest having a single method that builds and sets the Filter, then call that method from all the appropriate event handlers, e.g. CheckedChanged of all your CheckBoxes.

[sushi911]

jm, do you have a codebank for filtering?

[jmcilhinney]

jm, do you have a codebank for filtering?

It's one line of code. Create a string containing the filter and assign it to the BindingSource's Filter property. The filter is basically a SQL WHERE clause but it only supports specific syntax. For details of that syntax you should read the documentation.

[dlscott56]

Thank you. Works great but just want to know if I've followed the documentation correctly.

Private Sub cmdExecuteSproc(ByVal SO_Number As String, ByVal Prev_SO_Number As String)
        Dim spSteps As New SqlCommand("sproc_shop_order_get_recipe1", cnPMSQL)
        spSteps.Parameters.AddWithValue("@int_shop_order", SO_Number)
        spSteps.Parameters.AddWithValue("@int_prev_so", Prev_SO_Number)
        spSteps.CommandType = CommandType.StoredProcedure

            Dim StepsAdapter As SqlDataAdapter = New SqlDataAdapter(spSteps)
            Dim MyBindingSource as As New BindingSource
            tblSteps.Clear()
            StepsAdapter.Fill(tblSteps)
            MyBindingSource.DataSource = tblSteps
            ADataGridView.DataSource = MyBindingSource
            MyBindingSource.Filter = "str_cost_profit_id = 'DS'"
    End Sub 'cmdExecuteSproc()

[jmcilhinney]

That looks OK to me, although it would be more efficient to set the Filter before setting the DataSource of the grid. That way you don't draw the grid with all the data and then have to redraw it after filtering.

Also, I'm assuming that you may at some point want to change the filter and display the data that doesn't match that initial condition. If that's not the case then it would make more sense to include that condition in the original query and retrieve less data in the first place.

[dlscott56]

Thanks. In the actual program I am setting the filter ahead of time and am changing it.

As always, I appreciate the help.