Inject record in SqlDataSource

Question

Hello,

I would like suggestions on how to inject a record into my DropdownList to give an "All" option. Here is my code, data coming from the Northwind database.

<asp:DropDownList ID="ddlRegion" runat="server" DataSourceID="SqlDataSource1" 
    DataTextField="RegionDescription" DataValueField="RegionID" 
        AutoPostBack="True" onselectedindexchanged="ddlRegion_SelectedIndexChanged" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
    SelectCommand="SELECT [RegionID], [RegionDescription] FROM [Region]" 
        ondatabinding="SqlDataSource1_Databinding">
</asp:SqlDataSource>

I have tried the following but it does not add the item;

if(!Page.IsPostBack())
ddlRegion.Items.Insert(0, new ListItem("All", "-1"));

I am thinking perhaps using the ondatabinding might be the right way to go but am not sure how to go about it.

Thanks

Answer 1

Just add the All item in the ASPX, and then set AppendDataBoundItems=true and it will keep the statically defined items with the data bound items.

Answer 2

Timbagas makes a good suggestion, another one is to use a stored procedure and do the append there so the recordset that is returned includes the 'All' option, plus the rows that you want.