Filter a Gridview where columns are derived at run time.

Posted on 2012-08-18
Medium Priority
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
  • 4
  • 3
LVL 26

Expert Comment

by:Alan Warren
ID: 38308851
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)

Author Comment

by:Evan Cutler
ID: 38308858
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...

....is 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
ID: 38308878
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)

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

by:Evan Cutler
ID: 38308883
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
ID: 38308928
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

	SET @startRow = Case @Page when 0 Then 1 Else (@Page * @PerPage) + 1 End

	SET @maxRow = @StartRow + @PerPage

	WITH PagedResults AS
		SELECT top (100) percent
		ROW_NUMBER() OVER(ORDER BY (select 1)) as RowNum
		FROM holddat
	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

Alan Warren earned 2000 total points
ID: 38308978
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



Author Closing Comment

by:Evan Cutler
ID: 38322166
Thanks Alan. :)

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

Thanks again.

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Six Sigma Control Plans

850 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