The first of the 2 questions has been resolved however the second was not! My fault I shouldn’t of asked both in the same post.
Using the two DropDownLists and the code provided I would like to filter the data in my GridView inversed datatable (PivotTable) by the
Month and Year selected. If possible selecting the year first and then displaying the data on the month selection!
Public Sub Populate1()
Dim cmd As New SqlCommand("SELECT DISTINCT DATENAME(MONTH,[t_date]) t_date FROM [myTbl]", New SqlConnection(ConfigurationManager.ConnectionStrings("ConString").ConnectionString))
cmd.Connection.Open()
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)
ddlMonth.DataSource = ds
ddlMonth.DataValueField = "t_date"
ddlMonth.DataTextField = "t_date"
ddlMonth.DataBind()
ddlMonth.Items.Insert(0, New ListItem("--Select--", "0"))
cmd.Connection.Close()
cmd.Connection.Dispose()
End Sub
Public Sub Populate2()
Dim cmd As New SqlCommand("SELECT DISTINCT DATEPART(YEAR,[t_date]) t_date FROM [myTbl]", New SqlConnection(ConfigurationManager.ConnectionStrings("ConString").ConnectionString))
cmd.Connection.Open()
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)
ddlYear.DataSource = ds
ddlYear.DataValueField = "t_date"
ddlYear.DataTextField = "t_date"
ddlYear.DataBind()
ddlYear.Items.Insert(0, New ListItem("--Select--", "0"))
cmd.Connection.Close()
cmd.Connection.Dispose()
End Sub
Public Sub BindWatchGrid()
Dim conn As SqlConnection = Nothing
Try
Dim connString As String = "Data Source=;Initial Catalog=;Integrated Security=True"
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConString").ConnectionString)
Dim query As String = "SELECT * FROM userProfilesTbl LEFT JOIN storeTbl ON userProfilesTbl.st_id = storeTbl.st_id LEFT JOIN shiftTbl ON userProfilesTbl.wa_id = shiftTbl.wa_id LEFT JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId LEFT JOIN myTbl ON userAssessmentTbl.tt_id = myTbl.tt_id LEFT JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id WHERE userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id ORDER BY surname ASC"
'AND t_date=@t_date
Dim cmd As SqlCommand = New SqlCommand(query, conn)
conn.Open()
cmd.Parameters.AddWithValue("@st_id", myLblStore.Value().ToString)
cmd.Parameters.AddWithValue("@wa_id", myLblShift.Value().ToString)
'cmd.Parameters.AddWithValue("@t_date", ViewState("t_date").ToString())
Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Dim dt As DataTable = New DataTable()
Dim dt2 As DataTable = New DataTable()
dt.Load(dr)
dt2 = GetInversedDataTable(dt, "surname", "setName", "o_id", "", True)
GridView1.DataSource = dt2
GridView1.DataBind()
Catch ex As SqlException
' handle error
Catch ex As Exception
' handle error
Finally
conn.Close()
End Try
End Sub
Well, let's try to solve your porblem, plase be kind to post the feedback.
Basically, I am using one of my readymade application demo application to demonstrate the functionality you requested - And I have had done it mainly in the aspx code file, (Click
here to get the original version).
I tried and it seems to be working seemless, plus ... you do not have need to add the extra table and to do that funky things.
The code above looks quite adaptive, you can use the fiter expression in the markup too and by doing that you do not need to write even a single line of code in the code behind
Or
vice - versa.
I tried to translate the C# code to VB.net :
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Namespace WebApplication2
Public Partial Class _Default
Inherits System.Web.UI.Page
Public Property index() As Integer
Get
Return m_index
End Get
Set
m_index = Value
End Set
End Property
Private m_index As Integer
Protected Sub Page_Load(sender As Object, e As EventArgs)
End Sub
Protected Sub SelectUnselectRow(sender As Object, e As EventArgs)
End Sub
Protected Sub GridView_SelectedIndexChanged(sender As Object, e As EventArgs)
End Sub
Protected Sub ddFilter_SelectedIndexChanged(sender As Object, e As EventArgs)
'DropDownList filter = new DropDownList();
'filter = (DropDownList)MyGridView.FindControl("ddFilter");
'if (filter != null)
'{
' SQLDataSource1.FilterParameters.Add("Country", filter.SelectedItem.Text);
' SQLDataSource1.FilterExpression = "Country='{0}'";
' ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", "alert('" + filter.SelectedItem.Text + "');", true);
'}
Dim filter As DropDownList = DirectCast(sender, DropDownList)
index = DirectCast(sender, DropDownList).SelectedIndex
SQLDataSource.FilterParameters.Clear()
SQLDataSource.FilterExpression = "Country='{0}'"
SQLDataSource.FilterParameters.Add("Country", filter.SelectedItem.Text)
'ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", "alert('" + filter.SelectedItem.Text + "');", true);
If String.Equals(filter.SelectedItem.Text, "All") Then
MyGridView.DataSourceID = MainSQLDataSource.ID
MyGridView.DataBind()
Else
MyGridView.DataSourceID = SQLDataSource.ID
MyGridView.DataBind()
End If
End Sub
End Class
End Namespace
Ive got no probl;em with how to filter. The problem is more to do with how to filter just the date (integer 1 to 12) from the date field of the drop down?
Do i need to use sonme ort of trim function?
here's where I'm upto!
Public Sub BindWatchGrid()
Dim conn As SqlConnection = Nothing
Try
Dim connString As String = "Data Source=;Initial Catalog=;Integrated Security=True"
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConString").ConnectionString)
Dim query As String = "SELECT * FROM userProfilesTbl LEFT JOIN storeTbl ON userProfilesTbl.st_id = storeTbl.st_id LEFT JOIN shiftTbl ON userProfilesTbl.wa_id = shiftTbl.wa_id LEFT JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId LEFT JOIN myTbl ON userAssessmentTbl.tt_id = myTbl.tt_id LEFT JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id WHERE userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id AND trainingTbl.t_date = @t_date ORDER BY surname ASC"
Dim cmd As SqlCommand = New SqlCommand(query, conn)
conn.Open()
cmd.Parameters.AddWithValue("@st_id", myLblStore.Value().ToString)
cmd.Parameters.AddWithValue("@wa_id", myLblShift.Value().ToString)
cmd.Parameters.AddWithValue("@t_date", ddlMonth.SelectedValue().ToString())
Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Dim dt As DataTable = New DataTable()
Dim dt2 As DataTable = New DataTable()
dt.Load(dr)
dt2 = GetInversedDataTable(dt, "surname", "setName", "o_id", "", True)
GridView1.DataSource = dt2
GridView1.DataBind()
Catch ex As SqlException
' handle error
Catch ex As Exception
' handle error
Finally
conn.Close()
End Try
I-Weedy
Member
67 Points
239 Posts
Filter Datatable GridView with DropDownList
Nov 02, 2012 02:43 PM|LINK
Early last week I asked 2 questions in one forum post regarding the scenario which can be found here: http://forums.asp.net/t/1854237.aspx/1?Filter+GridView+with+DropDownLists
The first of the 2 questions has been resolved however the second was not! My fault I shouldn’t of asked both in the same post.
Using the two DropDownLists and the code provided I would like to filter the data in my GridView inversed datatable (PivotTable) by the Month and Year selected. If possible selecting the year first and then displaying the data on the month selection!
<div> <table><tr><th>Month:</th><td><asp:DropDownList ID="ddlMonth" runat="server" AutoPostBack="true" AppendDataBoundItems="true" OnSelectedIndexChanged = "MonthChanged_SelectedIndexChanged" > </asp:DropDownList></td></tr> <tr><th>Year:</th><td><asp:DropDownList ID="ddlYear" runat="server" AutoPostBack="true" AppendDataBoundItems="true" OnSelectedIndexChanged = "YearChanged_SelectedIndexChanged" > </asp:DropDownList></td></tr></table> </div> <asp:GridView ID="GridView1" runat="server" CssClass="rounded-corner myGridView1" > <Columns> </Columns> </asp:GridView>Public Sub Populate1() Dim cmd As New SqlCommand("SELECT DISTINCT DATENAME(MONTH,[t_date]) t_date FROM [myTbl]", New SqlConnection(ConfigurationManager.ConnectionStrings("ConString").ConnectionString)) cmd.Connection.Open() Dim da As New SqlDataAdapter(cmd) Dim ds As New DataSet() da.Fill(ds) ddlMonth.DataSource = ds ddlMonth.DataValueField = "t_date" ddlMonth.DataTextField = "t_date" ddlMonth.DataBind() ddlMonth.Items.Insert(0, New ListItem("--Select--", "0")) cmd.Connection.Close() cmd.Connection.Dispose() End Sub Public Sub Populate2() Dim cmd As New SqlCommand("SELECT DISTINCT DATEPART(YEAR,[t_date]) t_date FROM [myTbl]", New SqlConnection(ConfigurationManager.ConnectionStrings("ConString").ConnectionString)) cmd.Connection.Open() Dim da As New SqlDataAdapter(cmd) Dim ds As New DataSet() da.Fill(ds) ddlYear.DataSource = ds ddlYear.DataValueField = "t_date" ddlYear.DataTextField = "t_date" ddlYear.DataBind() ddlYear.Items.Insert(0, New ListItem("--Select--", "0")) cmd.Connection.Close() cmd.Connection.Dispose() End Sub Public Sub BindWatchGrid() Dim conn As SqlConnection = Nothing Try Dim connString As String = "Data Source=;Initial Catalog=;Integrated Security=True" conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConString").ConnectionString) Dim query As String = "SELECT * FROM userProfilesTbl LEFT JOIN storeTbl ON userProfilesTbl.st_id = storeTbl.st_id LEFT JOIN shiftTbl ON userProfilesTbl.wa_id = shiftTbl.wa_id LEFT JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId LEFT JOIN myTbl ON userAssessmentTbl.tt_id = myTbl.tt_id LEFT JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id WHERE userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id ORDER BY surname ASC" 'AND t_date=@t_date Dim cmd As SqlCommand = New SqlCommand(query, conn) conn.Open() cmd.Parameters.AddWithValue("@st_id", myLblStore.Value().ToString) cmd.Parameters.AddWithValue("@wa_id", myLblShift.Value().ToString) 'cmd.Parameters.AddWithValue("@t_date", ViewState("t_date").ToString()) Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) Dim dt As DataTable = New DataTable() Dim dt2 As DataTable = New DataTable() dt.Load(dr) dt2 = GetInversedDataTable(dt, "surname", "setName", "o_id", "", True) GridView1.DataSource = dt2 GridView1.DataBind() Catch ex As SqlException ' handle error Catch ex As Exception ' handle error Finally conn.Close() End Try End SubDataTable gridview DROPDOWNList filter
aarsh
Participant
1543 Points
427 Posts
Re: Filter Datatable GridView with DropDownList
Nov 02, 2012 07:17 PM|LINK
Well, let's try to solve your porblem, plase be kind to post the feedback.
Basically, I am using one of my readymade application demo application to demonstrate the functionality you requested - And I have had done it mainly in the aspx code file, (Click here to get the original version).
You may want to look at this : http://forums.asp.net/post/5199630.aspx (Or Download)
I tried and it seems to be working seemless, plus ... you do not have need to add the extra table and to do that funky things.
The code above looks quite adaptive, you can use the fiter expression in the markup too and by doing that you do not need to write even a single line of code in the code behind
Or
vice - versa.
I tried to translate the C# code to VB.net :
Imports System.Collections.Generic Imports System.Linq Imports System.Web Imports System.Web.UI Imports System.Web.UI.WebControls Namespace WebApplication2 Public Partial Class _Default Inherits System.Web.UI.Page Public Property index() As Integer Get Return m_index End Get Set m_index = Value End Set End Property Private m_index As Integer Protected Sub Page_Load(sender As Object, e As EventArgs) End Sub Protected Sub SelectUnselectRow(sender As Object, e As EventArgs) End Sub Protected Sub GridView_SelectedIndexChanged(sender As Object, e As EventArgs) End Sub Protected Sub ddFilter_SelectedIndexChanged(sender As Object, e As EventArgs) 'DropDownList filter = new DropDownList(); 'filter = (DropDownList)MyGridView.FindControl("ddFilter"); 'if (filter != null) '{ ' SQLDataSource1.FilterParameters.Add("Country", filter.SelectedItem.Text); ' SQLDataSource1.FilterExpression = "Country='{0}'"; ' ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", "alert('" + filter.SelectedItem.Text + "');", true); '} Dim filter As DropDownList = DirectCast(sender, DropDownList) index = DirectCast(sender, DropDownList).SelectedIndex SQLDataSource.FilterParameters.Clear() SQLDataSource.FilterExpression = "Country='{0}'" SQLDataSource.FilterParameters.Add("Country", filter.SelectedItem.Text) 'ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alert", "alert('" + filter.SelectedItem.Text + "');", true); If String.Equals(filter.SelectedItem.Text, "All") Then MyGridView.DataSourceID = MainSQLDataSource.ID MyGridView.DataBind() Else MyGridView.DataSourceID = SQLDataSource.ID MyGridView.DataBind() End If End Sub End Class End NamespaceThanks,
-Aarsh
---
Please mark it as answer if it helps.
filterparameters filterexpression dropdown gridview filter
RameshRajend...
Star
7983 Points
2099 Posts
Re: Filter Datatable GridView with DropDownList
Nov 02, 2012 07:30 PM|LINK
Simple just ref and try this
http://www.aspdotnet-suresh.com/2011/11/how-to-filter-gridview-records-with.html
Thank u
aarsh
Participant
1543 Points
427 Posts
Re: Filter Datatable GridView with DropDownList
Nov 02, 2012 07:35 PM|LINK
^ So are they using table instead gridview header ?
I-Weedy
Member
67 Points
239 Posts
Re: Filter Datatable GridView with DropDownList
Nov 05, 2012 01:19 PM|LINK
Yes I am ujsing tables instead of gridView header
aarsh
Participant
1543 Points
427 Posts
Re: Filter Datatable GridView with DropDownList
Nov 07, 2012 04:31 AM|LINK
Yeah that is also a possible solution now it depends on the way, the original questioner wants.
I-Weedy
Member
67 Points
239 Posts
Re: Filter Datatable GridView with DropDownList
Nov 08, 2012 08:53 AM|LINK
Sorry I'm not understanding what you mean? I am the original questioner!
aarsh
Participant
1543 Points
427 Posts
Re: Filter Datatable GridView with DropDownList
Nov 09, 2012 02:28 PM|LINK
Did you tried first reply I to your question ( Link ) ? Does not it work for you ?
If it does and solves your problem, please mark the post as an answer.
Download
You may download the working project.
Screen shots:
DefaultView-WhenApplicationLoads
I Selected Beligium
Changed My Selection To Poland And The GridView Automatically Got Updated
If I want to go back to the default View I select All from the list
aarsh
Participant
1543 Points
427 Posts
Re: Filter Datatable GridView with DropDownList
Nov 11, 2012 11:22 PM|LINK
Any luck
?If it does and solves your problem, please mark the post as an answer.
I-Weedy
Member
67 Points
239 Posts
Re: Filter Datatable GridView with DropDownList
Nov 14, 2012 03:58 PM|LINK
Hi aarsh, sorry i never answered sooner!
Ive got no probl;em with how to filter. The problem is more to do with how to filter just the date (integer 1 to 12) from the date field of the drop down?
Do i need to use sonme ort of trim function?
here's where I'm upto!
Public Sub BindWatchGrid() Dim conn As SqlConnection = Nothing Try Dim connString As String = "Data Source=;Initial Catalog=;Integrated Security=True" conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConString").ConnectionString) Dim query As String = "SELECT * FROM userProfilesTbl LEFT JOIN storeTbl ON userProfilesTbl.st_id = storeTbl.st_id LEFT JOIN shiftTbl ON userProfilesTbl.wa_id = shiftTbl.wa_id LEFT JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId LEFT JOIN myTbl ON userAssessmentTbl.tt_id = myTbl.tt_id LEFT JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id WHERE userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id AND trainingTbl.t_date = @t_date ORDER BY surname ASC" Dim cmd As SqlCommand = New SqlCommand(query, conn) conn.Open() cmd.Parameters.AddWithValue("@st_id", myLblStore.Value().ToString) cmd.Parameters.AddWithValue("@wa_id", myLblShift.Value().ToString) cmd.Parameters.AddWithValue("@t_date", ddlMonth.SelectedValue().ToString()) Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) Dim dt As DataTable = New DataTable() Dim dt2 As DataTable = New DataTable() dt.Load(dr) dt2 = GetInversedDataTable(dt, "surname", "setName", "o_id", "", True) GridView1.DataSource = dt2 GridView1.DataBind() Catch ex As SqlException ' handle error Catch ex As Exception ' handle error Finally conn.Close() End Try