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>
Once the connection string has been properly established, the subsequent step involves binding the DropDownList control to the city column of the stores table in the pubs database, all within the default.aspx page. To successfully accomplish this binding process, it is imperative to have two essential components in place: the DropDownList control itself and an SqlDataSource.
DropDownList control
The DropDownList control serves as the interface element responsible for displaying and selecting the city values. It acts as a conduit between the user and the database, facilitating the presentation and retrieval of the desired data.
On the other hand, the SqlDataSource plays a vital role in establishing the connection between the DropDownList control and the underlying database. This indispensable component allows for seamless integration by retrieving the relevant data from the stores table's city column within the pubs 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>