Filter a Gridview where columns are derived at run time.

Posted on 2012-08-18
Last Modified: 2012-08-22
Greetings, I have a GridView (See below):

 SQL filtering screen
I do not know what the columns will be prior to run time.
They are derived after a dynamic SQL table creation and insert execution.

How would I go about adding a filtering capability to this.
Ie. a text block to assign a value to a column, then the recordsource requeries to get the right values based on finding data in the assigned column using the value from the textblock?

Thank you.
Question by:Evan Cutler
    LVL 26

    Expert Comment

    by:Alan Warren
    can we take a peek at your dynamic SQL, I expect you need to add a whereclause to the SQL, which you would populate from a filter input control.
    strSQL = strSQL + " where [somefield]='" & textbox1.text & "'"

    Open in new window

    Alan ";0)
    LVL 9

    Author Comment

    by:Evan Cutler
    ok.  Here's the scenerio.

    I have an XML File, that dictates columns in a given row based record:

    <?xml version= "1.0"?>

    I do have that prior to execution,
    I guess I need to dynamically add drop downs, and textboxes.
    kinda like one where you see a single drop down, if selected = NOT NULL, and the textbox is filled with a filter, then a second drop down and textbox would appear, if the second filter is filled out, a third would appear, and so on... that possible?

    The initial SQL is:

                string query = "SELECT TOP " + PerPage + " * FROM (SELECT ";
                query += "row_number() OVER (ORDER BY (select 1)) AS num, * ";
                query += "FROM holddat) AS a WHERE num > " + ((Page * PerPage) - PerPage) + ";";

    and is called by:

                GVData.DataSource = GetTable(DSN, Convert.ToInt32(TXTPage.Text), Convert.ToInt32(TXTPerPage.Text));

    allows for paging.


    LVL 26

    Expert Comment

    by:Alan Warren
    Hi arcee123,
    not sure you can filter the SQL when using the "*" wildcard, think you need to select the fieldnames by name in order to apply whereclause as you have done with the [num] field.

    Basically you would extend your where:
    dim strFilter1 as string = ""
    strFilter1 = Me.somecontrol.text '(or selectedvalue) if the control is a dropdown
    query += "FROM holddat) AS a WHERE [somefield]='" & strFilter1 & "' AND  num > " + ((Page * PerPage) - PerPage) + ";";

    Open in new window

    It's all possible...
    Alan ";0)
    LVL 9

    Author Comment

    by:Evan Cutler
    I appreciate the SQL side of the house, but my concern is getting the controls on the formview.  How do I dynamicaly add dropdowns and textbox values when I want them?
    LVL 26

    Expert Comment

    by:Alan Warren
    Hi arcee123,

    Oh, thought it was an SQL issue, was just working on Stored proceedure to return the paged rowsets for you.
    CREATE PROCEDURE [dbo].[holddat_Sel]
    	 @Page int = 0
    	,@PerPage int = 10
    	DECLARE @StartRow INT
    	SET @startRow = Case @Page when 0 Then 1 Else (@Page * @PerPage) + 1 End
    	DECLARE @MaxRow INT
    	SET @maxRow = @StartRow + @PerPage
    	WITH PagedResults AS
    		SELECT top (100) percent
    		ROW_NUMBER() OVER(ORDER BY (select 1)) as RowNum
    		FROM holddat
    	SELECT	*
    	FROM PagedResults 
    	WHERE (RowNum >= @StartRow) and (RowNum < @maxRow)
    -- Testing
    -- exec holddat_Sel -- return first ten rows
    -- exec holddat_Sel 1 -- return rows 11 - 20
    -- exec holddat_Sel 1,50 -- return rows 51 - 100

    Open in new window

    Is the the snapshot of your gridview a snapshot of a "Dynamically Created" gridview?
    If not, then you can modify the GridView easily, adding the controls you need to extend the filter.

    If the GridView is "Dynamically Created" we could possibly add filtering controls to GridView dynamically, but they would be much more accessible outside of the GridView, in which case you can still add them to the page by dragging them from the toolbox.

    Apologies for being pedantic, but why write code when we don't need to write code, creating objects dynamically should be the exception rather than the rule.
    My 0.02c worth

    Alan ";0)
    LVL 26

    Accepted Solution

    Hi arcee123,
    thanks for the question, enjoyed it...

    Found some code to dynamically create a dropdownlist:

    Hooked it up to loop through your gridview columns, adding the column.headertext to the dropdownlist, the dropdownlist has a selectedindex_changed event which you can use to re-filter your dynamic SQL by the previously unknown column names.
    Partial Class Default6
      Inherits System.Web.UI.Page
      Private Sub Dynamic_Method(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim ComboBox As DropDownList = CType(sender, DropDownList)
        Dim sSTR As String = ""
        sSTR = "</br>Selected Index: " + ComboBox.SelectedIndex.ToString()
        sSTR += "</br>Selected Item: " + ComboBox.SelectedItem.Text.ToString()
        sSTR += "</br>Selected Value: " + ComboBox.SelectedItem.Value.ToString()
        Dim lbl As Label = New Label()
        lbl.Text = sSTR
        ' Your gridview.databind/sql concatenation here I expect
      End Sub
      Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        On Error GoTo ReportError
        Dim strErrMsg As String = ""
        Dim ComboBox As DropDownList = New DropDownList()
        ComboBox.ID = "ComboBox"
        ComboBox.AutoPostBack = True
        Dim n As Int32 = 0
        For Each col As DataControlField In GVData.Columns
          ComboBox.Items.Add(New ListItem(col.HeaderText, n.ToString))
          n = n + 1
        AddHandler ComboBox.SelectedIndexChanged, AddressOf Dynamic_Method
        On Error Resume Next
        Exit Sub
        strErrMsg = "Error in ~/default6.aspx.vb_Page_Load()" _
          & vbCr & "Error number " & CStr(Err.Number) _
          & " was generated by " & Err.Source _
          & vbCr & vbCr & Err.Description
        'Me.lblErrorMessage.Text = strErrMsg
        'Me.lblErrorMessage.Visible = True
        Resume ExitProcedure
      End Sub
    End Class

    Open in new window


    LVL 9

    Author Closing Comment

    by:Evan Cutler
    Thanks Alan. :)

    That got me where I needed to go.
    You have no idea how many directions a filter can go.
    Talk about a

    Thanks again.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    This is an explanation of a simple data model to help parse a JSON feed
    A short article about problems I had with the new location API and permissions in Marshmallow
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now