Binding Gridview with Jquery/Json Not getting more than 500 records

Question

1.80/5 (2 votes)

See more:

XML

Here is my code to bind gridview with json



Default.aspx



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



<!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 id="Head1" runat="server">

    <title></title>

    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>

    <script type="text/javascript">

        $(function () {

            $.ajax({

                type: "POST",

                url: "Default2.aspx/GetCustomers",

                data: '{}',

                contentType: "application/json; charset=utf-8",

                dataType: "json",

                success: OnSuccess,

                failure: function (response) {

                    alert(response.d);

                },

                error: function (response) {

                    alert(response.d);

                }

            });

        });



        function OnSuccess(response) {

            var xmlDoc = $.parseXML(response.d);

            var xml = $(xmlDoc);

            var customers = xml.find("Table");

            var row = $("[id*=gvCustomers] tr:last-child").clone(true);

            $("[id*=gvCustomers] tr").not($("[id*=gvCustomers] tr:first-child")).remove();

            $.each(customers, function () {

                var customer = $(this);

                $("td", row).eq(0).html($(this).find("ShipmentNo").text());

                $("td", row).eq(1).html($(this).find("Route").text());

                $("td", row).eq(2).html($(this).find("Ordernumber").text());

                $("td", row).eq(3).html($(this).find("TruckNo").text());

                $("td", row).eq(4).html($(this).find("Warehouse").text());

                $("td", row).eq(5).html($(this).find("CustomerName").text());

                $("td", row).eq(6).html($(this).find("StausCode").text());

                $("td", row).eq(7).html($(this).find("ETA").text());

                $("td", row).eq(8).html($(this).find("Delay").text());

                $("td", row).eq(9).html($(this).find("StatusColor").text());

                $("[id*=gvCustomers]").append(row);

                row = $("[id*=gvCustomers] tr:last-child").clone(true);

            });

        }

    </script>

</head>

<body>

    <form id="form1" runat="server">

    <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" Font-Names="Arial"

        Font-Size="10pt" RowStyle-BackColor="#A1DCF2" HeaderStyle-BackColor="#3AC0F2"

        HeaderStyle-ForeColor="White">

        <Columns>

            <asp:BoundField ItemStyle-Width="150px" DataField="TruckNo" HeaderText="TruckNo" />

            <asp:BoundField ItemStyle-Width="150px" DataField="Route" HeaderText="Route" />

            <asp:BoundField ItemStyle-Width="150px" DataField="ShipmentNo" HeaderText="ShipmentNo" />

            <asp:BoundField ItemStyle-Width="50px" DataField="Ordernumber" HeaderText="Ordernumber" />

            <asp:BoundField ItemStyle-Width="50px" DataField="Warehouse" HeaderText="Warehouse" />

            <asp:BoundField ItemStyle-Width="50px" DataField="CustomerName" HeaderText="CustomerName" />

            <asp:BoundField ItemStyle-Width="50px" DataField="StausCode" HeaderText="StausCode" />

            <asp:BoundField ItemStyle-Width="50px" DataField="ETA" HeaderText="ETA" />

            <asp:BoundField ItemStyle-Width="50px" DataField="Delay" HeaderText="Delay" />

            <asp:BoundField ItemStyle-Width="50px" DataField="StatusColor" HeaderText="StatusColor" />

        </Columns>

    </asp:GridView>

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

using System.Data;

using System.Web.Services;

using System.Data.SqlClient;

using System.Configuration;



public partial class Default2 : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            this.BindDummyRow();

        }

    }



    private void BindDummyRow()

    {

        DataTable dummy = new DataTable();

        dummy.Columns.Add("ShipmentNo");

        dummy.Columns.Add("Route");

        dummy.Columns.Add("Ordernumber");

        dummy.Columns.Add("TruckNo");

        dummy.Columns.Add("Warehouse");

        dummy.Columns.Add("CustomerName");

        dummy.Columns.Add("StausCode");

        dummy.Columns.Add("ETA");

        dummy.Columns.Add("Delay");

        dummy.Columns.Add("StatusColor");

        dummy.Rows.Add();

        gvCustomers.DataSource = dummy;

        gvCustomers.DataBind();

    }



    [WebMethod]

    public static string GetCustomers()

    {

        string query = "SP_TEST_BindDataToGridJQ";

        SqlCommand cmd = new SqlCommand(query);

        return GetData(cmd).GetXml();

    }

    private static DataSet GetData(SqlCommand cmd)

    {

        string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        using (SqlConnection con = new SqlConnection(strConnString))

        {

            using (SqlDataAdapter sda = new SqlDataAdapter())

            {

                cmd.Connection = con;

                sda.SelectCommand = cmd;

                cmd.CommandType = CommandType.StoredProcedure;

                using (DataSet ds = new DataSet())

                {

                    sda.Fill(ds);

                    return ds;



                }

            }

        }

    }



}





