In this Example we will see how to Open Drop down Controls inside Gridview While we Editing Particular rows from Gridview in dot net. Well we will Use SQL DataSource Control to Bind Gridview in our Web Form and Create Edit Item Template inside Gridview for Editing and updating record from Gridview. Here we will user two SQL DataSource for binding Gridview Data and for Drop down when we edit record in GridView.
Here we will take Simple example
for Store so Create table Store in your Sql Database and another table for
City. For City we will select and update in Store table.
SQL SCRIPT FOR TABLE
STORE TABLE
CREATE TABLE [dbo].[stores](
[stor_id] [bigint] IDENTITY(1,1) NOT NULL,
[stor_name] [varchar](40) NULL,
[stor_address] [varchar](40) NULL,
[city] [varchar](20) NULL,
[state] [char](2) NULL,
[zip] [bigint] NULL,
CONSTRAINT
[PK_stores] PRIMARY KEY
CLUSTERED
(
[stor_id] ASC
)WITH
(PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CITY TABLE
CREATE TABLE [dbo].[U_CITY_MASTER](
[CITY_CODE] [nvarchar](10) NOT NULL,
[CITY_NAME] [nvarchar](100) NOT NULL,
) ON [PRIMARY]
GO
In your Webpage let’s Drag
Gridview and bind using SQL DataSource so we can get Data from Sql. Now
AutoGeneratedColumn set False in Gridview so we can create our Own Column in
Gridview and Create Columns from using Bind Field in Gridview Otherwise it will
automatically create for you when you run SQL DataSource and Bind Data.
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AutoGenerateEditButton="True" AllowSorting="True" DataSourceID="SqlDataSource1" AllowPaging="True" DataKeyNames="stor_id" BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" ForeColor="Black" GridLines="Vertical">
<AlternatingRowStyle BackColor="#CCCCCC" />
<Columns>
<asp:BoundField ReadOnly="True" HeaderText="Store ID" DataField="stor_id" SortExpression="stor_id">
</asp:BoundField>
<asp:BoundField HeaderText="Store
Name" DataField="stor_name" SortExpression="stor_name" ReadOnly="True"></asp:BoundField>
<asp:BoundField HeaderText="Store Address" DataField="stor_address" SortExpression="stor_address" ReadOnly="True"></asp:BoundField>
<asp:TemplateField HeaderText="City" SortExpression="city">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1"
runat="server"
DataSourceID="SqlDataSource2"
DataTextField="CITY_NAME" DataValueField="CITY_NAME" 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"
ReadOnly="True">
</asp:BoundField>
<asp:BoundField HeaderText="Zip" DataField="zip" SortExpression="zip" ReadOnly="True">
</asp:BoundField>
</Columns>
<FooterStyle BackColor="#CCCCCC" />
<HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#808080" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#383838" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:UHConnectionString %>" 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:DBCS %>" SelectCommand="SELECT
[CITY_NAME] FROM [U_CITY_MASTER]"></asp:SqlDataSource>
You have to Set Update Parameter in SqlDataSource when you bind in
Gridview for Data.
<asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:UHConnectionString %>" 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>
Now in your Gridview Set Update Parameter for all Columns what we Create in Gridview. And Bind another
SqlDataSource for taking City from another table.
If you want to see where your
connection string is then open Web.config file and see in Connection String
Parameter for your Connection String.
If you want to enable while
Editing Record all column then Change Read Only
property to True in Bound Field in Gridview.
In your Gridview Enable Paging, Sorting, Editing so we can
Directly Edit record in GridView we don’t need to create for that.
Now open your Webpage in browser
and Check edit link button is Enable or not then click on link button to Edit
Records and Click on City Field there you can see Dropdown is created for you
by EditTemplateField in Gridview.
Thanks for comments.....