Problem with Paging and dynamic query generation

[RichJ]

Hi all,
I'm having a problem with paging in an Ajax RadGrid.
When NeedDataSource is fired, I am dynamicallly retrieving data depending on a list type (that I'm passing through to page).
As an example, if I need to show a list of sizes, I call the following:
1. dv (dataview) = getSizes()
2. getSizes() runs the following code:

Dim dbConn As String = [get conn string]
            Dim query As String = ""
            Dim selectClause As String = "SELECT ID, AccountType "
            Dim fromClause As String = "FROM tbl_WIP_DATA_AccountTypes WITH (NOLOCK) "
            Dim whereClause As String = "WHERE Active=1 AND Deleted=0"
            If FilterExpression <> "" Then FilterExpression = " AND AccountType LIKE '%" & CleanForSQL(FilterExpression) & "%'"
            whereClause &= " " & filterExpression
            query = selectClause & " " & fromClause & " " & whereClause & " ORDER BY AccountType ASC"
            Return WIP.Data.SqlHelper.ExecuteDataset(dbConn, CommandType.Text, query).Tables(0).DefaultView

Now this works fine!  Paging etc works as it should.  As you can see, it's dynamic t-sql.
BUT, if the getSizes() functions calls a second function get build a generic sql statement (based on a metadata system),
Radgrid displays the records correctly, but refuses to page.
I've tested the resulting queries and they are identical.

Does anyone have any suggestions why this would not work?!

Cheers in advance,
Rich

 

[Radoslav]

Hi Vaticnz,

Could you please try setting the AllowCustomPaging and VirtualItemCount properties as it described in the following online documentation article and let me know if the issue still persists:
http://www.telerik.com/help/aspnet-ajax/grid-custom-paging.html

Looking forward for your reply.

Best wishes,
Radoslav
the Telerik team

[RichJ]

Thanks Radoslav, that solved the problem.

I'm not sure I understand why the problem occurred?  Surely the grid doesn't care about the query and how it's created, and the query returned was identical?

Cheers
Rich

[Radoslav]

Hello Vaticnz,

In your case you use RadGrid with custom paging and fetch only a fixed number of records and perform operations on this limited set of data. RadGrid allows such data manipulation through its integrated custom paging mechanism. To use this mechanism you need to set AllowCustomPaging to true and provide the total number of records into VirtualItemCount. This allows the pager item to correctly represent the size of the pages you implement relative to the total number of records possible. Then you could bind the RadGrid using NeedDataSource event and pass it fixed number of records. When you enable custom paging, RadGrid maintains the pager buttons, updating the pager item in response to user actions, and other presentation specifics. You need only provide the logic of fetching the desired set of records in the NeedDataSource event handler.

I hope this helps.

Best wishes,
Radoslav
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.