Cascading Select list in ASP.NET AJAX

In this example I will show how to create a cascading select list in ASP.NET using data from SQL server database. The whole idea of cascading select list is to refine the other select list as per your selection in first select list. When user select a value from first select list it will populate the second select list depending on the first selection.

To demonstrate this example, let’s create two tables – emp, dept table. We will use the dept table for first selection and populate data in the select list from emp table. To create an dept table, click on database explorer in the right side. By default solution explorer is open when you open the Web Developer, switch to database explorer by clicking on that tab. Now you do not have any database installed, you can do so by create a new database file. That will be placed in App_data folder automatically.

[notice]Make sure you make the deptId as primary key and change the identity insert to YES and keep the default increment setting. Save the table and name is as dept table.[/notice]

I have created the emp table and empId is the primary key for this table with identity insert. Identity insert means when you create a new record into a table it will populate the next number from index into that table when inserting data. Click on the relationship icon on the toop toolber, it pops up a new window. click on Add button at the bottom. Now you will see an auto generated item under the selected relationship section. Click on tables and columns specified, select the foreign key table. In this case emp and select the key deptId and select the column deptId for dept table. Click on close button. Click on save button to save changes in the table.

Right click on dept table and click on show table data. Insert some data into the emp and dept tables. In the dept table make sure you typing any existing empId in the empId field as it a relational database now, otherwise it will not process your request.

Now in the default.aspx page I have added two controls and for the first one I have set the AutoPostBack property to YES so that the form will be submitted when value changed in the Department select list. Make sure EnableViewState is set to false for both select list. I have added an ScriptManager inside the form tag to enable AJAX functionality in the site. Added a update panel and placced the drop downlist inside the contentTemplate in the update Panel. I also have added an update progress image to display the progress of action. Here is what my code looks like –

<asp:ScriptManager ID="ScriptManager1" runat="server">    </asp:ScriptManager>
<div>
	<asp:UpdatePanel ID="UpdatePanel1" runat="server">
		<ContentTemplate>
			<asp:ValidationSummary ID="ValidationSummary1" runat="server" />
			Department:
			<asp:DropDownList ID="dlDept" runat="server" DataSourceID="dsDept" DataTextField="DeptName" DataValueField="deptId" 	AppendDataBoundItems="true" AutoPostBack="true" EnableViewState="false">
				<asp:ListItem Text="Select one" Value="" Selected="True" />
			</asp:DropDownList>        <br />
			Employee:
			<asp:DropDownList ID="dlEmployee" runat="server" DataSourceID="dsEmployee" DataTextField="name" DataValueField="empId" AppendDataBoundItems="true" EnableViewState="false">
				<asp:ListItem Text="Select one" Value="" Selected="True" />
			</asp:DropDownList>        

			<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="You must select one from list."            ControlToValidate="dlDept">*</asp:RequiredFieldValidator>
			<asp:Button ID="btnSubmit" runat="server" Text="Submit" />
		</ContentTemplate>
	</asp:UpdatePanel>

<asp:UpdateProgress ID="UpdateProgress1" runat="server">
	<ProgressTemplate>
			<img src="loading.gif" />
	</ProgressTemplate>
</asp:UpdateProgress>   

</div>

I have created two SQLDataSource for those two select lists. First one is called dsDept, this is basically to display all the department name from the dept table. Next one is called dsEmployee to display the employee names from emp table depending on the deptId selected in the first list. So as soon as user select a department name from the first select list, it will populate the employee name into the employee select list. Here is the SqlDataSources –

<asp:SqlDataSource ID="dsDept" runat="server" ConnectionString="<%$ ConnectionStrings:myConnection %>"
		SelectCommand="SELECT deptId, deptName FROM dept ">
</asp:SqlDataSource>
<asp:SqlDataSource ID="dsEmployee" runat="server" ConnectionString="<%$ ConnectionStrings:myConnection %>"
		SelectCommand="SELECT     empId, FirstName + '  ' + LastName AS Name FROM emp WHERE (deptId = @deptId)">
<SelectParameters>
		<asp:ControlParameter Name="deptId" ControlID="dlDept" PropertyName="selectedValue" />
</SelectParameters>
</asp:SqlDataSource>

[important]

I have added the control parameter in dsEmployee datasource to set the deptId for selected value in the first select list. I also have included an update progress template to display progress when fetching the second select list.

[/important]