HnD | Microsoft Access Paging issue, Page 1

Microsoft Access Paging issue

Posts

kakaiya
User

Posts: 161
Joined: 20-Mar-2004

# Posted on: 25-Mar-2009 04:01:12

Hi,

On one webpage placed a gridview control with paging enabled.

And using below code to get the data for a single page (25 is default pagesize for gridview) but after adapter.FetchEntityCollection line is executed and when checking eventList collection variable it display many more rows (1000+) in it than the page size specified. On screen GridView display 25 rows but why it is fetching 1000+ rows when pagesize is specified.


            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                if ((pageNumber.HasValue == false) && (pageSize.HasValue == false))
                {
                    adapter.FetchEntityCollection(eventList, bucket, 0, sorter, prefetchPath);
                }
                else
                {
                    adapter.FetchEntityCollection(eventList, bucket, pageSize.Value, sorter, prefetchPath, pageNumber.Value, pageSize.Value);
                }
            }

LLBLGenPro 2.6 Runtime Library: v2.6.08.1211 Templates: Adapter .NET Framework: .NET 2.0/3.5 Database: Microsoft Access 2003

regards

kaksss

daelmo
Support Team

Posts: 8245
Joined: 28-Nov-2005

# Posted on: 25-Mar-2009 06:48:21

ASP.Net or WinForms? How are you binding the collection to the GridView? Are you using LLBLGenProDataSource(2)? Please post code snippet where you are performing the binding, and if using ASP.Net, post the relevant declarative aspx of the GridView and the LLBLGenProDataSource.

David Elizondo | LLBLGen Support Team

kakaiya
User

Posts: 161
Joined: 20-Mar-2004

# Posted on: 25-Mar-2009 07:01:21

ASP.Net or WinForms? - ASP.NET How are you binding the collection to the GridView? - **Yes ** Are you using LLBLGenProDataSource(2)? No Please post code snippet where you are performing the binding, and if using ASP.Net, post the relevant declarative aspx of the GridView and the LLBLGenProDataSource.

in .aspx page

<asp:GridView ID="grdEventList" runat="server" AutoGenerateColumns="False" Width="102%" CssClass="gv" DataKeyNames="EventId" AllowPaging="True" AllowSorting="True" PageSize="25" RowStyle-VerticalAlign="Top" RowStyle-HorizontalAlign="Left" CellPadding="3" GridLines="Horizontal" ShowFooter="true" OnRowDataBound="grdEventList_RowDataBound" OnRowCommand="grdEventList_RowCommand" OnSorting="grdEventList_Sorting" OnPageIndexChanging="grdEventList_PageIndexChanging">
    <Columns>
        <asp:BoundField Visible="false" AccessibleHeaderText="EventId" DataField="EventId" ItemStyle-Width="20px" HeaderText="Id" ReadOnly="True" SortExpression="EventId" ItemStyle-Wrap="false" />
        <asp:BoundField Visible="false" AccessibleHeaderText="EventPriorityId" DataField="EventPriorityId" ItemStyle-Width="20px" HeaderText="PriId" SortExpression="EventPriorityId" />
        <asp:TemplateField ItemStyle-Width="20px" FooterStyle-Width="20px" AccessibleHeaderText="CheckBox">
            <ItemTemplate>
                <asp:CheckBox runat="server" ID="chkGridRow" />
            </ItemTemplate>
            <HeaderTemplate>
                <asp:CheckBox runat="server" ID="chkSelectAllRows" />
            </HeaderTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="EventName" ItemStyle-Width="250px" HeaderText="Event Name" SortExpression="EventName" AccessibleHeaderText="EventName" />
        <asp:TemplateField HeaderText="Date" ItemStyle-Width="80px" SortExpression="EventDate" AccessibleHeaderText="EventDate">
            <ItemTemplate>
                <asp:HyperLink runat="server" ID="hypEventDate" Text='<%# (DataBinder.Eval(Container.DataItem, "EventDate")) %>'></asp:HyperLink>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="EventLocation" ItemStyle-Width="80px" HeaderText="Location" SortExpression="EventLocation" AccessibleHeaderText="EventLocation" />
        <asp:TemplateField HeaderText="Category" ItemStyle-Width="80px" AccessibleHeaderText="CategoryName">
            <ItemTemplate>
                <asp:LinkButton runat="server" ID="lbCategoryID" CommandName="itemCategory" CommandArgument='<%# DataBinder.Eval(Container.DataItem, "EventCategoryId") %>' Text='<%# DataBinder.Eval(Container.DataItem, "EventCategoryId") %>'></asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="EventAmount" ItemStyle-Width="70px" HeaderText="Amount" SortExpression="EventAmount" AccessibleHeaderText="EventAmount" ItemStyle-Wrap="false" DataFormatString="{0:#,###,##0.00}" ItemStyle-HorizontalAlign="Right" FooterStyle-Font-Bold="true" FooterStyle-HorizontalAlign="Right" FooterStyle-Width="70px" />
        <asp:BoundField DataField="EventDescription" ItemStyle-Width="500px" HeaderText="Description" SortExpression="EventDescription" AccessibleHeaderText="EventDescription" HeaderStyle-HorizontalAlign="Left" FooterStyle-Font-Bold="true" FooterStyle-HorizontalAlign="Left" />
    </Columns>
    <PagerStyle HorizontalAlign="Center" CssClass="gvPager" />
    <PagerSettings Mode="NumericFirstLast" FirstPageText="<<" PreviousPageText="<" NextPageText=">" LastPageText=">>" Position="TopAndBottom" PageButtonCount="10" />
