Lazy Loading not working on setting scrollable: {virtual: true} in Kendo Grid

[Bharath]

I am a beginner working on Kendo Grid. I wanted to load the data on demand in the grid, like :

- I set the page size to 50, when a user scrolls down to 50 rows, the
grid should retrieve next 50 rows from database and display it on
demand.To do this, I changed "scrollable: true" to "scrollable: {virtual: true}". But this disables scrolling in the grid.

- I am stuck, let me know if anyone has faced the same issue and found any solution for it.
Here is my code

=================================================

var managecustomerGrid = $("#customerGrid").kendoGrid({
     dataSource: {
        schema: {
         data: "results",
         total : "totalRecords",
         model: {
            id: "SRNUMBER",
            fields: {
              SRNUMBER : {type: 'number'},
              CUSTOMERNAME : {type: 'string'},
              DATEPAID : {type: 'string'}
              }
           }
         },
         serverPaging: true,
         serverSorting: true,
         serverFiltering: true,
         pageSize: 20,
         batch: false,
         transport: {
             read: {
                 type: "POST",
                 url: "/customer/customer.cfc",
                 dataType: "json",
                 error: function (xhr, error) {
                          alert('Error In Getting Customer Information.');
                     }
             },
             parameterMap: function(options, type) {
                  return {
                        ntPageNumber: options.page,
                        ntRowLimit: options.pageSize,
                        ntskip: options.skip,
                        vcSortOrder: JSON.stringify(options.sort),
                        vcFilterCondition: JSON.stringify(options.filter)
             }
         }
     }
},
toolbar: kendo.template($("#template").html()),
height: 600,
scrollable: {
      virtual: true
   },
filterable: {
       operators: {
             string: {
                  contains: "Contains",
                  startswith: "Starts with",
                  endswith: "Ends with",
                  eq: "Is equal to",
                  doesnotcontain: "Doesn't contain"
             }
      }
},
sortable: true,
columns: [
       { field: "SRNUMBER", title: "SR No.", width: "80px", template: "<span id='#=SRNUMBER#'>#=SRNUMBER#</span>"},
       { field: "CUSTOMERNAME", title: "Customer Name", width: "110px"},
       { field: "DATEPAID", title: "Date", width: "110px"},
       { command: ["edit","detail","cancel"], title: "&nbsp;", title: "Actions", width: "130px", filterable: false, sortable: false}
    ]
});

========================================================

Thanks for your help.

[Dimo]

Hi Bharath,

The Grid declaration looks OK. Are you creating the Grid while it is invisible?

http://docs.kendoui.com/getting-started/web/grid/walkthrough#initializing-the-grid-inside-a-hidden-container

If your scenario is different, please provide a demo for further inspection.

Regards,
Dimo
Telerik

[Bharath]

Hi Dimo,

No, we are not creating the Grid while its invisible, neither we have used any hidden fields. Similar code is being used in other grids as well and they all work fine.

Can there be any possibility of error happening while data retrieval from DB?

Thanks,
Bharath

[Dimo]

Hi Bharath,

Surely, if no data is retrieved due to an error, no scrollbar will be created. You can check your browser console.

Regards,
Dimo
Telerik

[Bharath]

Hi Dimo,

The data is being retrieved perfectly. Please find the details below

- As in the code, exactly 20 rows is being retrieved(When I check in console)
- As I have set the size of the Grid to 600, in the grid, only 12 is displayed,

Expected scenario:  When the user scrolls down, remaining 8 records should become visible and next 20 records should be retrieved and the procedure continues.

Actual scenario happening: The virtual scroll bar is disabled as in the screenshot attached.

Thanks,
Bharath

[Dimo]

Hi Bharath,

Here is a demo, based on your code, which works as expected. Please compare with your project.

http://jsfiddle.net/gX7MX/

If you need further assistance, then modify the above demo, so that the discussed problem is exhibited, and send it back.

Regards,
Dimo
Telerik

[Bharath]

Hi Dimo,

The issue was with the total record count, Total record count was not retrieved properly as there was an issue with the SP.
We sorted it and Now it is working fine.

Thanks for all your support,
Bharath

[Matt]

Just to add some additional clarity to Bharath's conclusion, if you are using virtual scrolling mode, your data source schema should be expecting a 'total' result from the remote data provider.  The kendo grid uses this value to calculate a height for the virtual scrollbar (it gets appended to the DOM inside the k-grid-content element as div.k-scrollbar-vertical > div).  The grid calculates its virtual scroll height as: total * row-height + scrollbar-height.  So, if your data response returns 0 for 'total', your value will be 17px and you won't see a scroll bar.

[Natasa]

Hey, but what is the point of virtual scrolling If I need to provide TOTAL value to grid (to provide total value I need to run complete query to see how many rows there are).

Virtual scroll should help us by fetching parts of data (in my case by setting a LIMIT <from>, <to> in SQL query). So if I need to again run query to fetch the TOTAL rows, this is not most effective thing. The initial idea was to use virtual scroll to fetch parts and have quick return of the data to the client.

 

Am I missing something? Any help appreciated :)

[Natasa]

And is it possible to have virtual or endless scroll without passing the TOTAL, cause this means I will need to also run query that returns all results from db?? (they initial query was slow cause large amount of data , this is why I decided to go with virtual scroll initially.)

[Dimo]

Hi Natasa,

You can use the COUNT function in SQL to find out the total number of data items. There is no need to execute a query that will return all actual rows.

It is absolutely required to provide a TOTAL to the Kendo UI DataSource, otherwise the virtual scrollbar cannot be rendered correctly and the user will not be able to access all items by scrolling.

Regards,
Dimo
Progress Telerik

[Matt]

Depending on which SQL engine you're using, most of them offer a way to achieve this without executing two full scans. 

In MySQL, you can use the CALC_FOUND_ROWS option.  Essentially, when setting that option on your first query, the db engine will remember the total number of rows if found before taking the slice for your LIMIT.  Then, after your query you execute another query to SELECT FOUND_ROWS() and it simply retrieves that stored number without re-running the query.  For more info, see: https://stackoverflow.com/questions/12887266/get-total-number-of-rows-when-using-limit

I believe MS SQL Server has a similar concept with OFFSET/NEXT and COUNT OVER, though I've read performance isn't great over large datasets.

[Natasa]

Yes! That is exactly what I did yesterday after posting a question on this thread. But thanks anyway for the response! You guys rock. :)