Filter Gridview
Hi,
I am trying to create a simple drop down menu to filter each field. I have started the first field im testing which I am having no luck on. I have provided the front and back code.
I am using MS Access as database file and c# back code. code compiles ok but when selecting the field it does not sort grid view.
Thanks
I am trying to create a simple drop down menu to filter each field. I have started the first field im testing which I am having no luck on. I have provided the front and back code.
I am using MS Access as database file and c# back code. code compiles ok but when selecting the field it does not sort grid view.
Thanks
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="testFilter.aspx.cs" Inherits="testFilter" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/Data/ProCom.mdb" SelectCommand="SELECT [PName], [ICD_IDF], [PType], [CName] FROM [tblProCom]"></asp:AccessDataSource>
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
CellPadding="4" DataSourceID="AccessDataSource1"
ForeColor="#333333" GridLines="None"
AllowPaging="True" OnPageIndexChanged="GridView1_PageIndexChanged">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:BoundField DataField="PName" HeaderText="PName" SortExpression="PName" />
<asp:BoundField DataField="ICD_IDF" HeaderText="ICD_IDF" SortExpression="ICD_IDF" />
<asp:TemplateField HeaderText="ProjectType" SortExpression="PType">
<HeaderTemplate>
Project Type
<asp:DropDownList ID="ddPType"
DataTextField="PType"
AutoPostBack="true"
OnSelectedIndexChanged="FilterDropDown_IndexChanged"
OnPreRender="SetValue"
DataSourceID="AccessDataSource2" runat="server"/>
</HeaderTemplate>
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("PType") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("PType") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="CName" HeaderText="CName" SortExpression="CName" />
</Columns>
<RowStyle BackColor="#EFF3FB" />
<EditRowStyle BackColor="#2461BF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<br />
<br />
<br />
<asp:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="~/Data/ProCom.mdb"
SelectCommand="SELECT DISTINCT PType FROM tblProCom">
</asp:AccessDataSource>
</div>
</form>
</body>
</html>
========================================================================
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class testFilter : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void FilterDropDown_IndexChanged(object sender, EventArgs e)
{
}
protected void SetValue(object sender, EventArgs e)
{
}
protected void GridView1_PageIndexChanged(object sender, EventArgs e)
{
}
}
prairiedog
Where is your AccessDataSource1 DataSource?
ASKER
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/Data/ProCom.md
It is in there
It is in there
My bad.
Are you trying to populate the GridView based on the selection of the dropdownlist?
Are you trying to populate the GridView based on the selection of the dropdownlist?
ASKER
yes
ASKER
So it can filter. I have tried to follow this example but having problems.
http://www.eggheadcafe.com/tutorials/aspnet/c67c4daa-83c2-4baa-aea4-2c8855527acb/aspnet-gridview-filterin.aspx
Regards,
http://www.eggheadcafe.com/tutorials/aspnet/c67c4daa-83c2-4baa-aea4-2c8855527acb/aspnet-gridview-filterin.aspx
Regards,
Are you trying to make that example work with your data or you don't care as long as the GridView gets filtered?
ASKER
as long as the GridView gets filtered
Then let's try a different approach from the example article, a simple one.
1.Select your AccessDataSource1 from the designer, then in the properties window, you should see the SelectQuery property. you need to configure the query to add a parameter to your select statement.
2. In the Command and Parameter Editor, click "Add Parameter" button.
3. Give a name to the parameter, then select Session from the Parameter Source dropdownlist.
4. Give a name to the Session, and you may leave Default Value empty.
5. Click OK to exit.
Now in your DropDownList's SelectedIndexChanged event handler, do this:
1.Select your AccessDataSource1 from the designer, then in the properties window, you should see the SelectQuery property. you need to configure the query to add a parameter to your select statement.
2. In the Command and Parameter Editor, click "Add Parameter" button.
3. Give a name to the parameter, then select Session from the Parameter Source dropdownlist.
4. Give a name to the Session, and you may leave Default Value empty.
5. Click OK to exit.
Now in your DropDownList's SelectedIndexChanged event handler, do this:
protected void GridView1_PageIndexChanged(object sender, EventArgs e)
{
DropDownList dd = (DropDownList)sender;
Session["YourSessionName"] = dd.SelectedValue;
}ASKER
Now nothing is displayed!
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class testFilter : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void FilterDropDown_IndexChanged(object sender, EventArgs e)
{
}
protected void SetValue(object sender, EventArgs e)
{
}
protected void GridView1_PageIndexChanged(object sender, EventArgs e)
{
DropDownList dd = (DropDownList)sender;
Session["sess1"] = dd.SelectedValue;
}
}
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="testFilter.aspx.cs" Inherits="testFilter" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/Data/ProCom.mdb" SelectCommand="SELECT [PName], [ICD_IDF], [PType], [CName] FROM [tblProCom]">
<SelectParameters>
<asp:SessionParameter Name="para1" SessionField="sess1" />
</SelectParameters>
</asp:AccessDataSource>
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
CellPadding="4" DataSourceID="AccessDataSource1"
ForeColor="#333333" GridLines="None"
AllowPaging="True" OnPageIndexChanged="GridView1_PageIndexChanged" AllowSorting="True">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:BoundField DataField="PName" HeaderText="PName" SortExpression="PName" />
<asp:BoundField DataField="ICD_IDF" HeaderText="ICD_IDF" SortExpression="ICD_IDF" />
<asp:TemplateField HeaderText="ProjectType" SortExpression="PType">
<HeaderTemplate>
Project Type
<asp:DropDownList ID="ddPType"
DataTextField="PType"
AutoPostBack="true"
OnSelectedIndexChanged="FilterDropDown_IndexChanged"
OnPreRender="SetValue"
DataSourceID="AccessDataSource2" runat="server"/>
</HeaderTemplate>
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("PType") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("PType") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="CName" HeaderText="CName" SortExpression="CName" />
</Columns>
<RowStyle BackColor="#EFF3FB" />
<EditRowStyle BackColor="#2461BF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<br />
<br />
<br />
<br />
<br />
<asp:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="~/Data/ProCom.mdb"
SelectCommand="SELECT DISTINCT PType FROM tblProCom">
</asp:AccessDataSource>
</div>
</form>
</body>
</html>
My bad again. I am very sorry. The code needs to be placed in the DropDownList's IndexChanged event handler, not GridView's PageIndexChanged. What was wrong with me when I posted the code?
protected void FilterDropDown_IndexChanged(object sender, EventArgs e)
{
DropDownList dd = (DropDownList)sender;
Session["sess1"] = dd.SelectedValue;
}ASKER
still no improvement. still displays nothing.
Since I have added in the parameter, gridview disapears
Since I have added in the parameter, gridview disapears
What happens if you add GridView1.DataBind after you assign value to the Session variable?
ASKER
what you mean???
protected void FilterDropDown_IndexChanged(object sender, EventArgs e)
{
DropDownList dd = (DropDownList)sender;
Session["sess1"] = dd.SelectedValue;
GridView1.DataBind();
}
ASKER
does not do nothing.........
still display nothing (meaning blank screen)
still display nothing (meaning blank screen)
OK. let's go back to the configure query window and delete the parameter and add it again in the followign steps:
1. In Command and Parameter Editor, the top window shows your select command like this: SELECT [PName], [ICD_IDF], [PType], [CName] FROM [tblProCom]
Change it to: SELECT [PName], [ICD_IDF], [PType], [CName] FROM [tblProCom] WHERE ( [PType] = @para1), then click "Refresh Parameters".
2. The parameter para1 will be added automatically for you. Then select Session from the Parameter Source dropdownlist, and set SessionField=sess1
3. Click OK to exit and run the application again.
1. In Command and Parameter Editor, the top window shows your select command like this: SELECT [PName], [ICD_IDF], [PType], [CName] FROM [tblProCom]
Change it to: SELECT [PName], [ICD_IDF], [PType], [CName] FROM [tblProCom] WHERE ( [PType] = @para1), then click "Refresh Parameters".
2. The parameter para1 will be added automatically for you. Then select Session from the Parameter Source dropdownlist, and set SessionField=sess1
3. Click OK to exit and run the application again.
ASKER
blank screen again....... :-(
>>>blank screen again
When page is loaded at the first time?
When page is loaded at the first time?
Somehow I thought the DDL is bound before the GridView, but after re-look at the code I realized I was wrong.
Here is what you may do:
1. Remove the parameter from the select query by changing this block:
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/Data/ProCom.md
      <SelectParameters>
        <asp:SessionParameter Name="para1" SessionField="sess1" />
      </SelectParameters>
    </asp:AccessDataSource>
To this:
 <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/Data/ProCom.md
 </asp:AccessDataSource>
2. Then change DDL's IndexChanged event handler to this:
protected void FilterDropDown_IndexChanged(object sender, EventArgs e)
{
DropDownList dd = (DropDownList)sender;
this.AccessDataSource1.SelectCommand = "SELECT [PName], [ICD_IDF], [PType], [CName] FROM [tblProCom] WHERE [PType]=" + dd.SelectedValue;
GridView1.DataBind();
}ASKER
when selecting on of the values from the drop down list. I get the following error:
IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbExc
Source Error:
Line 21: Â Â Â Â DropDownList dd = (DropDownList)sender;
Line 22: Â Â Â Â this.AccessDataSource1.Sel
Line 23: Â Â Â Â GridView1.DataBind();
Line 24: Â Â }
Line 25:
Â
IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbExc
Source Error:
Line 21: Â Â Â Â DropDownList dd = (DropDownList)sender;
Line 22: Â Â Â Â this.AccessDataSource1.Sel
Line 23: Â Â Â Â GridView1.DataBind();
Line 24: Â Â }
Line 25:
Â
What is the DataValueField of your DDL? I don't see anywhere in your code.
ASKER
Does it have to have a value?
as the data it is loading is Full Service and Sample Only.
I never new a simple job in asp.net to have multiple filters would be so hard!
as the data it is loading is Full Service and Sample Only.
I never new a simple job in asp.net to have multiple filters would be so hard!
>>>Does it have to have a value?
DDL will take DataTextField as its value if you don't have DataValueField set. If PType is a string, then you don't have to, but if it is some integer, then you will need to.
If I assume it is a string type, then change the select command to this:
this.AccessDataSource1.Sel
>>>I never new....
My sincere apology. I misunderstood your code at the first place.
DDL will take DataTextField as its value if you don't have DataValueField set. If PType is a string, then you don't have to, but if it is some integer, then you will need to.
If I assume it is a string type, then change the select command to this:
this.AccessDataSource1.Sel
>>>I never new....
My sincere apology. I misunderstood your code at the first place.
ASKER
Cool! It works. But slight problem.
When the default page is loaded, displays Full Service. When selecting the second value Sample Only it works. But it will never allow me to sort it Full Service as its default value.
Do you understand what I mean?
Thanks for the help. And yes it is a STRING not an integer.
When the default page is loaded, displays Full Service. When selecting the second value Sample Only it works. But it will never allow me to sort it Full Service as its default value.
Do you understand what I mean?
Thanks for the help. And yes it is a STRING not an integer.
ASKER
Also how to reset filter
Great. A big progress.
In the IndexChanged event handler, you can add a check to see if the selected value is Full Service, if yes, then change the Select Command to the original one (without the WHERE statement), otherwise, change the Select Command to the one I provided.
Let me know if you have trouble implementing this.
In the IndexChanged event handler, you can add a check to see if the selected value is Full Service, if yes, then change the Select Command to the original one (without the WHERE statement), otherwise, change the Select Command to the one I provided.
Let me know if you have trouble implementing this.
ASKER
I think you dont understand. In the DDL these are the values:
Full Service
Sample Only
Now on load up, the Full Service is displayed with all data showing in gridview. When selecting Sample Only which is the only option, filters grid view.
But then refreshes page and default value in DDL is Full Service and you can never select this value as it does not register it.
The answer to this IF POSSIBLE to have a label above all values such as '-- Please select --'
Understand?
Thanks,
Full Service
Sample Only
Now on load up, the Full Service is displayed with all data showing in gridview. When selecting Sample Only which is the only option, filters grid view.
But then refreshes page and default value in DDL is Full Service and you can never select this value as it does not register it.
The answer to this IF POSSIBLE to have a label above all values such as '-- Please select --'
Understand?
Thanks,
1. Select your DDL from the designer, then set AppendDataBoundItem=True. Then manulally add an item to the DDL with Text="--Please select--" and Value=""
2. Add the check as I mentioned before in the IndexChanged event handler.
2. Add the check as I mentioned before in the IndexChanged event handler.
Wait a second. Missed something.
Sorry for the delay. Had to pick up my son from his summer camp because he is running fever.
OK, just noticed that you have not implemented "SetValue" function yet. You defined in your DDL OnPreRender="SetValue", but this handler is not implemented anywhere. So let us add this block in your code behind:
protected void SetValue(object sender, EventArgs e)
  {
    DropDownList ddl = (DropDownList)sender;
    ddl.SelectedValue = ViewState["SelectedValue"]
  }
