Bind ASP.Net GridView from SQL Server Database using jQuery AJAX

Bharati

SOLVED CLOSED ACTIVE ANSWERED
User: Bharati
Posted: on Apr 05, 2018 09:09 PM
Forum: Data Controls
Answer: 2
Views: 2561
Sample Code: Download

Hi All

I am refering below link to display data in the gridview, however unable to do so.

Link:https://www.aspsnippets.com/Articles/Select-Insert-Edit-Update-Delete-using-Web-Service-in-ASPNet.aspx

Please guide me..thanks in advance.

Below is the code I am using:

Default.asp.cs:

    [WebMethod]
    public static string GetItems()
    {
        string query = "SELECT [Sno] ";
        query += ",[UMC_No] ";
        query += ",[New_UMC] ";
        query += ",[Item_Desc] ";
        query += ",[Part_No] ";
        query += ",[Make] ";
        query += ",[Area] ";
        query += ",[Qty] ";
        query += ",[Location] ";
        query += ",[Sub_Location] ";
        query += ",[Remarks] ";
        query += "FROM [DB_Store].[dbo].[Store$]";
        SqlCommand cmd = new SqlCommand(query);
        string cs = "Data Source=JLD3WKS03294\\SQLEXPRESS;Initial Catalog=DB_Store;Persist Security Info=True;User ID=sa;Password=92080;";
        using (SqlConnection con = new SqlConnection(cs))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);
                    return ds.GetXml();
                }
            }
        }
    }

