Pass DropDownList Selected Value as Parameter to SqlDataSource in ASPNet

In this article I will explain with an example and attached sample code, how to pass Selected Value of DropDownList as CommandParameter to SqlDataSource in ASP.Net.

Database

Here I am making use of Microsoft’s Northwind Database. You can download it from here

Download and install Northwind Database

Populating the DropDownList for Filtering GridView Records

Firstly I am populating the DropDownList with Distinct Countries from the Customers table of the Northwind Database, so that later on we can filter the GridView records based on the selected country.

The default item in the DropDownList is set as All Countries, so that by default all records are loaded in the GridView

Select Country:

<asp:DropDownList ID="ddlCountries" runat="server" AutoPostBack="true" DataSourceID="DropDownDataSource"

DataTextField="Country" DataValueField="Country" AppendDataBoundItems="true">

<asp:ListItem Text="All Countries" Value="" />

</asp:DropDownList>

<asp:SqlDataSource ID="DropDownDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConStr %>"

SelectCommand="SELECT DISTINCT Country FROM Customers"></asp:SqlDataSource>

Populating and Filtering the GridView

For populating the GridView, I am making use of another SqlDataSource where I have specified FilterExpression along with the SelectCommand.

<asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"

runat="server" AutoGenerateColumns="false" DataSourceID="GridDataSource">

<asp:BoundField DataField="ContactName" HeaderText="Name" ItemStyle-Width="150" />

<asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="150" />

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="GridDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConStr %>"

SelectCommand="SELECT ContactName, City, Country FROM Customers" FilterExpression="Country = '{0}'">

<asp:ControlParameter Name="Country" ControlID="ddlCountries" PropertyName="SelectedValue" />

</FilterParameters>

</asp:SqlDataSource>

SelectCommand – Here we need to set the default SQL Query without any WHERE Clause or filter.

FilterExpression – Here we need to set the filter part of the SQL Query with a placeholder {0}. The placeholder {0} will be replaced by the value of the ControlParameter, i.e. the DropDownList’s Selected Value

FilterParameters – Here we specify the Control Parameters to be used by the FilterExpression

As soon as some item is selected in the DropDownList the GridView records are filtered, this happens because we have set AutoPostBack property to true for the DropDownList, if this property is not set then the GridView records will not be refreshed and filtered.

Note: SqlDataSource can also filter records in GridView using QueryString Parameters, to know more about it please refer ASP.Net SqlDataSource pass value to SelectParameter using QueryString Parameter Example