Tuesday, January 15, 2008

asp.net 2.0 and up: DropDownList in GridView (using SQLDataSource)

To have a dropdownlist inside a gridview, we use the property "SelectedValue='<%# Eval("datakeyfield") %>' knowing that it won't show in the intellisense. To handle null values, we can have an item with a value of null. The SQLDataSource for the dropdownlist is better off put outside the gridview, so that it does not get created as many times as the dropdownlist is.

Here's the code for the DropDownList Template Field :
The Dropdownlist is Filled From a table called Categories (see schema below)

<asp:TemplateField HeaderText="Category" SortExpression="CategoryID">
<ItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SourceCategories"
AppendDataBoundItems="true" DataTextField="CategoryName" DataValueField="CategoryID"
Width="190px" Enabled="false" SelectedValue='<%# Eval("CategoryID") %>'>
<asp:ListItem Text="No Item was Selected, to handle null values" Value="" />
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>



In the code above, Notice the property SelectedValue, and the static Item to handle null values. to this, we add the AppendDataBoundItems Property, and the dropdownlist is ready.


Here's the complete code listing with a functional gridview:





The GridView is filled from a table Called Clients (see schema below)



<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
DataSourceID="SourceClients" ForeColor="#333333" GridLines="Both" Font-Names="Verdana"
Font-Size="10px" AllowSorting="true">
<Columns>
<asp:BoundField DataField="ClientID" HeaderText="ClientID" InsertVisible="False"
ReadOnly="True" SortExpression="ClientID" />
<asp:BoundField DataField="FullName" HeaderText="FullName" SortExpression="FullName" />
<asp:TemplateField HeaderText="Category" SortExpression="CategoryID">
<ItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SourceCategories"
AppendDataBoundItems="true" DataTextField="CategoryName" DataValueField="CategoryID"
Width="190px" Enabled="false" SelectedValue='<%# Eval("CategoryID") %>'>
<asp:ListItem Text="No Item was Selected, to handle null values" Value="" />
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>


</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SourceClients" runat="server" ConnectionString="<%$ ConnectionStrings:ClientsConnectionString %>"
SelectCommand="SELECT * FROM [Clients]"></asp:SqlDataSource>
<asp:SqlDataSource ID="SourceCategories" runat="server" ConnectionString="<%$ ConnectionStrings:ClientsConnectionString %>"
SelectCommand="SELECT * FROM [Categories]"></asp:SqlDataSource>
</div>


Here's are the table definitions: (int and varchar(50) are the datatypes used)


image


Screen Shot for the gridview


image


Comments are welcome

4 comments:

Anonymous said...

The SortExpression attribute on the Category column is "CategoryID". This will sort based upon the ID, not the text that is displayed. If you didn't add the Category values to the database in alphabetical order (which is almost always the case), the results will not appear to be sorted. Is there a way to have the sort work against the Text instead of the ID?

oudinia said...

Hi there,

Sorry for responding so late, It got really busy this month. It does sort by ID for now. I'll check on the sorting for that

thanks for the comment

Anonymous said...

That works great.
Now how would I go about updating the database with the selected values?

Anonymous said...

To update values using the Edit functionality of the GridView, use Bind instead of Eval in the SelectedValue attribute of the DropDownList