How to save dynamically created GridView data inside user control (.ascx) to Database using jQuery in ASP.Net

Last Reply on Jul 26, 2017 09:56 AM By dharmendr

Posted on Jul 26, 2017 03:33 AM

Hai all,

SQL:

CREATE TABLE nagaraju60
(
    Id INT IDENTITY,
    [1] NVARCHAR(100) NULL,
    [2] NVARCHAR(100) NULL,
    [3] NVARCHAR(100) NULL,
    [4] NVARCHAR(100) NULL,
    [5] NVARCHAR(100) NULL,
    [6] NVARCHAR(100) NULL,
    [7] NVARCHAR(100) NULL,
    [8] NVARCHAR(100) NULL,
    [9] NVARCHAR(100) NULL,
    [10] NVARCHAR(100) NULL
)

HTML:

 

<div>
    <table border="0" cellpadding="0" cellspacing="0">
        <tr>
            <td>
                Columns
            </td>
            <td>
                <asp:TextBox ID="txtColumns" runat="server" Text="2" />
            </td>
        </tr>
        <tr>
            <td>
                Rows
            </td>
            <td>
                <asp:TextBox ID="txtRows" runat="server" Text="2" />
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:Button ID="btnSubmit" OnClick="GenerateGridView" Text="Submit" runat="server" />
            </td>
        </tr>
    </table>
    <br />
    <asp:GridView ID="gvData" runat="server" />
    <input type="hidden" name="hfInsertQuery" id="hfInsertQuery" />
    <br />
    <asp:Button Text="Save" runat="server" ID="btnSave" />
    <br />
    <br />
    <asp:GridView ID="gvRecords" runat="server" EmptyDataText="No record present" />
</div>
<div>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $('#btnSave').on("click", function () {
                var query = "";
                var columnQuery = "INSERT INTO nagaraju60(";
                var trs = $('#gvData').find('tr:has(:not(th))');
                var columnCount = $($(trs)[0]).find('td').length;
                for (var i = 1; i <= columnCount; i++) {
                    columnQuery += "[" + i + "],";
                }
                columnQuery = columnQuery.substring(0, columnQuery.length - 1);
                columnQuery += ") VALUES (";
 
                for (var i = 0; i < $(trs).length; i++) {
                    var tds = $(trs)[i];
                    var valueQuery = "";
                    $(tds).find('td').each(function () {
                        valueQuery += "'" + $(this).find('[id*=txtDynamic]').val() + "',";
                    });
                    valueQuery = valueQuery.substring(0, valueQuery.length - 1);
                    query += columnQuery + valueQuery + ") \n";
                }
                $.ajax({
                    type: "POST",
                    url: "CS.aspx/InsertCustomers",
                    data: '{query: "' + query + '" }',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        window.location.href = window.location.href;
                    }
                });
                return false;
            });
        });
    </script>
</div>

C#:

 

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(constr))
        {
            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM nagaraju60"))
            {
                using (System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        gvRecords.DataSource = dt;
                        gvRecords.DataBind();
                    }
                }
            }
        }
    }
}
 
protected void GenerateGridView(object sender, EventArgs e)
{
    gvData.Columns.Clear();
    DataTable dt = new DataTable();
    int cols = Convert.ToInt32(txtColumns.Text.Trim());
    int rows = Convert.ToInt32(txtRows.Text.Trim());
    for (int i = 0; i < cols; i++)
    {
        TemplateField field = new TemplateField();
        field.HeaderText = "Column" + (i + 1).ToString();
        field.ItemTemplate = new GridViewTemplate("Column" + (i + 1).ToString(), (i + 1).ToString());
        gvData.Columns.Add(field);
    }
 
    for (int i = 0; i < rows; i++)
    {
        dt.Rows.Add();
    }
    gvData.DataSource = dt;
    gvData.DataBind();
}
 
public class GridViewTemplate : ITemplate
{
    private string columnNameBinding;
 
    public GridViewTemplate(string colname, string colNameBinding)
    {
        columnNameBinding = colNameBinding;
    }
 
    public void InstantiateIn(System.Web.UI.Control container)
    {
        TextBox tb = new TextBox();
        tb.ID = "txtDynamic" + columnNameBinding;
        container.Controls.Add(tb);
    }
}
 