</asp:GridView>

.aspx.cs


private void BindData()
{
EntityCollection<EventEntity> eventList = BLL.Event.GetEventList(status, categoryID, searchFor, searchWhere, fromDate, toDate, matchPattern, pageSize, pageNumber, sortExpressionField, sortOperatorAscDes, sortOnPriority, isMinusOnly);

            if (eventList.Count > 0)
            {
                grdEventList.DataSource = eventList;
                grdEventList.DataBind();
                return eventList;
            }
}

in BLL


        public static EntityCollection<EventEntity> GetEventList(ConstantsEnums.EventStatus.Status status, int? categoryId, string searchFor, ConstantsEnums.SearchWhereIndex? searchWhere, DateTime? fromDate, DateTime? toDate, ConstantsEnums.MatchPatternIndex? matchPattern, int? pageSize, int? pageNumber, EntityField2 sortField, SortOperator sortOperator, bool sortOnPriority, bool isMinusOnly)
        {
            EntityCollection<EventEntity> eventList = new EntityCollection<EventEntity>();

            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            IPredicateExpression filter = new PredicateExpression();

            //status
            if ((!string.IsNullOrEmpty(status.ToString())) && (status != ConstantsEnums.EventStatus.Status.A))
            {
                filter.AddWithAnd(EventFields.EventStatusId == status.ToString().ToUpper());
            }

            //categoryId
            if (categoryId.HasValue)
            {
                filter.AddWithAnd(EventFields.EventCategoryId == categoryId.Value);
            }

            //isMinusOnly
            if (isMinusOnly)
            {
                filter.AddWithAnd(EventFields.EventAmount < 0);
            }

            bucket.PredicateExpression.Add(filter);
            bucket.Relations.Add(EventEntity.Relations.CategoryEntityUsingEventCategoryId);

            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EventEntity);
            prefetchPath.Add(EventEntity.PrefetchPathCategory);

            SortExpression sorter = new SortExpression();
            sorter.Add(sortField | sortOperator);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                if ((pageNumber.HasValue == false) && (pageSize.HasValue == false))
                {
                    adapter.FetchEntityCollection(eventList, bucket, 0, sorter, prefetchPath);
                }
                else
                {
                    adapter.FetchEntityCollection(eventList, bucket, pageSize.Value, sorter, prefetchPath, pageNumber.Value, pageSize.Value);
                }
            }

            return eventList;
        }

Walaa
Support Team

Posts: 14950
Joined: 21-Aug-2005

# Posted on: 25-Mar-2009 09:24:40