Default.asp:

    <script type="text/javascript">
        $(function () {
            $.ajax({
                type: "POST",
                url: "Default.aspx/GetItems",
                data: '{}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: OnSuccess
            });
        });

        function OnSuccess(response) {
            var xmlDoc = $.parseXML(response.d);
            var xml = $(xmlDoc);
            var Items = xml.find("Table");
            var row = $("[id*=gvItems] tr:last-child").clone(true);
            $("[id*=gvItems] tr").not($("[id*=gvItems] tr:first-child")).remove();
            $.each(Items, function () {
                var Item = $(this);
                AppendRow(row, $(this).find("Sno").text(), $(this).find("UMC_No").text(), $(this).find("New_UMC").text(), $(this).find("Item_Desc").text(), $(this).find("Part_No").text(), $(this).find("Make").text(), $(this).find("Area").text(), $(this).find("Qty").text(), $(this).find("Location").text(), $(this).find("Sub_Location").text(), $(this).find("Remarks").text())
                row = $("[id*=gvItems] tr:last-child").clone(true);
            });
        }
        function AppendRow(row, sno, UMC_no, new_UMC, item_desc, part_no, make, area, qty, location, sub_location, remarks) {
            $(".Sno", row).find("span").html(sno);
            $(".UMC_No", row).find("span").html(UMC_no);

            $(".New_UMC", row).find("span").html(new_UMC);
            $(".New_UMC", row).find("input").val(new_UMC);

            $(".Item_Desc", row).find("span").html(item_desc);
            $(".Item_Desc", row).find("input").val(item_desc);

            $(".Part_No", row).find("span").html(part_no);
            $(".Part_No", row).find("input").val(part_no);

            $(".Make", row).find("span").html(make);
            $(".Make", row).find("input").val(make);

            $(".Area", row).find("span").html(area);
            $(".Area", row).find("input").val(area);

            $(".Qty", row).find("span").html(qty);
            $(".Qty", row).find("input").val(qty);

            $(".Location", row).find("span").html(location);
            $(".Location", row).find("input").val(location);

            $(".Sub_Location", row).find("span").html(sub_location);
            $(".Sub_Location", row).find("input").val(sub_location);

            $(".Remarks", row).find("span").html(remarks);
            $(".Remarks", row).find("input").val(remarks);

            $("[id*=gvItems]").append(row);
        }
    </script>


    <asp:GridView ID="gvItems" runat="server" AutoGenerateColumns="False" BackColor="White"
            BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3">
            <Columns>
                <asp:TemplateField HeaderText="">
                    <ItemTemplate>
                        <asp:LinkButton ID="Edit" runat="server" Text="Edit" CssClass="Edit"></asp:LinkButton>
                        <asp:LinkButton ID="Update" runat="server" Text="Update" CssClass="Edit" Style="display: none;"></asp:LinkButton>
                        <asp:LinkButton ID="Cancel" runat="server" Text="Cancel" CssClass="Edit" Style="display: none;"></asp:LinkButton>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="SNo" ItemStyle-CssClass="Sno">
                    <ItemTemplate>
                        <asp:Label runat="server" Text='<%# Eval("Sno") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle CssClass="Sno"></ItemStyle>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="UMC No" ItemStyle-CssClass="UMC_No">
                    <ItemTemplate>
                        <asp:Label runat="server" Text='<%# Eval("UMC_No") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle CssClass="UMC_No"></ItemStyle>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="New UMC" ItemStyle-CssClass="New_UMC">
                    <ItemTemplate>
                        <asp:Label runat="server" Text='<%# Eval("New_UMC") %>'></asp:Label>
                        <asp:TextBox runat="server" Text='<%# Eval("New_UMC") %>' CssClass="textbox"></asp:TextBox>
                    </ItemTemplate>
                    <ItemStyle CssClass="New_UMC"></ItemStyle>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Item Desc" ItemStyle-CssClass="Item_Desc">
                    <ItemTemplate>
                        <asp:Label runat="server" Text='<%# Eval("Item_Desc") %>'></asp:Label>
                        <asp:TextBox runat="server" Text='<%# Eval("Item_Desc") %>' CssClass="textbox"></asp:TextBox>
                    </ItemTemplate>
                    <ItemStyle CssClass="Item_Desc"></ItemStyle>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Part No" ItemStyle-CssClass="Part_No">
                    <ItemTemplate>
                        <asp:Label runat="server" Text='<%# Eval("Part_No") %>'></asp:Label>
                        <asp:TextBox runat="server" Text='<%# Eval("Part_No") %>' CssClass="textbox"></asp:TextBox>
                    </ItemTemplate>
                    <ItemStyle CssClass="Part_No"></ItemStyle>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Make" ItemStyle-CssClass="Make">
                    <ItemTemplate>
                        <asp:Label runat="server" Text='<%# Eval("Make") %>'></asp:Label>
                        <asp:TextBox runat="server" Text='<%# Eval("Make") %>' CssClass="textbox"></asp:TextBox>
                    </ItemTemplate>
                    <ItemStyle CssClass="Make"></ItemStyle>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Area" ItemStyle-CssClass="Area">
                    <ItemTemplate>
                        <asp:Label runat="server" Text='<%# Eval("Area") %>'></asp:Label>
                        <asp:TextBox runat="server" Text='<%# Eval("Area") %>' CssClass="textbox"></asp:TextBox>
                    </ItemTemplate>
                    <ItemStyle CssClass="Area"></ItemStyle>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Qty" ItemStyle-CssClass="Qty">
                    <ItemTemplate>
                        <asp:Label runat="server" Text='<%# Eval("Qty") %>'></asp:Label>
                        <asp:TextBox runat="server" Text='<%# Eval("Qty") %>' CssClass="textbox"></asp:TextBox>
                    </ItemTemplate>
                    <ItemStyle CssClass="Qty"></ItemStyle>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Location" ItemStyle-CssClass="Location">
                    <ItemTemplate>
                        <asp:Label runat="server" Text='<%# Eval("Location") %>'></asp:Label>
                        <asp:TextBox runat="server" Text='<%# Eval("Location") %>' CssClass="textbox"></asp:TextBox>
                    </ItemTemplate>
                    <ItemStyle CssClass="Location"></ItemStyle>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Sub Location" ItemStyle-CssClass="Sub_Location">
                    <ItemTemplate>
                        <asp:Label runat="server" Text='<%# Eval("Sub_Location") %>'></asp:Label>
                        <asp:TextBox runat="server" Text='<%# Eval("Sub_Location") %>' CssClass="textbox"></asp:TextBox>
                    </ItemTemplate>
                    <ItemStyle CssClass="Sub_Location"></ItemStyle>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Remarks" ItemStyle-CssClass="Remarks">
                    <ItemTemplate>
                        <asp:Label runat="server" Text='<%# Eval("Remarks") %>'></asp:Label>
                        <asp:TextBox runat="server" Text='<%# Eval("Remarks") %>' CssClass="textbox"></asp:TextBox>
                    </ItemTemplate>
                    <ItemStyle CssClass="Remarks"></ItemStyle>
                </asp:TemplateField>
            </Columns>     
            <FooterStyle BackColor="White" ForeColor="#000066" />
            <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
            <RowStyle ForeColor="#000066" />
            <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#007DBB" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#00547E" />
        </asp:GridView>

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 5

