How To Show All Items Using a DropDownList and a GridView Control
(ASP.NET 2.0)
We often want to have a gridview which selects items based on a dropdownlist. It’s trivial to do this in the VS2005 designer by simply first creating an SqlDataSource with a where clause, then with the sqldatasource wizard, assign the SelectedValue of the dropdownlist to the GridView.
The application running looks like this:
And the page source to do is simply this:
<%@ Page Language=”C#” %>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<script runat=”server”>
</script>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head runat=”server”>
<title>Simple GridView With DropDownList Selection</title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<asp:DropDownList ID=”DropDownList1″ runat=”server” AutoPostBack=”True”>
<asp:ListItem>Ikura</asp:ListItem>
<asp:ListItem>Konbu</asp:ListItem>
</asp:DropDownList><br />
<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:NorthWindConnectionString %>“
SelectCommand=”SELECT [ProductName], [UnitsInStock], [UnitsOnOrder] FROM [Alphabetical List of Products] WHERE ([ProductName] = @ProductName2)”>
<SelectParameters>
<asp:ControlParameter ControlID=”DropDownList1″ Name=”ProductName2″ PropertyName=”SelectedValue”
Type=”String” />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False” DataSourceID=”SqlDataSource1″>
<Columns>
<asp:BoundField DataField=”ProductName” HeaderText=”ProductName” SortExpression=”ProductName” />
<asp:BoundField DataField=”UnitsInStock” HeaderText=”UnitsInStock” SortExpression=”UnitsInStock” />
<asp:BoundField DataField=”UnitsOnOrder” HeaderText=”UnitsOnOrder” SortExpression=”UnitsOnOrder” />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
However, what if you want to have a show all option in your DropDownList? My suggestion is to abandon (somewhat) the visual programming model (or at least that’s the only way I can think of doing it) and add the SelectParameters in the Page_Load event of the page. In addition, to get all the rows to show, I suggest adding the Sql Injection trick which is add an OR to the sql. You can that make the OR always return true, or always return false. I do this by simply passing a 1=@CompareInteger to the Sql that always gets evaluated. Then dynamically set the CompareInteger to 1 or 999. 1 makes all the rows return, 999 forces the evaluation of the ProductName=@ProductName part of the Where clause.
Here is what the application looks like running:
And here is the code that actually does the job.
<%@ Page Language=”C#” %>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<script runat=”server”>
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ControlParameter cp =
new ControlParameter(“ProductName”,TypeCode.String,“DropDownList1″,“SelectedValue”);
SqlDataSource1.SelectParameters.Add(cp);
Parameter p = new Parameter(“CompareInteger”, TypeCode.Int32);
p.DefaultValue = “1″;
SqlDataSource1.SelectParameters.Add(p);
}
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
if (DropDownList1.SelectedValue.Equals(“(All)”))
{
SqlDataSource1.SelectParameters[1].DefaultValue = “1″;
}
else
{
SqlDataSource1.SelectParameters[1].DefaultValue = “999″; // not one
}
}
</script>
<html xmlns=”http://www.w3.org/1999/xhtml” >
<head runat=”server”>
<title>DropDownList and GridView with Show All Option</title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<asp:DropDownList ID=”DropDownList1″ runat=”server” AutoPostBack=”True” OnSelectedIndexChanged=”DropDownList1_SelectedIndexChanged”>
<asp:ListItem Value=”(All)”></asp:ListItem>
<asp:ListItem>Ikura</asp:ListItem>
<asp:ListItem>Konbu</asp:ListItem>
</asp:DropDownList><br />
<br />
<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False” DataSourceID=”SqlDataSource1″>
<Columns>
<asp:BoundField DataField=”ProductName” HeaderText=”ProductName” SortExpression=”ProductName” />
<asp:BoundField DataField=”UnitsInStock” HeaderText=”UnitsInStock” SortExpression=”UnitsInStock” />
</Columns>
</asp:GridView>
<br />
<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:NorthWindConnectionString %>“ SelectCommand=”SELECT [ProductName], [UnitsInStock] FROM [Alphabetical List of Products] WHERE ([ProductName] = @ProductName OR 1 = @CompareInteger) ORDER BY [ProductName]”>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
Notice that autopostback is set on the dropdownlist as well as the select change event. That’s it! Give it a try.
March 27th, 2007 at 4:48 am
Finally a solution that worked. I’ve been searching/fiddling for a while now.
Thanks!!!
October 2nd, 2007 at 1:23 pm
I had to dynamically generate the contents of the dropdown list, so I used the SQL union statement when populating it to add a row with “ALL” for the name and “1″ for the value.
Something like this: “SELECT [ProductName], [ProductId] FROM [Alphabetical List of Products] UNION Select ‘(All)’ as [ProductName], ‘1′ As [ProductId] ORDER BY [ProductName]>”
In the dropdownlist, I had set my DataTextField to [ProductName] and DataValueField to [ProductId]. Because of this, I didn’t have to add any of the code into the Page_Load function or have the SelectedIndexChanged function. The value was passed from the DropDownList into the select parameter of the data source, which was set to default to 1 so that all would show first.