By using the above code ... i am not getting more than 500 records why?



SP:

ALTER PROCEDURE [dbo].[SP_TEST_BindDataToGridJQ] 

 -- Add the parameters for the stored procedure here
 
 AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

SELECT   ROW_NUMBER() OVER (ORDER BY dbo.tbl_pms_JobOrders.JobID) AS Sno,
			dbo.tbl_pms_JobOrders.Ordernumber, 
			dbo.tbl_pms_ShipmentInfo.ShipmentNo,
		    dbo.tbl_pms_Trucks.TruckNo,
		    dbo.tbl_pms_JobOrders.Route,
		    dbo.tbl_pms_Warehouses.Name as Warehouse,
		    dbo.tbl_pms_JobOrders.CustomerName,
		    dbo.tbl_pms_JobOrders_Trucks_Info.StausCode,
		    dbo.tbl_pms_JobOrders_Trucks_Info.ETA,
		    dbo.tbl_pms_JobOrders.JobID,
		    dbo.tbl_pms_JobOrders.Ordertype,
		    (case when tbl_pms_iComETAInfo.ETA IS NOT NULL then 
		    DATEDIFF(SECOND,dbo.tbl_pms_JobOrders_Trucks_Info.ETA,tbl_pms_iComETAInfo.ETA)
			else '' end)  [Delay],
			
		    (case when tbl_pms_JobOrders_Trucks_Info.StausCode=1 then 'Blue'
				when tbl_pms_JobOrders_Trucks_Info.StausCode=2 then 'Green'
				when tbl_pms_JobOrders_Trucks_Info.StausCode=3 then 'Red'
			    when tbl_pms_JobOrders_Trucks_Info.StausCode=4 then 'Yellow'
				else 'Black' end)StatusColor
		    
FROM       dbo.tbl_pms_JobOrders

INNER JOIN dbo.tbl_pms_JobOrders_Trucks_Info ON dbo.tbl_pms_JobOrders.JobID = dbo.tbl_pms_JobOrders_Trucks_Info.JobID 
INNER JOIN dbo.tbl_pms_ShipmentInfo ON dbo.tbl_pms_JobOrders_Trucks_Info.ShipmentID = dbo.tbl_pms_ShipmentInfo.ShipmentID 
INNER JOIN dbo.tbl_pms_Trucks ON dbo.tbl_pms_JobOrders_Trucks_Info.TruckID = dbo.tbl_pms_Trucks.TruckID 
INNER JOIN dbo.tbl_pms_Warehouses ON dbo.tbl_pms_JobOrders.WarehouseID = dbo.tbl_pms_Warehouses.WarehouseID
LEFT OUTER JOIN tbl_pms_iComETAInfo on tbl_pms_JobOrders.JobID = tbl_pms_iComETAInfo.JobID 

WHERE --(dbo.tbl_pms_JobOrders.Planneddate = '20140206') 
 dbo.tbl_pms_JobOrders.WarehouseID=225 
and dbo.tbl_pms_Trucks.TruckNo = 'W1227B' 
and dbo.tbl_pms_JobOrders_Trucks_Info.StausCode =2
and dbo.tbl_pms_ShipmentInfo.ShipmentNo = '1279411'
--or dbo.tbl_pms_Trucks.TruckNo = 'W1238B' 
and dbo.tbl_pms_Trucks.IsDeleted=0 and dbo.tbl_pms_Trucks.TruckID != 312
ORDER BY	dbo.tbl_pms_ShipmentInfo.ShipmentNo,
			dbo.tbl_pms_JobOrders_Trucks_Info.ETA,
			dbo.tbl_pms_JobOrders.Ordernumber
			
			END

Thanks Advance :) Please help me

Posted 6-May-14 17:41pm

DevilsCod

Updated 6-May-14 18:04pm

v2

Add a Solution

Comments

DamithSL 6-May-14 23:53pm

can you update the question with SP_TEST_BindDataToGridJQ code?

Laiju k 6-May-14 23:54pm

is there any message or something / simply you are getting no records after 500

DevilsCod 7-May-14 0:05am

Up to 500 records it is showing but when records are more than it shows empty table and A popup message comes in chrome "Undefined"?

Laiju k 7-May-14 0:25am

have you tried another browsers

DevilsCod 7-May-14 0:30am

ya .. same issue

DamithSL 7-May-14 0:31am

you need to find from where you get this error like failure or success function. And then which object is Undefined. put alerts on each case and check where is the error

Kornfeld Eliyahu Peter 7-May-14 3:06am

I never heard about a know bug to display more than 500 records in GridView (you may fill a bug report to Microsoft), but in any case I would advise you to add paging to your grid. Displaying too much information in one grid will slow down the browser and will disturb your users too...

DevilsCod 7-May-14 3:11am

Thanks for ur reply

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)