Then follow the instruction I gave in ID# 22162788.
Finaly, add this line of code in the DDL's IndexChanged event handler:
OK, just noticed that you have not implemented "SetValue" function yet. You defined in your DDL OnPreRender="SetValue", but this handler is not implemented anywhere. So let us add this block in your code behind:
protected void SetValue(object sender, EventArgs e)
  {
    DropDownList ddl = (DropDownList)sender;
    ddl.SelectedValue = ViewState["SelectedValue"]
  }
Then follow the instruction I gave in ID# 22162788.
Finaly, add this line of code in the DDL's IndexChanged event handler:
protected void FilterDropDown_IndexChanged(object sender, EventArgs e)
{
DropDownList dd = (DropDownList)sender;
ViewState["SelectedValue"] = dd.SelectedValue; //This line added
//the rest of the code
//...
//...
}
ASKER
Hi, I am now getting the following error:
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceExcept
Source Error:
Line 28: Â Â {
Line 29: Â Â Â Â DropDownList dd = (DropDownList)sender;
Line 30: Â Â Â Â dd.SelectedValue = ViewState["SelectedValue"]
Line 31: Â Â }
Line 32:
Â
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceExcept
Source Error:
Line 28: Â Â {
Line 29: Â Â Â Â DropDownList dd = (DropDownList)sender;
Line 30: Â Â Â Â dd.SelectedValue = ViewState["SelectedValue"]
Line 31: Â Â }
Line 32:
Â
ASKER
I will also point out, idealy after we have this one working 100%. I would like to have multiple filters.
So for instance I will have PName with a list, and PType. When selecting each one, would like it to filter after filter.
And then when finished, would like to reset the filters back to default.
So for instance I will have PName with a list, and PType. When selecting each one, would like it to filter after filter.
And then when finished, would like to reset the filters back to default.
>>>Exception Details: System.NullReferenceExcept
Did you add this line to the DDL's IndexChanged event handler?
ViewState["SelectedValue"]
Â
Did you add this line to the DDL's IndexChanged event handler?
ViewState["SelectedValue"]
Â
ASKER
Yeah I have
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class testFilter : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void FilterDropDown_IndexChanged(object sender, EventArgs e)
{
DropDownList dd = (DropDownList)sender;
ViewState["SelectedValue"] = dd.SelectedValue;
this.AccessDataSource1.SelectCommand = "SELECT [PName], [ICD_IDF], [PType], [CName] FROM [tblProCom] WHERE [PName] <> 'select' AND [PType]='" + dd.SelectedValue + "'";
GridView1.DataBind();
}
protected void SetValue(object sender, EventArgs e)
{
DropDownList ddl = (DropDownList)sender;
ddl.SelectedValue = ViewState["SelectedValue"].ToString();
}
protected void GridView1_PageIndexChanged(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
Response.Redirect("testFilter.aspx");
}
}
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Right that works. So I will be awarding you the points for your hard work.
I have the next step in this question:
https://www.experts-exchange.com/questions/23625787/Sample-For-GridView-with-Multiple-Filters-ASP-NET-C.html
Thanks!
I have the next step in this question:
https://www.experts-exchange.com/questions/23625787/Sample-For-GridView-with-Multiple-Filters-ASP-NET-C.html
Thanks!
ASKER
Thanks