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”>
<span class="kwrd">protected</span> <span class="kwrd">void</span> Page_Load(<span class="kwrd">object</span> sender, EventArgs e) { <span class="kwrd">if</span> (!IsPostBack) { ControlParameter cp = <span class="kwrd">new</span> ControlParameter(<span class="str">"ProductName"</span>,TypeCode.String,<span class="str">"DropDownList1"</span>,<span class="str">"SelectedValue"</span>); SqlDataSource1.SelectParameters.Add(cp); Parameter p = <span class="kwrd">new</span> Parameter(<span class="str">"CompareInteger"</span>, TypeCode.Int32); p.DefaultValue = <span class="str">"1"</span>; SqlDataSource1.SelectParameters.Add(p); } } <span class="kwrd">protected</span> <span class="kwrd">void</span> DropDownList1_SelectedIndexChanged(<span class="kwrd">object</span> sender, EventArgs e) { <span class="kwrd">if</span> (DropDownList1.SelectedValue.Equals(<span class="str">"(All)"</span>)) { SqlDataSource1.SelectParameters[1].DefaultValue = <span class="str">"1"</span>; } <span class="kwrd">else</span> { SqlDataSource1.SelectParameters[1].DefaultValue = <span class="str">"999"</span>; <span class="rem">// not one</span> } }</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: pan style=“color: maroon;“>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.