Hi ashthakor,
Check this example. Now please take its reference and correct your code.
Database
For this sample I have used of NorthWind database that you can download using the link given below.
Download Northwind Database
Namespaces
C#
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
HTML
<div>
<asp:ScriptManager runat="server" />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="True"
PageSize="10" OnPageIndexChanging="PageIndexChanging" OnRowDataBound="RowDataBound">
<Columns>
<asp:BoundField DataField="CustomerId" HeaderText="CustomerId" />
<asp:BoundField DataField="ContactName" HeaderText="Name" />
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<asp:UpdatePanel runat="server">
<ContentTemplate>
<asp:HiddenField ID="hfCountry" runat="server" Value='<%#Eval("Country") %>' />
<asp:DropDownList ID="ddlCountries" runat="server" AutoPostBack="true" OnSelectedIndexChanged="CountryChange">
</asp:DropDownList>
</ContentTemplate>
</asp:UpdatePanel>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<br />
<asp:Button Text="Save" OnClick="Save" runat="server" />
</div>
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridView();
}
}
private void BindGridView()
{
DataTable dt = new DataTable();
if (ViewState["Data"] != null)
{
dt = ViewState["Data"] as DataTable;
}
else
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
{
string query = "SELECT CustomerId,ContactName,Country FROM Customers";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dt);
ViewState["Data"] = dt;
}
}
}
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void CountryChange(object sender, EventArgs e)
{
DropDownList ddl = sender as DropDownList;
GridViewRow row = ddl.NamingContainer as GridViewRow;
string customerId = row.Cells[0].Text;
string updatedValue = ddl.SelectedValue;
DataTable dt = ViewState["Data"] as DataTable;
DataRow[] customerRow = dt.Select("CustomerID = '" + customerId + "'");
customerRow[0]["Country"] = updatedValue;
ViewState["Data"] = dt;
BindGridView();
}
protected void Save(object sender, EventArgs e)
{
GridView1.AllowPaging = false;
BindGridView();
foreach (GridViewRow row in GridView1.Rows)
{
DropDownList ddlCountry = (row.FindControl("ddlCountries") as DropDownList);
string id = row.Cells[0].Text;
string name = row.Cells[1].Text;
string country = ddlCountry.SelectedValue;
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
{
string query = "UPDATE Customers SET Country = @Country WHERE CustomerId = @CustomerId";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Country", country);
cmd.Parameters.AddWithValue("@CustomerId", id);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
GridView1.AllowPaging = true;
BindGridView();
}
protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
BindGridView();
}
protected void RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
string country = (e.Row.FindControl("hfCountry") as HiddenField).Value;
DropDownList ddlCountries = (e.Row.FindControl("ddlCountries") as DropDownList);
ddlCountries.DataSource = PopulateCountries();
ddlCountries.DataTextField = "Country";
ddlCountries.DataValueField = "Country";
ddlCountries.DataBind();
if (!string.IsNullOrEmpty(country) && ddlCountries.Items.FindByValue(country) != null)
{
ddlCountries.Items.FindByValue(country).Selected = true;
}
}
}
private DataTable PopulateCountries()
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
{
string query = "SELECT DISTINCT Country FROM Customers WHERE Country IS NOT NULL";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
using (DataTable dt = new DataTable())
{
da.Fill(dt);
return dt;
}
}
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Me.BindGridView()
End If
End Sub
Private Sub BindGridView()
Dim dt As DataTable = New DataTable
If (Not (ViewState("Data")) Is Nothing) Then
dt = CType(ViewState("Data"), DataTable)
Else
Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
Dim query As String = "SELECT CustomerId,ContactName,Country FROM Customers"
Dim cmd As SqlCommand = New SqlCommand(query)
cmd.Connection = con
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
da.Fill(dt)
ViewState("Data") = dt
End If
GridView1.DataSource = dt
GridView1.DataBind()
End Sub
Protected Sub CountryChange(ByVal sender As Object, ByVal e As EventArgs)
Dim ddl As DropDownList = CType(sender, DropDownList)
Dim row As GridViewRow = CType(ddl.NamingContainer, GridViewRow)
Dim customerId As String = row.Cells(0).Text
Dim updatedValue As String = ddl.SelectedValue
Dim dt As DataTable = CType(ViewState("Data"), DataTable)
Dim customerRow() As DataRow = dt.Select(("CustomerID = '" + (customerId + "'")))
customerRow(0)("Country") = updatedValue
ViewState("Data") = dt
Me.BindGridView()
End Sub
Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs)
GridView1.AllowPaging = False
Me.BindGridView()
For Each row As GridViewRow In GridView1.Rows
Dim ddlCountry As DropDownList = CType(row.FindControl("ddlCountries"), DropDownList)
Dim id As String = row.Cells(0).Text
Dim name As String = row.Cells(1).Text
Dim country As String = ddlCountry.SelectedValue
Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
Dim query As String = "UPDATE Customers SET Country = @Country WHERE CustomerId = @CustomerId"
Dim cmd As SqlCommand = New SqlCommand(query)
cmd.Connection = con
cmd.Parameters.AddWithValue("@Country", country)
cmd.Parameters.AddWithValue("@CustomerId", id)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Next
GridView1.AllowPaging = True
Me.BindGridView()
End Sub
Protected Sub PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
GridView1.PageIndex = e.NewPageIndex
Me.BindGridView()
End Sub
Protected Sub RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
If (e.Row.RowType = DataControlRowType.DataRow) Then
Dim country As String = CType(e.Row.FindControl("hfCountry"), HiddenField).Value
Dim ddlCountries As DropDownList = CType(e.Row.FindControl("ddlCountries"), DropDownList)
ddlCountries.DataSource = Me.PopulateCountries
ddlCountries.DataTextField = "Country"
ddlCountries.DataValueField = "Country"
ddlCountries.DataBind()
If (Not String.IsNullOrEmpty(country) _
AndAlso (Not (ddlCountries.Items.FindByValue(country)) Is Nothing)) Then
ddlCountries.Items.FindByValue(country).Selected = True
End If
End If
End Sub
Private Function PopulateCountries() As DataTable
Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
Dim query As String = "SELECT DISTINCT Country FROM Customers WHERE Country IS NOT NULL"
Dim cmd As SqlCommand = New SqlCommand(query)
cmd.Connection = con
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt As DataTable = New DataTable
da.Fill(dt)
Return dt
End Function
Screenshot
