- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Microsoft Access Paging issue
Joined: 20-Mar-2004
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
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.
Joined: 20-Mar-2004
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;
}
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).
Joined: 20-Mar-2004
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
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 ?
Joined: 20-Mar-2004
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
Filename | File size | Added on | Approval |
---|---|---|---|
LLBL_Trace.txt | 14,348 | 25-Mar-2009 19:42.44 | Approved |
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.
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?
Joined: 17-Apr-2010
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 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 3
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 4
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
In the page 4 I saw all the records. It happends to me with all my tables.
Thanks for the help
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.
Joined: 17-Apr-2010
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
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.
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.
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).