Hi Kankon,
Please refer below sample.
Database
I have made use of the following Customers table with the schema as follows.
I have already inserted few records in the table.
You can download the database table SQL by clicking the download link below.
Download SQL file
HTML
<asp:RadioButtonList ID="rblCountries" runat="server" AutoPostBack="true" OnSelectedIndexChanged="OnSelectedIndexChanged">
<asp:ListItem Text="All" Value=""></asp:ListItem>
<asp:ListItem Text="India" Value="India" Selected="True"></asp:ListItem>
<asp:ListItem Text="Russia" Value="Russia"></asp:ListItem>
<asp:ListItem Text="France" Value="France"></asp:ListItem>
<asp:ListItem Text="United States" Value="United States"></asp:ListItem>
</asp:RadioButtonList>
<asp:GridView ID="gvCustomers" AutoGenerateColumns="false" runat="server" OnRowDataBound="OnRowDataBound">
<Columns>
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<asp:Label ID="lblRowNumber" runat="server" Text='<%# Eval("CustomerId")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<asp:Label ID="lblName" runat="server" Text='<%# Eval("Name")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<asp:Label ID="lblCountry" runat="server" Text='<%# Eval("Country")%>'></asp:Label>
<asp:DropDownList ID="ddlCountries" runat="server" BackColor="#FFFFCC" Font-Size="Large">
</asp:DropDownList>
<asp:Button Text="Update" ID="btnUpdate" runat="server" OnClick="Update" Font-Bold="True" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
Namespace
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindGrid(rblCountries.SelectedValue);
}
}
protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
//Find the DropDownList in the Row
DropDownList ddlCountries = (e.Row.FindControl("ddlCountries") as DropDownList);
ddlCountries.Items.Clear();
ddlCountries.DataSource = GetData("SELECT DISTINCT Country FROM Customers");
ddlCountries.DataTextField = "Country";
ddlCountries.DataValueField = "Country";
ddlCountries.DataBind();
ddlCountries.Items.Insert(0, new ListItem("Please select", ""));
string country = (e.Row.FindControl("lblCountry") as Label).Text;
if (ddlCountries.Items.FindByValue(country) != null)
{
ddlCountries.Items.FindByValue(country).Selected = true;
}
}
}
protected void OnSelectedIndexChanged(object sender, EventArgs e)
{
this.BindGrid(rblCountries.SelectedValue);
}
protected void Update(object sender, EventArgs e)
{
GridViewRow gvRow = (sender as Button).NamingContainer as GridViewRow;
string id = (gvRow.FindControl("lblRowNumber") as Label).Text.Trim();
string name = (gvRow.FindControl("lblName") as Label).Text.Trim();
string country = (gvRow.FindControl("ddlCountries") as DropDownList).SelectedItem.Value;
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand("UPDATE Customers SET Country = @Country WHERE CustomerId = @CustomerId", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@CustomerId", id);
if (!string.IsNullOrEmpty(country))
{
cmd.Parameters.AddWithValue("@Country", country);
}
else
{
cmd.Parameters.AddWithValue("@Country", DBNull.Value);
}
con.Open();
cmd.ExecuteNonQuery();
con.Close();
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('Record Updated.');", true);
}
}
this.BindGrid(rblCountries.SelectedValue);
}
private void BindGrid(string country)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, Name, Country FROM Customers WHERE Country = @Country OR @Country IS NULL", con))
{
cmd.CommandType = CommandType.Text;
if (!string.IsNullOrEmpty(country))
{
cmd.Parameters.AddWithValue("@Country", country);
}
else
{
cmd.Parameters.AddWithValue("@Country", DBNull.Value);
}
con.Open();
this.gvCustomers.DataSource = cmd.ExecuteReader();
this.gvCustomers.DataBind();
con.Close();
}
}
}
private DataSet GetData(string query)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlCommand cmd = new SqlCommand(query);
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds);
return ds;
}
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.BindGrid(rblCountries.SelectedValue)
End If
End Sub
Protected Sub OnRowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
Dim ddlCountries As DropDownList = (TryCast(e.Row.FindControl("ddlCountries"), DropDownList))
ddlCountries.Items.Clear()
ddlCountries.DataSource = GetData("SELECT DISTINCT Country FROM Customers")
ddlCountries.DataTextField = "Country"
ddlCountries.DataValueField = "Country"
ddlCountries.DataBind()
ddlCountries.Items.Insert(0, New ListItem("Please select", ""))
Dim country As String = (TryCast(e.Row.FindControl("lblCountry"), Label)).Text
If ddlCountries.Items.FindByValue(country) IsNot Nothing Then
ddlCountries.Items.FindByValue(country).Selected = True
End If
End If
End Sub
Protected Sub OnSelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
Me.BindGrid(rblCountries.SelectedValue)
End Sub
Protected Sub Update(ByVal sender As Object, ByVal e As EventArgs)
Dim gvRow As GridViewRow = TryCast((TryCast(sender, Button)).NamingContainer, GridViewRow)
Dim id As String = (TryCast(gvRow.FindControl("lblRowNumber"), Label)).Text.Trim()
Dim name As String = (TryCast(gvRow.FindControl("lblName"), Label)).Text.Trim()
Dim country As String = (TryCast(gvRow.FindControl("ddlCountries"), DropDownList)).SelectedItem.Value
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(conString)
Using cmd As SqlCommand = New SqlCommand("UPDATE Customers SET Country = @Country WHERE CustomerId = @CustomerId", con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@CustomerId", id)
If Not String.IsNullOrEmpty(country) Then
cmd.Parameters.AddWithValue("@Country", country)
Else
cmd.Parameters.AddWithValue("@Country", DBNull.Value)
End If
con.Open()
cmd.ExecuteNonQuery()
con.Close()
ClientScript.RegisterStartupScript([GetType](), "alert", "alert('Record Updated.');", True)
End Using
End Using
Me.BindGrid(rblCountries.SelectedValue)
End Sub
Private Sub BindGrid(ByVal country As String)
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(conString)
Using cmd As SqlCommand = New SqlCommand("SELECT CustomerId, Name, Country FROM Customers WHERE Country = @Country OR @Country IS NULL", con)
cmd.CommandType = CommandType.Text
If Not String.IsNullOrEmpty(country) Then
cmd.Parameters.AddWithValue("@Country", country)
Else
cmd.Parameters.AddWithValue("@Country", DBNull.Value)
End If
con.Open()
Me.gvCustomers.DataSource = cmd.ExecuteReader()
Me.gvCustomers.DataBind()
con.Close()
End Using
End Using
End Sub
Private Function GetData(ByVal query As String) As DataSet
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim cmd As SqlCommand = New SqlCommand(query)
Using con As SqlConnection = New SqlConnection(conString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using ds As DataSet = New DataSet()
sda.Fill(ds)
Return ds
End Using
End Using
End Using
End Function
Screenshot
