How to filter a data grid view in accordance with one or multiple drop down list selections

Question

as the title states I am trying to filter a grid view based on one or multiple selections made among 4 drop down lists. On a button click event I populate the grid view with the broadest set of information. From here I would like to have the grid view update as I make selections in the available drop down lists (4 in total).

While I feel like I could accomplish this through a series of if statements and a modified query for each condition, I feel like there should be a more efficient way this can be done. Below I have posted any pertinent code to this problem I am having.

html/asp code

page load event

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim RSQuery As String = "
        SELECT dDown.OrderCode, dDown.[Description], dDown.[Net Price]
        FROM (
              SELECT SKU.OrderCode AS OrderCode, SKU.[Description] AS [Description],
              (SELECT FORMAT(SKU.ListPrice * EnrollmentType.Multiplier, 'C')) AS [Net Price]
              FROM Enrollment
              INNER JOIN EnrollmentType ON Enrollment.EnrollmentTypeID = EnrollmentType.RecID
              INNER JOIN SKU ON EnrollmentType.TypeID = SKU.EnrollmentTypeID
              INNER JOIN SKUBuild ON SKU.SKU = SKUBuild.SKU
              WHERE (Enrollment.AccountNumber = '12345')
              AND (Enrollment.Active = 'yes')
              AND (SKU.[Description] IS NOT NULL AND NOT (SKU.[Description] = '""'))
        ) AS dDown
        "
    pnlSearchOC.Visible = False
    PopSearchOCDDLs()
    PopGVSearchOC(RSQuery)
End Sub

Procedure to populate the gridview

Protected Sub PopGVSearchOC(ByVal query As String)
    Dim dt As New DataTable
    Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("WarrantyConnectionString").ToString)
        Using cmd As New SqlCommand(query, con)
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader
                If sdr.HasRows Then
                    dt.Load(sdr)
                    With gvReverseSearchOC
                        .DataSource = dt
                        .DataBind()
                        con.Close()
                    End With
                End If
            End Using
        End Using
        If con.State <> ConnectionState.Closed Then
            con.Close()
        End If
    End Using

End Sub

I have one more procedure that populates the drop down lists but I don't believe it is relevant to my problem, I may be wrong.

I am looking for an elegant solution to update/filter my grid view based on the selections made in the drop down lists, any information in this regard would be much appreciated. If I am missing any necessary code I would be happy to supply it but I am pretty sure its all here. Thanks for you time guys.

user_10527 · Answer

Ok, you can do this two ways.

You can pull the data table, and persist the table (but, this can be a bit heavy on browser memory. (but, you could use session() to hold that resulting table - but using session() often means that if the user opens two copies of the page, then things don't work well.

So, probably best to filer against the sql you have. (I would put that sql in a sql server view - we really don't want all sql code in-line, as it hard to maintain.

So, our lodgic:

No selections - show all data. Select 1, or 2 or 3 or 4 combo or filters, then we progressive add this extra conditions. So, blank = ignore, and some value = filter with given values.

So, we kind of need a code stub and approach that works for 1, or 10 filer options. In other words, if we don't find a easy way to "add up" the filters, then we have to write code that considers all combinations. (very bad).

and in this solution, we ALSO very much do NOT want to concatenate values into the sql, since that opens up to not only sql injection, but such code tends to get very messy in a hurry.

So, this approach:

Test the given filter control - if value, then add to our criteria, but STILL use correct parameters - no concatenation of USER input values ever!!!

I don't have your data, but lets just cook up a simple grid, and add a few filters. We will have 2 combo, and say one check box (for only Active Hotels).

So, we have this markup, nothing fancy:

        Filters
        
            
            

            
            
        

        
            
            

            
            
        

        
            
            

            
        

        
            
            

            
        

        
            
        

        
            
        



        
                <%-- this starts new line for grid --%>

So, we just drop in as many "divs", float them left for the filers. You can easy add more filter controls.

So, now our code to load up this stuff, is say this:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadCombos()
        Dim cmdSQL As New SqlCommand("SELECT * FROM tblHotels ORDER BY HotelName")
        LoadGrid(cmdSQL)

    End If

End Sub

Sub LoadCombos()

    Dim cmdSQL As New _
        SqlCommand("SELECT City FROM tblHotels WHERE City is not null GROUP by City")
    cboCity.DataSource = MyrstP(cmdSQL)
    cboCity.DataBind()
    cboCity.Items.Insert(0, New ListItem("All", "All"))

    cmdSQL.CommandText =
        "SELECT Province FROM tblHotels WHERE Province is not null GROUP by Province"
    cboProvince.DataSource = MyrstP(cmdSQL)
    cboProvince.DataBind()
    cboProvince.Items.Insert(0, New ListItem("All", "All"))


End Sub

Sub LoadGrid(cmdSQL As SqlCommand)

    Dim rstData As DataTable = MyrstP(cmdSQL)
    GridView1.DataSource = rstData
    GridView1.DataBind()

End Sub


Public Function MyrstP(sqlCmd As SqlCommand) As DataTable

    Dim rstData As New DataTable

    Using sqlCmd
        sqlCmd.Connection = New SqlConnection(My.Settings.TEST4)
        sqlCmd.Connection.Open()
        rstData.Load(sqlCmd.ExecuteReader)
    End Using

    Return rstData

End Function

Again, quite simple - just some clean code to load up our combo, and the grid.

So, now we have this:

Ok, so now the filter code. As noted, this can work if we have 2 or 20 filters. and you can add more over time - you don't have to change EXISTING code.

So, the code is like this - search button:

Protected Sub cmdSearch_Click(sender As Object, e As EventArgs) Handles cmdSearch.Click

    Dim strSQL As String = "SELECT * FROM tblHotels"
    Dim strOrderBy As String = " ORDER BY HotelName"
    Dim strWhere As String = ""

    Dim cmdSQL As New SqlCommand("")

    If cboCity.Text <> "All" Then
        ' city filter
        strWhere = "(City = @City)"
        cmdSQL.Parameters.Add("@City", SqlDbType.NVarChar).Value = cboCity.Text
    End If

    If cboProvince.Text <> "All" Then
        If strWhere <> "" Then strWhere &= " AND "
        strWhere &= " (Province = @Province)"
        cmdSQL.Parameters.Add("@Province", SqlDbType.NVarChar).Value = cboProvince.Text
    End If

    ' must have description
    If chkDescripiton.Checked Then
        If strWhere <> "" Then strWhere &= " AND "
        strWhere &= " (Description is not null)"
    End If

    If chkActiveOnly.Checked Then
        If strWhere <> "" Then strWhere &= " AND "
        strWhere &= " (Active = 1)"
    End If

    If strWhere <> "" Then
        strSQL &= " WHERE " & strWhere
    End If

    strSQL &= " " & strOrderBy
    cmdSQL.CommandText = strSQL
    LoadGrid(cmdSQL)

End Sub

So, note the "pattern". For each control you add, you have a if/then block. You check existing where clause (might be none), and add " AND " to the where clause. You then add parameters to the sql command object.

MANY fail to realize, that you can add parameters to the command object, and you don't even need to have set what the sql source for the sql command object is!!! - they are separate concepts.

So, with above, we are free to check/select or whatever for the filter, and then hit search button.

the above approach works if you have 2 or 25 optional controls.

And VERY important? We NEVER in ANY case ever concatenate user input into that sql string - only ever use parmaters , so this approach is SQL injection safe.

How to filter a data grid view in accordance with one or multiple drop down list selections

1 Answers