Multiple DropdownLists with AppendDataBoundItems

With ASP.Net 2.0, using multiple DropdownLists is really simple. However, what happens when you want to add an extra item to the first DDL, like ‘ALL’?
Consider this scenario: You want all models (in this sample) returned, when the ‘All’ item is chosen.
You can’t put 2 SelectCommands in one DataSource control, but, when ‘All’ is selected, you still need the SQL statement (in this case, the DataSource Control’s ‘SelectCommand’ property) to change.

But, with just a little code, this is still fairly easy to accomplish. To do this, in this scenario, we’ll need two DropDownLists – one for the ‘Brands’ of automobiles, and one for the ‘Models’ of the particular brands. First, you create a new sub (here, it’s named ‘doCheck’, but you can call it anything you want), and add the following code:

Sub doCheck(Source as Object, E as EventArgs)
	if ddlBrand.SelectedItem.Value=99 then
		ds2.selectCommand="Select id, model from Models order by model"
	End If
End Sub

Then, reference that subroutine in the DropDownList’s tag:

<asp:DropDownList id="ddlBrand" DataTextField="Brand" DataValueField="id"
	 DataSourceID="DS1" AppendDataBoundItems="True" Runat="server"
	  AutoPostBack="True" OnSelectedIndexChanged="doCheck"
<asp:ListItem Value="99">All</asp:ListItem>
</asp:DropDownList>

The ‘AppendDataBoundItems’ property is added, along with the ListItem, containing a value, so that the ‘All’ entry gets added to the DDL along with all the items returned from the Database. Keep in mind, the ‘All item was appended here, with a value of 99. There’s nothing magical about that number. It simply must be a number higher than anything the DDL’s databound value will return.

Next, we’ll create the second DropDownList (for the models table):

<asp:DropDownList id="ddlModels" DataTextField="Model"
 	DataValueField="id"
	DataSourceID="DS2" Runat="server">
</asp:DropDownList>

Notice that each one of the DDLs reference it’s own DataSource control (DS1, and DS2). So, let’s create them here:

<asp:SQLDataSource ID="DS1"
	 Runat="Server"
	 SelectCommand = "SELECT id, Brand From Automobiles"
	ConnectionString="<%$ ConnectionStrings:YourConnStringGoesHere %>">
</asp:SQLDataSource>

<asp:SQLDataSource ID="DS2"
	 Runat="Server"
	ConnectionString="<%$ ConnectionStrings:YourConnStringGoesHere %>">
	<SelectParameters>
		<asp:ControlParameter ControlID="ddlBrand"
			 Name="ID"
			 PropertyName="SelectedValue" />
	</SelectParameters>
</asp:SQLDataSource>

The ‘SelectCommand’ in the first DataSource control is straight-forward. It simply returns all the brands from the ‘Automobiles’ table. However, in the second DataSource control, though it can be added directly into the DataSource Control, it’s a long string, so, to break it up, and save more horizontal real estate on the screen, we’re showing it here, in the ‘Page_Load‘ event routine:

Sub Page_Load(Source as Object, E as EventArgs)
	DS2.SelectCommand="SELECT Models.id, Models.Model From  " & _
	"Models INNER JOIN Automobiles ON Automobiles.id = Models.Auto_ID " & _
	"Where  ((Models.Auto_ID = @ID))"
End Sub

The second DataSource (DS2), as you will notice, adds a ControlParameter, referencing the SelectedValue of the ddlBrand DropDownList.

If you want to see an online example of this code, along with the source code, check out:
Multiple DropDownLists Sample/Source

That’s pretty much it. That’s all you need to be able to add an item to the first DDL, after databinding, allowing the user to select all models, changing the SQL statement which returns the models, displaying them in the second DropDownList.

Related Posts:

• No Related Posts