Hi andybazz,
I have created sample that full-fill your requirement by refering the below article.
HTML
<div align="center">
<asp:GridView ID="gvCustomers" DataKeyNames="CustomerId" runat="server" AutoGenerateColumns="false"
OnRowEditing="EditCustomer" OnRowDataBound="RowDataBound" OnRowUpdating="UpdateCustomer"
OnRowCancelingEdit="CancelEdit" AllowPaging="true" PageSize="10">
<Columns>
<asp:BoundField DataField="ContactName" HeaderText="Contact Name" />
<asp:TemplateField HeaderText="City">
<ItemTemplate>
<asp:Label ID="lblCity" runat="server" Text='<%# Eval("City")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="lblCity" runat="server" Text='<%# Eval("City")%>' Visible="false"></asp:Label>
<asp:DropDownList ID="ddlCities" runat="server">
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="True" />
</Columns>
</asp:GridView>
</div>
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindData();
}
}
private void BindData()
{
string query = "Customers_GetCustomers";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.StoredProcedure;
gvCustomers.DataSource = GetData(cmd);
gvCustomers.DataBind();
}
private DataTable GetData(SqlCommand cmd)
{
string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
}
}
protected void EditCustomer(object sender, GridViewEditEventArgs e)
{
gvCustomers.EditIndex = e.NewEditIndex;
BindData();
}
protected void CancelEdit(object sender, GridViewCancelEditEventArgs e)
{
gvCustomers.EditIndex = -1;
BindData();
}
protected void RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow && gvCustomers.EditIndex == e.Row.RowIndex)
{
DropDownList ddlCities = (DropDownList)e.Row.FindControl("ddlCities");
string query = "Customers_GetCities";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.StoredProcedure;
ddlCities.DataSource = GetData(cmd);
ddlCities.DataTextField = "CityName";
ddlCities.DataValueField = "CityName";
ddlCities.DataBind();
ddlCities.Items.FindByValue((e.Row.FindControl("lblCity") as Label).Text).Selected = true;
}
}
protected void UpdateCustomer(object sender, GridViewUpdateEventArgs e)
{
string city = (gvCustomers.Rows[e.RowIndex].FindControl("ddlCities") as DropDownList).SelectedItem.Value;
string customerId = gvCustomers.DataKeys[e.RowIndex].Value.ToString();
string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
string query = "Customer_UpdateCustomer";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@city", city);
cmd.Parameters.AddWithValue("@customerId", customerId);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Response.Redirect(Request.Url.AbsoluteUri);
}
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.BindData()
End If
End Sub
Private Sub BindData()
Dim query As String = "Customers_GetCustomers"
Dim cmd As New SqlCommand(query)
cmd.CommandType = CommandType.StoredProcedure
gvCustomers.DataSource = GetData(cmd)
gvCustomers.DataBind()
End Sub
Private Function GetData(cmd As SqlCommand) As DataTable
Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Using con As New SqlConnection(strConnString)
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function
Protected Sub EditCustomer(sender As Object, e As GridViewEditEventArgs)
gvCustomers.EditIndex = e.NewEditIndex
BindData()
End Sub
Protected Sub CancelEdit(sender As Object, e As GridViewCancelEditEventArgs)
gvCustomers.EditIndex = -1
BindData()
End Sub
Protected Sub RowDataBound(sender As Object, e As GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow AndAlso gvCustomers.EditIndex = e.Row.RowIndex Then
Dim ddlCities As DropDownList = DirectCast(e.Row.FindControl("ddlCities"), DropDownList)
Dim query As String = "Customers_GetCities"
Dim cmd As New SqlCommand(query)
cmd.CommandType = CommandType.StoredProcedure
ddlCities.DataSource = GetData(cmd)
ddlCities.DataTextField = "CityName"
ddlCities.DataValueField = "CityName"
ddlCities.DataBind()
ddlCities.Items.FindByValue(TryCast(e.Row.FindControl("lblCity"), Label).Text).Selected = True
End If
End Sub
Protected Sub UpdateCustomer(sender As Object, e As GridViewUpdateEventArgs)
Dim city As String = TryCast(gvCustomers.Rows(e.RowIndex).FindControl("ddlCities"), DropDownList).SelectedItem.Value
Dim customerId As String = gvCustomers.DataKeys(e.RowIndex).Value.ToString()
Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Using con As New SqlConnection(strConnString)
Dim query As String = "Customer_UpdateCustomer"
Using cmd As New SqlCommand(query)
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@city", city)
cmd.Parameters.AddWithValue("@customerId", customerId)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Response.Redirect(Request.Url.AbsoluteUri)
End Using
End Using
End Sub
SQL
ALTER PROCEDURE [dbo].[Customers_GetCities]
AS
BEGIN
SET NOCOUNT ON;
SELECT
DISTINCT [City] AS [CityName]
FROM [Customers]
ORDER BY [City]
END
GO
ALTER PROCEDURE [dbo].[Customers_GetCustomers]
AS
BEGIN
SET NOCOUNT ON;
SELECT [CustomerID]
,[CompanyName]
,[ContactName]
,[City]
,[Country]
FROM [Customers]
END
GO
Screenshot