DropDownList in GridView
The GridView allows editing on a row-by-row basis. In the previous lesson we saw how to edit a Gridview using SqlDataSource.
Edit GridView
In this chapter, we are going add a DropDownList in the gridview control.
Download Database
In this article I have used Microsoft's Pubs database for sample data. You can download it free from the following link.
Download
After setting up the database, we should configure the Web.Config File for connectionStrings. Add the following code to the Web.Config File.
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="SQLDbConnection"
connectionString="Server=Your-Server-Name; Database=pubs; User Id=sa; password=*****"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
After setting the cpnnectionstring, we are going to bind the DropDownList to the city column of the stores table in the pubs database from default.aspx. In order to bind a DropDownList, we need a DropDownList control and an SqlDataSource to connect the datafield to the database. The following code create a TemplateField for city column in the webpage.
<asp:TemplateField HeaderText="city" SortExpression="city">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server"
DataSourceID="SqlDataSource2" DataTextField="city" DataValueField="city"
SelectedValue='<%# Bind("city") %>'>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("city") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
Also we need another SqlDataSource to connect the DropDownList to the database.
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:SQLDbConnection %>"
SelectCommand="SELECT DISTINCT [city] FROM [stores]">
</asp:SqlDataSource>
Copy and paste the following full source code for add a DropDownList to asp.net gridview
Default.aspx
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AutoGenerateEditButton="true"
AllowSorting="True" DataSourceID="SqlDataSource1" AllowPaging="True" DataKeyNames="stor_id">
<Columns>
<asp:BoundField ReadOnly="True" HeaderText="stor_id"
DataField="stor_id" SortExpression="stor_id"></asp:BoundField>
<asp:BoundField HeaderText="stor_name" DataField="stor_name"
SortExpression="stor_name"></asp:BoundField>
<asp:BoundField HeaderText="stor_address" DataField="stor_address"
SortExpression="stor_address"></asp:BoundField>
<asp:TemplateField HeaderText="city" SortExpression="city">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server"
DataSourceID="SqlDataSource2" DataTextField="city" DataValueField="city"
SelectedValue='<%# Bind("city") %>'>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("city") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="state" DataField="state"
SortExpression="state"></asp:BoundField>
<asp:BoundField HeaderText="zip" DataField="zip"
SortExpression="zip"></asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:SQLDbConnection %>"
SelectCommand="select * from stores"
UpdateCommand="UPDATE [stores] SET [stor_name] = @stor_name , [stor_address] = @stor_address , [city] = @city , [state]=@state , [zip]=@zip
WHERE [stor_id] = @stor_id" >
<UpdateParameters>
<asp:Parameter Type="String" Name="stor_name"></asp:Parameter>
<asp:Parameter Type="String" Name="stor_address"></asp:Parameter>
<asp:Parameter Type="String" Name="city"></asp:Parameter>
<asp:Parameter Type="String" Name="state"></asp:Parameter>
<asp:Parameter Type="String" Name="zip"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
</div>
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:SQLDbConnection %>"
SelectCommand="SELECT DISTINCT [city] FROM [stores]"></asp:SqlDataSource>
</form>
</body>
</html>