The following is qouted from the Manual (Concepts - Database Drivers)

Microsoft Access All features of MS Access 2000, except parameterized stored queries, so no stored procedure calls Database passwords, security files are supported. **Limiting the number of objects to return in a query requires the primary key field(s) to be added to a sort clause if a sort clause is specified. ** LLBLGen Pro transaction savepoints aren't supported.

Try it first, otherwise try using an LLBLGenProDataSource, and then you can try its enablepaging property (true: paging on the database, false: paging on the client side).

kakaiya
User

Posts: 161
Joined: 20-Mar-2004

# Posted on: 25-Mar-2009 11:33:08

I have tried to add the Primary key in sorter (on top of current sorting fields) but still it's returning 1000+ rows.

Then also removed all other fields in sorter and just sort on primary key and still it's returning 1000+ rows.

Any way to fix this?

regards

kaksss

Walaa
Support Team

Posts: 14950
Joined: 21-Aug-2005

# Posted on: 25-Mar-2009 14:24:02

Did you try the LLBLGenProDataSource?

Otis
LLBLGen Pro Team

Posts: 39614
Joined: 17-Aug-2003

# Posted on: 25-Mar-2009 16:02:39

I also would like to see the generated SQL queries. Please turn on DQE tracing (see troubleshooting and debugging) and grab the queries from the output window in vs.net (run your webapp in debug mode). Are you sure the code (not everything is posted, the paging code for example seems missing) sends indeed proper page number and page size values to the BLL ?

Frans Bouma | Lead developer LLBLGen Pro

kakaiya
User

Posts: 161
Joined: 20-Mar-2004

# Posted on: 25-Mar-2009 19:42:22

Hi Otis,

See attached file for LLBL trace.

on below line in Debug mode - using breakpoint

adapter.FetchEntityCollection(eventList, bucket, pageSize.Value, sorter, prefetchPath, pageNumber.Value, pageSize.Value);

pageNumber.Value = 1 pageSize.Value = 50 sorter = EventEntity.[EventId] ASC

prefetchPath = (see below) - Path element for RootEntityType 'EventEntity' - EventEntity - Category (CategoryEntity, ManyToOne)

bucket = (see below) RelationCollection:

( CategoryEntity INNER JOIN EventEntity ON CategoryEntity.CategoryId=EventEntity.EventCategoryId )

PredicateExpression: ( ( EventEntity.[EventDate] >= @EventDate1 AND EventEntity.[EventDate] <= @EventDate2 ) )

@EventDate1 01/07/2008 0:00:00 AM @EventDate2 30/06/2009 0:00:00 AM

Hope this helps.

Regards

Kaksss

Attachments
Filename	File size	Added on	Approval
LLBL_Trace.txt	14,348	25-Mar-2009 19:42.44	Approved

Walaa
Support Team

Posts: 14950
Joined: 21-Aug-2005

# Posted on: 26-Mar-2009 10:22:24

Might be related to this issue: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=13411

So the question is why are you joining with the Category as sown here:

bucket.Relations.Add(EventEntity.Relations.CategoryEntityUsingEventCategoryId);

And what happens when you comment this line out.

Otis
LLBLGen Pro Team

Posts: 39614
Joined: 17-Aug-2003

# Posted on: 26-Mar-2009 17:22:34

