Error in Paging/Search with LBLGenDatasource and Telerik Grid

Posts   
 
    
eirizarry
User
Posts: 48
Joined: 30-Mar-2011
# Posted on: 14-Aug-2012 16:07:32   

I have an error that the search of the Telerik Grid does not work if I activate the paging in the LLBGenDatasource.

After debugging the Datasource and the grid here is what happening. When i enter a filter data like i want all record starting with "AF", this is what the datasource receive as parameters from the grid.

Generated Sql query:

Query: SELECT * FROM (SELECT a.*, rownum r___ FROM (SELECT "RENOVA"."SYS_ETHNICITY_MASTER"."DESCRIPTION" AS "Description", "RENOVA"."SYS_ETHNICITY_MASTER"."EEOC_REPORT" AS "EeocReport", "RENOVA"."SYS_ETHNICITY_MASTER"."ETHNICITY_ID" AS "EthnicityId", "RENOVA"."SYS_ETHNICITY_MASTER"."LAST_UPD_DATE" AS "LastUpdDate", "RENOVA"."SYS_ETHNICITY_MASTER"."LAST_UPD_TERMINAL" AS "LastUpdTerminal", "RENOVA"."SYS_ETHNICITY_MASTER"."LAST_UPD_USER_NAME" AS "LastUpdUserName", "RENOVA"."SYS_ETHNICITY_MASTER"."STATUS" AS "Status" FROM "RENOVA"."SYS_ETHNICITY_MASTER" WHERE ( ( ( UPPER("RENOVA"."SYS_ETHNICITY_MASTER"."DESCRIPTION") LIKE :p1))) ORDER BY "RENOVA"."SYS_ETHNICITY_MASTER"."DESCRIPTION" ASC) a WHERE rownum < :p5) WHERE r___ >=:p3

Parameter: :p1 : AnsiString. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: "%AF%". 
Parameter: :p5 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: -2147483648. 
Parameter: :p3 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

The value of parameter :p5 cause that the select does not return any record.  

Any Idea?

PD. If paging is enable on the grid but disable on the datasource it works fine. but we need the paging on the datasource because the huge amount of data on the table.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 14-Aug-2012 17:37:06   

llblgen pro version, runtime build nr?

Frans Bouma | Lead developer LLBLGen Pro
eirizarry
User
Posts: 48
Joined: 30-Mar-2011
# Posted on: 14-Aug-2012 19:30:20   

LLBLGen PRo version 3.1 Final

SD.LLBLGen.Pro.ORMSupportClasses.NET20 3.1.11.208

SD.LLBLGen.Pro.DQE.OracleMS.NET20 3.1.11.207

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 14-Aug-2012 19:38:55   

Please try with the latest release of v.3.1.

eirizarry
User
Posts: 48
Joined: 30-Mar-2011
# Posted on: 14-Aug-2012 19:53:08   

you means "3.5" or an update for the 3.1?

if an update, where i can download it?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 14-Aug-2012 19:57:02   

Well you can try the latest 3.5 release. But I was speaking about the latest release of v.3.1Go to llblgen . com and login with you cutomerId and password, then go to the downloads section.

eirizarry
User
Posts: 48
Joined: 30-Mar-2011
# Posted on: 14-Aug-2012 21:27:36   

same problem

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 15-Aug-2012 07:43:14   

Hi Elvin,

I can't reproduce it using RadControls Q1 2009 and latest LLBLGenPro v3.1 with SQLServer's Northwind. I have to check it whether the problem is with Oracle, which I think is the one DB you are using.

Are you using ODP.Net or MSOracle?

David Elizondo | LLBLGen Support Team
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 15-Aug-2012 08:41:26   

Hi Elvin. I reproduced it with MSOracle and latest LLBLGen v3.1

ASPX

<llblgenpro:LLBLGenProDataSource2 ID="_departmentsDS" runat="server" 
AdapterTypeName="HR.LLBL.MSOracle.Adapter.v31.DatabaseSpecific.DataAccessAdapter, HR.LLBL.MSOracle.Adapter.v31DBSpecific" 
DataContainerType="EntityCollection" 
EntityFactoryTypeName="HR.LLBL.MSOracle.Adapter.v31.FactoryClasses.DepartmentEntityFactory, HR.LLBL.MSOracle.Adapter.v31"
EnablePaging="true" EnableViewState="true">
</llblgenpro:LLBLGenProDataSource2>

<rad:RadGrid AutoGenerateColumns="false" ID="RadGrid1" DataSourceID="_departmentsDS" Width="96%" EnableAJAX="True"
    Skin="Outlook2007" AllowFilteringByColumn="True" AllowSorting="True" PageSize="15" ShowStatusBar="true"
    ShowFooter="True" AllowPaging="True" runat="server" GridLines="None" >
    <PagerStyle Mode="NextPrevAndNumeric" />
    <MasterTableView 
        AutoGenerateColumns="false" 
        EditMode="InPlace" 
        AllowFilteringByColumn="True" 
        ShowFooter="True" Width="99%"
        TableLayout="Fixed">
            <Columns>
            <rad:GridBoundColumn 
                DataField="DepartmentId" 
                HeaderText="DepartmentId" 
                SortExpression="DepartmentId"
                UniqueName="DepartmentId">
            <HeaderStyle Width="17%" />
            </rad:GridBoundColumn>
            <rad:GridBoundColumn 
                DataField="DepartmentName" 
                HeaderText="DepartmentName" 
                SortExpression="DepartmentName"
                UniqueName="DepartmentName">
            <HeaderStyle Width="27%" />
            </rad:GridBoundColumn>                  
        </Columns>
    </MasterTableView>
</rad:RadGrid>

Generated SQL

SELECT * 
FROM (SELECT a.*, rownum r___ 
    FROM (SELECT "HR"."DEPARTMENTS"."DEPARTMENT_ID" AS "DepartmentId", "HR"."DEPARTMENTS"."DEPARTMENT_NAME" AS "DepartmentName", "HR"."DEPARTMENTS"."LOCATION_ID" AS "LocationId", "HR"."DEPARTMENTS"."MANAGER_ID" AS "ManagerId" FROM "HR"."DEPARTMENTS") a 
    WHERE rownum < :p4) 
WHERE r___ >=:p2
Parameter: :p4 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: -2147483648.
Parameter: :p2 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

Looking at the parameter creation source code at MSOracleDQE, the pageSize passed from the grid to the dataSource is 2147483647. The parameter creation code looks like:

DbParameter endNoParameter = this.Creator.CreateParameter(ParameterDirection.Input, (pageNumber * pageSize) + 1 );

... and for some reason 2147483647 + 1 = -2147483648 ... I think it's an int overflow and how .net managed the situation.

We will look into this.

(Edit) BTW, this seems to be a Telerik's known issue: http://www.telerik.com/community/forums/aspnet-ajax/grid/maximum-rows-parameter-with-filtering.aspx

David Elizondo | LLBLGen Support Team
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 15-Aug-2012 09:13:52   

As this is not an LLBLGen issue, I don't know if you will get a fix here. However this is a workaround suggested in the Telerik's forum (using CanRetrieveAllData="false") that seems to work:

<rad:RadGrid AutoGenerateColumns="false" ID="RadGrid1" DataSourceID="_departmentsDS" 
  ...>
    <MasterTableView  CanRetrieveAllData="false"
        ...
David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 15-Aug-2012 11:26:32   

Will close the thread for now, as it's a telerik issue.

Frans Bouma | Lead developer LLBLGen Pro
eirizarry
User
Posts: 48
Joined: 30-Mar-2011
# Posted on: 15-Aug-2012 14:55:11   

thanks David....