Update record in Nested ASP.Net GridView on TextBox Change using C# and VB.Net

Hey Warren,

Please refer below sample.

HTML

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
    $("[src*=plus]").live("click", function () {
        $(this).closest("tr").after("<tr><td></td><td colspan = '999'>" + $(this).next().html() + "</td></tr>")
        $(this).attr("src", "images/minus.png");
    });
    $("[src*=minus]").live("click", function () {
        $(this).attr("src", "images/plus.png");
        $(this).closest("tr").next().remove();
    });
</script>
<asp:ScriptManager runat="server" />
<asp:UpdatePanel runat="server">
    <ContentTemplate>
        <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" CssClass="Grid"
            DataKeyNames="CustomerID" OnRowDataBound="OnRowDataBound">
            <Columns>
                <asp:TemplateField>
                    <ItemTemplate>
                        <img alt="" style="cursor: pointer" src="images/plus.png" />
                        <asp:Panel ID="pnlOrders" runat="server" Style="display: none">
                            <asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="false" CssClass="ChildGrid">
                                <Columns>
                                    <asp:TemplateField HeaderText="OrderId">
                                        <ItemTemplate>
                                            <asp:TextBox ID="txtOrderId" runat="server" Text='<%#Eval("OrderId") %>' />
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="OrderDate">
                                        <ItemTemplate>
                                            <asp:TextBox ID="txtDate" runat="server" Text='<%#Eval("OrderDate") %>' AutoPostBack="true"
                                                OnTextChanged="Change" />
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                </Columns>
                            </asp:GridView>
                        </asp:Panel>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="ContactName">
                    <ItemTemplate>
                        <asp:TextBox ID="txtName" runat="server" Text='<%#Eval("ContactName") %>' />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="City">
                    <ItemTemplate>
                        <asp:TextBox ID="txtCity" runat="server" Text='<%#Eval("City") %>' />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Update">
                    <ItemTemplate>
                        <asp:Button Text="Update" runat="server" ID="btnUpdate" />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </ContentTemplate>
</asp:UpdatePanel>

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)
    {
        gvCustomers.DataSource = GetData("select top 10 * from Customers");
        gvCustomers.DataBind();
    }
}

private static DataTable GetData(string query)
{
    string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = query;
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    return dt;
                }
            }
        }
    }
}

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        string customerId = gvCustomers.DataKeys[e.Row.RowIndex].Value.ToString();
        GridView gvOrders = e.Row.FindControl("gvOrders") as GridView;
        gvOrders.DataSource = GetData(string.Format("select top 3 * from Orders where CustomerId='{0}'", customerId));
        gvOrders.DataBind();
    }
}

protected void Change(object sender, EventArgs e)
{
    GridViewRow row = (sender as TextBox).NamingContainer as GridViewRow;
    string orderId = (row.FindControl("txtOrderId") as TextBox).Text;
    string orderdate = (row.FindControl("txtDate") as TextBox).Text;
    string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlCommand cmd = new SqlCommand("UPDATE Orders SET OrderDate = @OrderDate WHERE OrderID = @OrderId", con))
        {
            cmd.Parameters.AddWithValue("@OrderId", orderId.Split(',')[orderId.Split(',').Length - 1]);
            cmd.Parameters.AddWithValue("@OrderDate", orderdate.Split(',')[orderdate.Split(',').Length - 1]);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    (row.FindControl("txtDate") as TextBox).Text = orderdate.Split(',')[orderdate.Split(',').Length - 1];
    (row.FindControl("txtOrderId") as TextBox).Text = orderId.Split(',')[orderId.Split(',').Length - 1];
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        gvCustomers.DataSource = GetData("select top 10 * from Customers")
        gvCustomers.DataBind()
    End If
End Sub

Private Shared Function GetData(ByVal query As String) As DataTable
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(strConnString)
        Using cmd As SqlCommand = New SqlCommand()
            cmd.CommandText = query
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using ds As DataSet = New DataSet()
                    Dim dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Using
End Function

Protected Sub OnRowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
    If e.Row.RowType = DataControlRowType.DataRow Then
        Dim customerId As String = gvCustomers.DataKeys(e.Row.RowIndex).Value.ToString()
        Dim gvOrders As GridView = TryCast(e.Row.FindControl("gvOrders"), GridView)
        gvOrders.DataSource = GetData(String.Format("select top 3 * from Orders where CustomerId='{0}'", customerId))
        gvOrders.DataBind()
    End If
End Sub

Protected Sub Change(ByVal sender As Object, ByVal e As EventArgs)
    Dim row As GridViewRow = TryCast((TryCast(sender, TextBox)).NamingContainer, GridViewRow)
    Dim orderId As String = (TryCast(row.FindControl("txtOrderId"), TextBox)).Text
    Dim orderdate As String = (TryCast(row.FindControl("txtDate"), TextBox)).Text
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(strConnString)
        Using cmd As SqlCommand = New SqlCommand("UPDATE Orders SET OrderDate = @OrderDate WHERE OrderID = @OrderId", con)
            cmd.Parameters.AddWithValue("@OrderId", orderId.Split(","c)(orderId.Split(","c).Length - 1))
            cmd.Parameters.AddWithValue("@OrderDate", orderdate.Split(","c)(orderdate.Split(","c).Length - 1))
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using

    TryCast(row.FindControl("txtDate"), TextBox).Text = orderdate.Split(","c)(orderdate.Split(","c).Length - 1)
    TryCast(row.FindControl("txtOrderId"), TextBox).Text = orderId.Split(","c)(orderId.Split(","c).Length - 1)
End Sub

Screenshot