Our MS Access code pages always in memory as it has no way to specify a page in a resultset inside the 'database' (ms access doesn't offer that). This isn't as bad as it sounds, it simply fetches as much rows as required for the page from the datareader and then closes the connection.

That said, I now see you use a prefetch path. Paging and prefetch paths only work if you have a page size which is < the ParameterizedPrefetchPathThreshold on the adapter, which is default 50. Paging otherwise can't work, as the child entities can't be filtered on the parent without it.

Could you please set this threshold to 100 and try again?

Frans Bouma | Lead developer LLBLGen Pro

kakaiya
User

Posts: 161
Joined: 20-Mar-2004

# Posted on: 01-Apr-2009 06:38:24

Hi,

Please mark as resolve this issue as I have used alternate code (which generate another SQL) to give me same resultset.

Thanks.

demesa
User

Posts: 4
Joined: 17-Apr-2010

# Posted on: 17-Apr-2010 21:15:05

Hello, I'm using Dinamyc Data Web Site with LLBLGen Pro 2.6 with a Microsoft Access Database, but I've problems with the paging, the page's number (n of m) calc is fine, but in every page I saw always the same rows, except in the last page, when I get all the rows.

The trace for a table with 33 rows and 10 result per page (4 pages in total) is:

//For page 1

Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [Niveles].[Nivel], [Niveles].[Nombre del nivel] AS [NombreDelNivel], [Niveles].[Codigo de Seccion] AS [CodigoDeSeccion], [Niveles].[Siguiente nivel] AS [SiguienteNivel], [Niveles].[Artificial], [Niveles].[IH] AS [Ih], [Niveles].[UnidadIntensidadHoraria], [Niveles].[Semestre], [Niveles].[Creado], [Niveles].[Modificado] FROM [Niveles]

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [SedeJornada].[IdSedeJornada], [SedeJornada].[SedeJornada], [SedeJornada].[EtiquetaSJ] AS [EtiquetaSj], [SedeJornada].[OrdenSJ] AS [OrdenSj], [SedeJornada].[Sede], [SedeJornada].[Jornada], [SedeJornada].[NombreInstitucion], [SedeJornada].[Direccion], [SedeJornada].[Telefono1], [SedeJornada].[Telefono2], [SedeJornada].[IdSecretaria], [SedeJornada].[ColorCarne], [SedeJornada].[Fax], [SedeJornada].[IdRector], [SedeJornada].[FuentePieCertificado], [SedeJornada].[FirmasConstancia] FROM [SedeJornada]

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ Method Enter: CreateRowCountDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [Cursos].[Curso], [Cursos].[Nivel], [Cursos].[Nombre del Curso] AS [NombreDelCurso], [Cursos].[Director de curso] AS [DirectorDeCurso], [Cursos].[Cupos], [Cursos].[Jornada], [Cursos].[SedeJornada], [Cursos].[EtiquetaGrupo], [Cursos].[Codirector], [Cursos].[Creado], [Cursos].[Modificado], [Cursos].[IdCoordinador], [Cursos].[ZipExportado], [Cursos].[SiguienteSedeJornada] FROM [Cursos]

Method Exit: CreateSelectDQ Generated Sql query: Query: SELECT COUNT(*) AS NumberOfRows FROM (SELECT [Cursos].[Curso], [Cursos].[Nivel], [Cursos].[Nombre del Curso] AS [NombreDelCurso], [Cursos].[Director de curso] AS [DirectorDeCurso], [Cursos].[Cupos], [Cursos].[Jornada], [Cursos].[SedeJornada], [Cursos].[EtiquetaGrupo], [Cursos].[Codirector], [Cursos].[Creado], [Cursos].[Modificado], [Cursos].[IdCoordinador], [Cursos].[ZipExportado], [Cursos].[SiguienteSedeJornada] FROM [Cursos]) TmpResult

Method Exit: CreateRowCountDQ Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [Cursos].[Curso], [Cursos].[Nivel], [Cursos].[Nombre del Curso] AS [NombreDelCurso], [Cursos].[Director de curso] AS [DirectorDeCurso], [Cursos].[Cupos], [Cursos].[Jornada], [Cursos].[SedeJornada], [Cursos].[EtiquetaGrupo], [Cursos].[Codirector], [Cursos].[Creado], [Cursos].[Modificado], [Cursos].[IdCoordinador], [Cursos].[ZipExportado], [Cursos].[SiguienteSedeJornada] FROM [Cursos]

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ

//For page 2

Method Enter: CreateRowCountDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [Cursos].[Curso], [Cursos].[Nivel], [Cursos].[Nombre del Curso] AS [NombreDelCurso], [Cursos].[Director de curso] AS [DirectorDeCurso], [Cursos].[Cupos], [Cursos].[Jornada], [Cursos].[SedeJornada], [Cursos].[EtiquetaGrupo], [Cursos].[Codirector], [Cursos].[Creado], [Cursos].[Modificado], [Cursos].[IdCoordinador], [Cursos].[ZipExportado], [Cursos].[SiguienteSedeJornada] FROM [Cursos]

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ

//For page 3

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ

//For page 4

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ

In the page 4 I saw all the records. It happends to me with all my tables.

Thanks for the help

Otis
LLBLGen Pro Team

Posts: 39614
Joined: 17-Aug-2003

# Posted on: 18-Apr-2010 11:25:58

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7725

Frans Bouma | Lead developer LLBLGen Pro

demesa
User

Posts: 4
Joined: 17-Apr-2010

# Posted on: 20-Apr-2010 01:01:23

Version: 2.6 Final October 9 2009 Demo Runtime Library Version: SD.LLBLGen.Pro.DynamicDataSupportClasses.NET35.dll 2.6.9.501 Template Group: Adapter Database: Microsoft Access 2003

Walaa
Support Team

Posts: 14950
Joined: 21-Aug-2005

# Posted on: 20-Apr-2010 10:21:02

Please open a new thread for your issue.

And while doing this, please use the latest available version of LLBLGen Pro, as ou are using a very old one.

Also please post a code snippet of what you are using for paging.

Thanks.

demesa
User

Posts: 4
Joined: 17-Apr-2010

# Posted on: 20-Apr-2010 17:57:38

Hello, when I download LLBLGen Pro demo from the page

http://www.llblgen.com/pages/DownloadDemo.aspx

that's the version I got

2.6 Final October 2009

Thank you

Otis
LLBLGen Pro Team

Posts: 39614
Joined: 17-Aug-2003

# Posted on: 20-Apr-2010 20:26:23

demesa wrote:

Hello, when I download LLBLGen Pro demo from the page

http://www.llblgen.com/pages/DownloadDemo.aspx

that's the version I got

2.6 Final October 2009

Thank you

The problem with paging on access is that it always happens on the client, because there's no way to page inside MS Access, it doesn't offer any SQL statements / temp tables etc. to do this. Our code pages on the client by reading n rows so the page is read and then closes the datareader. That's why the queries don't have any filtering / paging sql as there isn't any.

It's a mystery to me why this doesn't work though. We'll look into it by creating a dyn. data site on ms access' northwind database and see if we can reproduce it.

Frans Bouma | Lead developer LLBLGen Pro

Otis
LLBLGen Pro Team

Posts: 39614
Joined: 17-Aug-2003

# Posted on: 21-Apr-2010 10:44:13

I can reproduce the issue you're seeing on Access. I'll look into it what's causing it.

(edit) We identified the problem inside our runtime library: when client-side paging was executed and no client-side distinct filtering (this is the case in your situation) client-side paging not always works. We're working on a fix.

Frans Bouma | Lead developer LLBLGen Pro

Otis
LLBLGen Pro Team

Posts: 39614
Joined: 17-Aug-2003

# Posted on: 21-Apr-2010 11:54:20

Please use the ormsupportclasses dll I attached to this post (click the paperclip). it will fix your problem regarding paging on ms access. (which isn't dyn. data related btw).

Frans Bouma | Lead developer LLBLGen Pro

demesa
User

Posts: 4
Joined: 17-Apr-2010

# Posted on: 21-Apr-2010 18:48:15

Thank you for your time, the fix works fine!

kakaiya
User

Posts: 161
Joined: 20-Mar-2004

# Posted on: 22-Apr-2010 02:05:02

Hi Otis,

Is this fix available on LLBLGen site to download with source?

Thanks.

Walaa
Support Team

Posts: 14950
Joined: 21-Aug-2005

# Posted on: 22-Apr-2010 10:43:48

I believe this will be available soon in the next release (few days ahead). You can use the attached dll, for now till the next official release.

Otis
LLBLGen Pro Team

Posts: 39614
Joined: 17-Aug-2003

# Posted on: 23-Apr-2010 17:33:31

Source is now available.

Frans Bouma | Lead developer LLBLGen Pro