Insert multiple DropDownList values inside ASP.Net GridView in Database using C# and VB.Net

Hi BugHunter,

Refer below sample.

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" EnableViewState="true"
    OnRowDataBound="GridView1_RowDataBound">
    <Columns>
        <asp:BoundField HeaderText="Id" DataField="CountryId" />
        <asp:TemplateField HeaderText="Country">
            <ItemTemplate>
                <asp:DropDownList ID="ddlCountries" runat="server" AutoPostBack="true" OnSelectedIndexChanged="drpcountry_SelectedIndexChanged">
                </asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="State">
            <ItemTemplate>
                <asp:DropDownList ID="ddlState" runat="server" AutoPostBack="true" OnSelectedIndexChanged="drpstate_SelectedIndexChanged">
                </asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="City/Town">
            <ItemTemplate>
                <asp:DropDownList runat="server" ID="ddlCity" ClientIDMode="Static" Width="100px">
                </asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<br />
<asp:Button Text="Save" runat="server" OnClick="Save" />
<br />
<asp:GridView ID="Gridview2" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField HeaderText="Country" DataField="Country" />
        <asp:BoundField HeaderText="State" DataField="State" />
        <asp:BoundField HeaderText="City" DataField="City" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        GridView1.DataSource = GetData("SELECT * FROM Countries");
        GridView1.DataBind();
    }
}
protected void Save(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] { new DataColumn("Country", typeof(string)), new DataColumn("State", typeof(string)), new DataColumn("City", typeof(string)) });
    DropDownList ddlCountries = null;
    DropDownList ddlStates = null;
    DropDownList ddlCities = null;
    foreach (GridViewRow row in GridView1.Rows)
    {
        if (row.RowType == DataControlRowType.DataRow)
        {
            ddlCountries = (row.FindControl("ddlCountries") as DropDownList);
            ddlStates = (row.FindControl("ddlState") as DropDownList);
            ddlCities = (row.FindControl("ddlCity") as DropDownList);
            string country = (ddlCountries.SelectedItem.Value == "0" ? string.Empty : ddlCountries.SelectedItem.Value);
            string state = (ddlStates.SelectedItem.Value == "0" ? string.Empty : ddlStates.SelectedItem.Value);
            string city = (ddlCities.SelectedItem.Value == "0" ? string.Empty : ddlCities.SelectedItem.Value);
            if (country != "" || state != "" || city != "")
            {
                dt.Rows.Add(ddlCountries.SelectedItem.Text == "Select Country" ? "NULL" : ddlCountries.SelectedItem.Text, ddlStates.SelectedItem.Text == "Select State" ? "NULL" : ddlStates.SelectedItem.Text, ddlCities.SelectedItem.Text == "Select City" ? "NULL" : ddlCities.SelectedItem.Text);
            }
            else
            {
                dt.Rows.Add(country == "" ? "NULL" : ddlCountries.SelectedItem.Value, country == "" ? "NULL" : ddlStates.SelectedItem.Value, country == "" ? "NULL" : ddlCities.SelectedItem.Value);
            }
        }
    }
    Gridview2.DataSource = dt;
    Gridview2.DataBind();
}

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;
            }
        }
    }
}

private void BindDropDownList(DropDownList ddl, string query, string text, string value, string defaultText)
{
    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;
            con.Open();
            ddl.DataSource = cmd.ExecuteReader();
            ddl.DataTextField = text;
            ddl.DataValueField = value;
            ddl.DataBind();
            con.Close();
        }
    }
    ddl.Items.Insert(0, new ListItem(defaultText, "0"));
}

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    DropDownList ddlCountries = null;
    DropDownList ddlStates = null;
    DropDownList ddlCities = null;
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        ddlCountries = (e.Row.FindControl("ddlCountries") as DropDownList);
        ddlStates = (e.Row.FindControl("ddlState") as DropDownList);
        ddlCities = (e.Row.FindControl("ddlCity") as DropDownList);
        BindDropDownList(ddlCountries, "SELECT DISTINCT CountryName,CountryId FROM Countries", "CountryName", "CountryId", "Select Country");
        BindDropDownList(ddlStates, "SELECT DISTINCT StateName,StateId FROM States", "StateName", "StateId", "Select Country");
        BindDropDownList(ddlCities, "SELECT DISTINCT CityName,CityId FROM Cities", "CityName", "CityId", "Select Country");
        ddlStates.Enabled = false;
        ddlCities.Enabled = false;
        ddlStates.Items.Insert(0, new ListItem("Select State", "0"));
        ddlCities.Items.Insert(0, new ListItem("Select City", "0"));
    }
}