[System.Web.Services.WebMethod]
public static void InsertCustomers(string query)
{
    string constr = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(constr))
    {
        using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query))
        {
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

By following above code i am getting dynamic gridview records and saved in database.

Reference link :

http://www.aspforums.net/Threads/705028/How-to-save-dynamic-gridview-data-in-aspnet/?p=2

if i am using same code usercontrol page (.ascx) gridview rows are not found at below line of code.

 

 var trs = $('#gvData').find('tr:has(:not(th))');
                var columnCount = $($(trs)[0]).find('td').length;

 

how to get gridview "trs" .

please help me

Thanks in advance

Posted on Jul 26, 2017 09:56 AM

Hi nagaraju60,

You can't call a method kept inside a usercontrol through jquery. Because .ascx controls doesn't represent real url that can be accessed from a client machine. They're purely server side meant to embed in other pages.

So you need to write the ajax code and the web method in the page itself.

Refer the below sample code

DynamicGrid.ascx

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="DynamicGrid.ascx.cs" Inherits="DynamicGrid" %>
<div>
    <table border="0" cellpadding="0" cellspacing="0">
        <tr>
            <td>
                Columns
            </td>
            <td>
                <asp:TextBox ID="txtColumns" runat="server" Text="2" />
            </td>
        </tr>
        <tr>
            <td>
                Rows
            </td>
            <td>
                <asp:TextBox ID="txtRows" runat="server" Text="2" />
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:Button ID="btnSubmit" OnClick="GenerateGridView" Text="Submit" runat="server" />
            </td>
        </tr>
    </table>
    <br />
    <asp:GridView ID="gvData" runat="server" />
    <input type="hidden" name="hfInsertQuery" id="hfInsertQuery" />
    <br />
    <asp:Button Text="Save" runat="server" ID="btnSave" />
    <br />
    <br />
    <asp:GridView ID="gvRecords" runat="server" EmptyDataText="No record present" />
</div>

DynamicGrid.ascx.cs

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(constr))
        {
            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM nagaraju60"))
            {
                using (System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        gvRecords.DataSource = dt;
                        gvRecords.DataBind();
                    }
                }
            }
        }
    }
}

protected void GenerateGridView(object sender, EventArgs e)
{
    gvData.Columns.Clear();
    DataTable dt = new DataTable();
    int cols = Convert.ToInt32(txtColumns.Text.Trim());
    int rows = Convert.ToInt32(txtRows.Text.Trim());
    for (int i = 0; i < cols; i++)
    {
        TemplateField field = new TemplateField();
        field.HeaderText = "Column" + (i + 1).ToString();
        field.ItemTemplate = new GridViewTemplate("Column" + (i + 1).ToString(), (i + 1).ToString());
        gvData.Columns.Add(field);
    }

    for (int i = 0; i < rows; i++)
    {
        dt.Rows.Add();
    }
    gvData.DataSource = dt;
    gvData.DataBind();
}

public class GridViewTemplate : ITemplate
{
    private string columnNameBinding;

    public GridViewTemplate(string colname, string colNameBinding)
    {
        columnNameBinding = colNameBinding;
    }

    public void InstantiateIn(System.Web.UI.Control container)
    {
        TextBox tb = new TextBox();
        tb.ID = "txtDynamic" + columnNameBinding;
        container.Controls.Add(tb);
    }
}    

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register TagPrefix="uc" Src="~/DynamicGrid.ascx" TagName="Grid" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $('[id*=btnSave]').on("click", function () {
                var query = "";
                var columnQuery = "INSERT INTO nagaraju60(";
                var trs = $('[id*=gvData]').find('tr:has(:not(th))');
                var columnCount = $($(trs)[0]).find('td').length;
                for (var i = 1; i <= columnCount; i++) {
                    columnQuery += "[" + i + "],";
                }
                columnQuery = columnQuery.substring(0, columnQuery.length - 1);
                columnQuery += ") VALUES (";

                for (var i = 0; i < $(trs).length; i++) {
                    var tds = $(trs)[i];
                    var valueQuery = "";
                    $(tds).find('td').each(function () {
                        valueQuery += "'" + $(this).find('[id*=txtDynamic]').val() + "',";
                    });
                    valueQuery = valueQuery.substring(0, valueQuery.length - 1);
                    query += columnQuery + valueQuery + ") \n";
                }
                $.ajax({
                    type: "POST",
                    url: "Default.aspx/InsertCustomers",
                    data: '{query: "' + query + '" }',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        window.location.href = window.location.href;
                    },
                    error: function (response) {
                        alert(response.d);
                    }
                });
                return false;
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <uc:Grid ID="ucGrid" runat="server" />
    </div>
    </form>
</body>
</html>

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    [System.Web.Services.WebMethod]
    public static void InsertCustomers(string query)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(constr))
        {
            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query))
            {
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

Screenshot