Data Source is not supported server side pagination

Question

I am working on project which has hundred thousands of records coming from database. I have to show this in DevExpress grid. Well default behavior of grid is to load all records at once and it applies pagination on client end. The problem i am having is that the page takes lots of time while loading. To stop this i am going to use server side pagination at devExpress grid. But I am getting error : "The data source does not support server-side data paging"

My grid is "gvList" and i am setting its property as :

gvList.DataSourceForceStandardPaging = True

And then

Dim cmd As New SqlCommand
Dim ds As New DataSet
Dim da As SqlDataAdapter
Dim dbConn As New SqlConnection(conStr)
cmd.CommandType = CommandType.Text
cmd.CommandText = strSQL     'contains SQL string
cmd.Connection = dbConn      'contains connection object
da = New SqlDataAdapter(cmd)
da.Fill(ds, tbl)
gvList.DataSource = ds
gvList.DataBind()

Can any one please tell me where i am going wrong ?

Thanks.. Anjum Dhamial

http://stackoverflow.com/questions/1661292/the-data-source-does-not-support-server-side-data-paging — Magnus, Jun 30 '14 at 08:36
@Magnus Burton , Well at link you sent LINQ query is being used where as i am using simple query. — Anjum, Jun 30 '14 at 08:39
Sure but it follows the same principal. Use your query but add a `Limit 0,10` to show the first 10 rows. `Limit 10,10` to show rows 11 to 20 — Magnus, Jun 30 '14 at 08:41
ok, I added Limit 0,10 to my query strong and i end up with error. Can you please let me know with any example ? — Anjum, Jun 30 '14 at 08:55
I used `Top 10` but i am getting same issue `The data source does not support server-side data paging` — Anjum, Jul 02 '14 at 05:52

score 0 · Answer 1 · answered Jun 30 '14 at 09:43

ASPxGridView supports three different data binding modes:

1) common binding when all data is fetched to the web server and processed by the ASPxGridView itself; 2) server side sorting and paging. This functionality is turned on by activating the ASPxGridView's DataSourceForceStandardPaging property; In this case, you need to use ObjectDataSource since SQLDataSource does not support server side pagination. 3) real server mode when almost grid data related calculations (like grouping, summary) are implemented on the DB server. The link above contains some useful information regarding this mode.

So, the easiest solution to this problem is to use my second option. The third option is much more powerful but will require some additional work.

Thanks for answer, but IS DataSet ObjectDataSource or SQLDataSource ? — Anjum, Jul 01 '14 at 04:10
DataSet is neither ObjectDataSource nor SQLDataSource. These are different essences which are used particularly in ASP.NET. If you need to implement paging in ASPxGridView - use one of the options I provided in the answer. — platon, Jul 01 '14 at 05:17

score 0 · Answer 2 · answered Jul 23 '14 at 13:51

Use Custom Pagination aproach.

Hence, you should associate the grid to a datasource. It can be a objectdatasource or another. In datasource, some parameters should be send to class which contains methods for selecting and counting.

An example:

Web form

<asp:ObjectDataSource ID="ds"
    EnablePaging="True"
    TypeName="Namespace.to.Service"
    runat="server"
    SelectMethod="FindAll"
    SelectCountMethod="FindAllCount"
    StartRowIndexParameterName="startRow">
    <SelectParameters>
        <asp:Parameter Name="maximumRows" Type="Int32" />
        <asp:Parameter Name="startRow" Type="Int32" />
    </SelectParameters>
</asp:ObjectDataSource>

<asp:GridView  ID="grd" runat="server" AutoGenerateColumns="False" 
    DataSourceID="ds"
    AllowPaging="True">
<%--  add columns here --%>
</asp:GridView>

If you need pass extra parameters in datasource from some control, you can add to SelectParameters

<asp:ControlParameter ControlID="txtID" Name="parameterName" PropertyName="Text" Type="String" />

In Namespace.to.Service class, put methods as below:

public IList<MyObject> FindAll(int maximumRows, int startRow) { ... }
public int FindAllCount(int maximumRows, int startRow) { ... }

If extra parameters in datasource was used, simply add them to methods too:

public IList<MyObject> FindAll(int maximumRows, int startRow, string parameterName) 
{ 
   /*
      fetch result from database or something;
      'maximumRows' is pageSize
      'startRow' is first result to fetch 'maximumRows' records from database
   */ 
}
public int FindAllCount(int maximumRows, int startRow, string parameterName)
{
   /* 
     take amount of data for. It will be used to create grid footer for pagination;
     parameters are like above.
   */
}

I guess it's all you need.

Data Source is not supported server side pagination

2 Answers2