protected void drpcountry_SelectedIndexChanged(object sender, EventArgs e)
{
    DropDownList ddlCountries = (DropDownList)sender;
    GridViewRow currentRow = (GridViewRow)ddlCountries.NamingContainer;
    DropDownList ddlStates = (DropDownList)currentRow.FindControl("ddlState");
    DropDownList ddlCities = (DropDownList)currentRow.FindControl("ddlCity");
    ddlStates.Enabled = false;
    ddlCities.Enabled = false;
    ddlStates.Items.Clear();
    ddlCities.Items.Clear();
    ddlStates.Items.Insert(0, new ListItem("Select State", "0"));
    ddlCities.Items.Insert(0, new ListItem("Select City", "0"));
    int countryId = int.Parse(ddlCountries.SelectedItem.Value);
    if (countryId > 0)
    {
        string query = string.Format("select StateId, StateName from States where CountryId = {0}", countryId);
        BindDropDownList(ddlStates, query, "StateName", "StateId", "Select State");
        ddlStates.Enabled = true;
    }
}

protected void drpstate_SelectedIndexChanged(object sender, EventArgs e)
{
    DropDownList ddlCountries = (DropDownList)sender;
    GridViewRow currentRow = (GridViewRow)ddlCountries.NamingContainer;
    DropDownList ddlStates = (DropDownList)currentRow.FindControl("ddlState");
    DropDownList ddlCities = (DropDownList)currentRow.FindControl("ddlCity");
    ddlCities.Enabled = false;
    ddlCities.Items.Clear();
    ddlCities.Items.Insert(0, new ListItem("Select City", "0"));
    int stateId = int.Parse(ddlStates.SelectedItem.Value);
    if (stateId > 0)
    {
        string query = string.Format("select CityId, CityName from Cities where StateId = {0}", stateId);
        BindDropDownList(ddlCities, query, "CityName", "CityId", "Select City");
        ddlCities.Enabled = true;
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        GridView1.DataSource = GetData("SELECT * FROM Countries")
        GridView1.DataBind()
    End If
End Sub

Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn() {New DataColumn("Country", GetType(String)), New DataColumn("State", GetType(String)), New DataColumn("City", GetType(String))})
    Dim ddlCountries As DropDownList = Nothing
    Dim ddlStates As DropDownList = Nothing
    Dim ddlCities As DropDownList = Nothing
    For Each row As GridViewRow In GridView1.Rows
        If row.RowType = DataControlRowType.DataRow Then
            ddlCountries = (TryCast(row.FindControl("ddlCountries"), DropDownList))
            ddlStates = (TryCast(row.FindControl("ddlState"), DropDownList))
            ddlCities = (TryCast(row.FindControl("ddlCity"), DropDownList))
            Dim country As String = (If(ddlCountries.SelectedItem.Value = "0", String.Empty, ddlCountries.SelectedItem.Value))
            Dim state As String = (If(ddlStates.SelectedItem.Value = "0", String.Empty, ddlStates.SelectedItem.Value))
            Dim city As String = (If(ddlCities.SelectedItem.Value = "0", String.Empty, ddlCities.SelectedItem.Value))
            If country <> "" OrElse state <> "" OrElse city <> "" Then
                dt.Rows.Add(If(ddlCountries.SelectedItem.Text = "Select Country", "NULL", ddlCountries.SelectedItem.Text), If(ddlStates.SelectedItem.Text = "Select State", "NULL", ddlStates.SelectedItem.Text), If(ddlCities.SelectedItem.Text = "Select City", "NULL", ddlCities.SelectedItem.Text))
            Else
                dt.Rows.Add(If(country = "", "NULL", ddlCountries.SelectedItem.Value), If(country = "", "NULL", ddlStates.SelectedItem.Value), If(country = "", "NULL", ddlCities.SelectedItem.Value))
            End If
        End If
    Next

    Gridview2.DataSource = dt
    Gridview2.DataBind()
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

Private Sub BindDropDownList(ByVal ddl As DropDownList, ByVal query As String, ByVal text As String, ByVal value As String, ByVal defaultText As String)
    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
            con.Open()
            ddl.DataSource = cmd.ExecuteReader()
            ddl.DataTextField = text
            ddl.DataValueField = value
            ddl.DataBind()
            con.Close()
        End Using
    End Using

    ddl.Items.Insert(0, New ListItem(defaultText, "0"))
End Sub

Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
    Dim ddlCountries As DropDownList = Nothing
    Dim ddlStates As DropDownList = Nothing
    Dim ddlCities As DropDownList = Nothing
    If e.Row.RowType = DataControlRowType.DataRow Then
        ddlCountries = (TryCast(e.Row.FindControl("ddlCountries"), DropDownList))
        ddlStates = (TryCast(e.Row.FindControl("ddlState"), DropDownList))
        ddlCities = (TryCast(e.Row.FindControl("ddlCity"), DropDownList))
        BindDropDownList(ddlCountries, "SELECT DISTINCT CountryName,CountryId FROM Countries", "CountryName", "CountryId", "Select Country")
        BindDropDownList(ddlStates, "SELECT DISTINCT StateName,StateId FROM States", "StateName", "StateId", "Select Country")
        BindDropDownList(ddlCities, "SELECT DISTINCT CityName,CityId FROM Cities", "CityName", "CityId", "Select Country")
        ddlStates.Enabled = False
        ddlCities.Enabled = False
        ddlStates.Items.Insert(0, New ListItem("Select State", "0"))
        ddlCities.Items.Insert(0, New ListItem("Select City", "0"))
    End If
End Sub

Protected Sub drpcountry_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim ddlCountries As DropDownList = CType(sender, DropDownList)
    Dim currentRow As GridViewRow = CType(ddlCountries.NamingContainer, GridViewRow)
    Dim ddlStates As DropDownList = CType(currentRow.FindControl("ddlState"), DropDownList)
    Dim ddlCities As DropDownList = CType(currentRow.FindControl("ddlCity"), DropDownList)
    ddlStates.Enabled = False
    ddlCities.Enabled = False
    ddlStates.Items.Clear()
    ddlCities.Items.Clear()
    ddlStates.Items.Insert(0, New ListItem("Select State", "0"))
    ddlCities.Items.Insert(0, New ListItem("Select City", "0"))
    Dim countryId As Integer = Integer.Parse(ddlCountries.SelectedItem.Value)
    If countryId > 0 Then
        Dim query As String = String.Format("select StateId, StateName from States where CountryId = {0}", countryId)
        BindDropDownList(ddlStates, query, "StateName", "StateId", "Select State")
        ddlStates.Enabled = True
    End If
End Sub

Protected Sub drpstate_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim ddlCountries As DropDownList = CType(sender, DropDownList)
    Dim currentRow As GridViewRow = CType(ddlCountries.NamingContainer, GridViewRow)
    Dim ddlStates As DropDownList = CType(currentRow.FindControl("ddlState"), DropDownList)
    Dim ddlCities As DropDownList = CType(currentRow.FindControl("ddlCity"), DropDownList)
    ddlCities.Enabled = False
    ddlCities.Items.Clear()
    ddlCities.Items.Insert(0, New ListItem("Select City", "0"))
    Dim stateId As Integer = Integer.Parse(ddlStates.SelectedItem.Value)
    If stateId > 0 Then
        Dim query As String = String.Format("select CityId, CityName from Cities where StateId = {0}", stateId)
        BindDropDownList(ddlCities, query, "CityName", "CityId", "Select City")
        ddlCities.Enabled = True
    End If
End Sub

Screenshot