Reply
ANSWER
User: dharmendr
Replied: on Apr 10, 2018 06:08 AM
Report

Hi Bharati,

Check this example. Now please take its reference and correct your code.

HTML

<asp:GridView ID="gvItems" runat="server" AutoGenerateColumns="False">
    <Columns>
        <asp:TemplateField HeaderText="">
            <ItemTemplate>
                <asp:LinkButton ID="Edit" runat="server" Text="Edit" CssClass="Edit"></asp:LinkButton>
                <asp:LinkButton ID="Update" runat="server" Text="Update" CssClass="Edit" Style="display: none;"></asp:LinkButton>
                <asp:LinkButton ID="Cancel" runat="server" Text="Cancel" CssClass="Edit" Style="display: none;"></asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="SNo" ItemStyle-CssClass="Sno">
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" Text='<%# Eval("Sno") %>'></asp:Label>
            </ItemTemplate>
            <ItemStyle CssClass="Sno"></ItemStyle>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="UMC No" ItemStyle-CssClass="UMC_No">
            <ItemTemplate>
                <asp:Label ID="Label2" runat="server" Text='<%# Eval("UMC_No") %>'></asp:Label>
            </ItemTemplate>
            <ItemStyle CssClass="UMC_No"></ItemStyle>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="New UMC" ItemStyle-CssClass="New_UMC">
            <ItemTemplate>
                <asp:Label ID="Label3" runat="server" Text='<%# Eval("New_UMC") %>'></asp:Label>
                <asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("New_UMC") %>' CssClass="textbox"></asp:TextBox>
            </ItemTemplate>
            <ItemStyle CssClass="New_UMC"></ItemStyle>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Item Desc" ItemStyle-CssClass="Item_Desc">
            <ItemTemplate>
                <asp:Label ID="Label4" runat="server" Text='<%# Eval("Item_Desc") %>'></asp:Label>
                <asp:TextBox ID="TextBox2" runat="server" Text='<%# Eval("Item_Desc") %>' CssClass="textbox"></asp:TextBox>
            </ItemTemplate>
            <ItemStyle CssClass="Item_Desc"></ItemStyle>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Part No" ItemStyle-CssClass="Part_No">
            <ItemTemplate>
                <asp:Label ID="Label5" runat="server" Text='<%# Eval("Part_No") %>'></asp:Label>
                <asp:TextBox ID="TextBox3" runat="server" Text='<%# Eval("Part_No") %>' CssClass="textbox"></asp:TextBox>
            </ItemTemplate>
            <ItemStyle CssClass="Part_No"></ItemStyle>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Make" ItemStyle-CssClass="Make">
            <ItemTemplate>
                <asp:Label ID="Label6" runat="server" Text='<%# Eval("Make") %>'></asp:Label>
                <asp:TextBox ID="TextBox4" runat="server" Text='<%# Eval("Make") %>' CssClass="textbox"></asp:TextBox>
            </ItemTemplate>
            <ItemStyle CssClass="Make"></ItemStyle>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Area" ItemStyle-CssClass="Area">
            <ItemTemplate>
                <asp:Label ID="Label7" runat="server" Text='<%# Eval("Area") %>'></asp:Label>
                <asp:TextBox ID="TextBox5" runat="server" Text='<%# Eval("Area") %>' CssClass="textbox"></asp:TextBox>
            </ItemTemplate>
            <ItemStyle CssClass="Area"></ItemStyle>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Qty" ItemStyle-CssClass="Qty">
            <ItemTemplate>
                <asp:Label ID="Label8" runat="server" Text='<%# Eval("Qty") %>'></asp:Label>
                <asp:TextBox ID="TextBox6" runat="server" Text='<%# Eval("Qty") %>' CssClass="textbox"></asp:TextBox>
            </ItemTemplate>
            <ItemStyle CssClass="Qty"></ItemStyle>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Location" ItemStyle-CssClass="Location">
            <ItemTemplate>
                <asp:Label ID="Label9" runat="server" Text='<%# Eval("Location") %>'></asp:Label>
                <asp:TextBox ID="TextBox7" runat="server" Text='<%# Eval("Location") %>' CssClass="textbox"></asp:TextBox>
            </ItemTemplate>
            <ItemStyle CssClass="Location"></ItemStyle>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Sub Location" ItemStyle-CssClass="Sub_Location">
            <ItemTemplate>
                <asp:Label ID="Label10" runat="server" Text='<%# Eval("Sub_Location") %>'></asp:Label>
                <asp:TextBox ID="TextBox8" runat="server" Text='<%# Eval("Sub_Location") %>' CssClass="textbox"></asp:TextBox>
            </ItemTemplate>
            <ItemStyle CssClass="Sub_Location"></ItemStyle>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Remarks" ItemStyle-CssClass="Remarks">
            <ItemTemplate>
                <asp:Label ID="Label11" runat="server" Text='<%# Eval("Remarks") %>'></asp:Label>
                <asp:TextBox ID="TextBox9" runat="server" Text='<%# Eval("Remarks") %>' CssClass="textbox"></asp:TextBox>
            </ItemTemplate>
            <ItemStyle CssClass="Remarks"></ItemStyle>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
    $(function () {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetItems",
            data: '{}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: OnSuccess,
            error: function (r) {
                alert(r.responseText);
            }
        });
    });

    function OnSuccess(response) {
        var xmlDoc = $.parseXML(response.d);
        var xml = $(xmlDoc);
        var Items = xml.find("Table1");
        var row = $("[id*=gvItems] tr:last-child").clone(true);
        $("[id*=gvItems] tr").not($("[id*=gvItems] tr:first-child")).remove();
        $.each(Items, function () {
            var Item = $(this);
            AppendRow(row, $(this).find("Sno").text(), $(this).find("UMC_No").text(), $(this).find("New_UMC").text(), $(this).find("Item_Desc").text(), $(this).find("Part_No").text(), $(this).find("Make").text(), $(this).find("Area").text(), $(this).find("Qty").text(), $(this).find("Location").text(), $(this).find("Sub_Location").text(), $(this).find("Remarks").text())
            row = $("[id*=gvItems] tr:last-child").clone(true);
        });
    }
    function AppendRow(row, sno, UMC_no, new_UMC, item_desc, part_no, make, area, qty, location, sub_location, remarks) {
        $(".Sno", row).find("span").html(sno);
        $(".UMC_No", row).find("span").html(UMC_no);

        $(".New_UMC", row).find("span").html(new_UMC);
        $(".New_UMC", row).find("input").val(new_UMC);

        $(".Item_Desc", row).find("span").html(item_desc);
        $(".Item_Desc", row).find("input").val(item_desc);

        $(".Part_No", row).find("span").html(part_no);
        $(".Part_No", row).find("input").val(part_no);

        $(".Make", row).find("span").html(make);
        $(".Make", row).find("input").val(make);

        $(".Area", row).find("span").html(area);
        $(".Area", row).find("input").val(area);

        $(".Qty", row).find("span").html(qty);
        $(".Qty", row).find("input").val(qty);

        $(".Location", row).find("span").html(location);
        $(".Location", row).find("input").val(location);

        $(".Sub_Location", row).find("span").html(sub_location);
        $(".Sub_Location", row).find("input").val(sub_location);

        $(".Remarks", row).find("span").html(remarks);
        $(".Remarks", row).find("input").val(remarks);

        $("[id*=gvItems]").append(row);
    }
</script>

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindDummyRow();
    }
}

private void BindDummyRow()
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[11] { 
        new DataColumn("Sno"),new DataColumn("UMC_No"),new DataColumn("New_UMC"),new DataColumn("Item_Desc"),new DataColumn("Part_No"),
        new DataColumn("Make"),new DataColumn("Area"),new DataColumn("Qty"),new DataColumn("Location"),new DataColumn("Sub_Location"),
        new DataColumn("Remarks")});
    dt.Rows.Add();
    gvItems.DataSource = dt;
    gvItems.DataBind();
}

[WebMethod]
public static string GetItems()
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[11] { 
            new DataColumn("Sno"),new DataColumn("UMC_No"),new DataColumn("New_UMC"),new DataColumn("Item_Desc"),new DataColumn("Part_No"),
            new DataColumn("Make"),new DataColumn("Area"),new DataColumn("Qty"),new DataColumn("Location"),new DataColumn("Sub_Location"),
            new DataColumn("Remarks")});
    dt.Rows.Add("1", "UMC-1", "New_UMC-1", "New", "123", "Make 1", "500", "10", "Mumbai", "Andheri", "Better");
    using (DataSet ds = new DataSet())
    {
        ds.Tables.Add(dt);
        return ds.GetXml();
    }
}

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Bind ASP.Net GridView from SQL Server Database using jQuery AJAX

Company

Explore

Follow us

Contact Us