We have over 25,000 products. I am trying to limit the number of records returned to 500 by using the maximumRows parameter.
Here is my code on the ManageProducts.aspx page:
<asp:ObjectDataSource ID="ProductsDS" runat="server"
OldValuesParameterFormatString="original_{0}" SelectMethod="FindProducts"
TypeName="CommerceBuilder.Products.ProductDataSource"
onselecting="ProductsDS_Selecting" SortParameterName="sortExpression" EnablePaging="true">
<SelectParameters>
<asp:ControlParameter ControlID="Name" Name="name" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="SearchDescriptions" DefaultValue="False" Name="searchDescription" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="Sku" DefaultValue="" Name="sku" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="CategoriesList" DefaultValue="0" Name="categoryId" PropertyName="SelectedValue" Type="Int32" />
<asp:ControlParameter ControlID="ManufacturerList" DefaultValue="0" Name="manufacturerId" PropertyName="SelectedValue" Type="Int32" />
<asp:ControlParameter ControlID="VendorList" DefaultValue="0" Name="vendorId" PropertyName="SelectedValue" Type="Int32" />
<asp:Parameter Name="featured" Type="Object" />
<asp:Parameter DefaultValue="0" Name="taxCodeId" Type="Int32" />
<asp:ControlParameter ControlID="FromPrice" Name="lowPrice" PropertyName="Text" Type="Decimal" />
<asp:ControlParameter ControlID="ToPrice" Name="highPrice" PropertyName="Text" Type="Decimal" />
<asp:ControlParameter ControlID="OnlyDigitalGoods" DefaultValue="False" Name="digitalGoodsOnly" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="OnlyGiftCertificates" DefaultValue="False" Name="giftCertificatesOnly" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="OnlyKits" DefaultValue="False" Name="kitsOnly" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="OnlySubscriptions" DefaultValue="False" Name="subscriptionsOnly" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="ProductGroups" DefaultValue="0" Name="groupId" PropertyName="SelectedValue" Type="Int32" />
<asp:Parameter Name="maximumRows" Type="Int32" DefaultValue="500" />
<asp:Parameter Name="startRowIndex" Type="Int32" DefaultValue="1" />
</SelectParameters>
</asp:ObjectDataSource>
But, it does not seem matter what default value that I enter for maximum rows. I always get all 25,000+ records.
Here is the version info:
AbleCommerce for ASP.NET
VERSION: 7.0.92.9266
Release Label: GoldR12SR1
Please advise.
Thanks,
Mike
Cannot Limit Records on Manage Products Page
-
- Ensign (ENS)
- Posts: 7
- Joined: Fri May 18, 2012 8:27 pm
- jmestep
- AbleCommerce Angel
- Posts: 8164
- Joined: Sun Feb 29, 2004 8:04 pm
- Location: Dayton, OH
- Contact:
Re: Cannot Limit Records on Manage Products Page
AC normally picks up the max rows from the PageSize in the Gridview.
Judy Estep
Web Developer
jestep@web2market.com
http://www.web2market.com
708-653-3100 x209
New search report plugin for business intelligence:
http://www.web2market.com/Search-Report ... -P154.aspx
Web Developer
jestep@web2market.com
http://www.web2market.com
708-653-3100 x209
New search report plugin for business intelligence:
http://www.web2market.com/Search-Report ... -P154.aspx
-
- Ensign (ENS)
- Posts: 7
- Joined: Fri May 18, 2012 8:27 pm
Re: Cannot Limit Records on Manage Products Page
Judy:
Thank you for your reply. I am not trying to limit the number of rows on the page; I am trying to limit the total number of records returned from the database.
How do I do that?
Thanks,
Mike
Thank you for your reply. I am not trying to limit the number of rows on the page; I am trying to limit the total number of records returned from the database.
How do I do that?
Thanks,
Mike
- jmestep
- AbleCommerce Angel
- Posts: 8164
- Joined: Sun Feb 29, 2004 8:04 pm
- Location: Dayton, OH
- Contact:
Re: Cannot Limit Records on Manage Products Page
Default AC code uses this object datasource:
The SelectMethod FindProducts picks up the maximum number to return using the GridView Page Size and executes a select Top(xx) products query. Then the information for those xx products is loaded into the gridview
The SelectCountMethod FindProductsCount does a query like SELECT COUNT(DISTINCT P.ProductId) AS ProductCount FROM ac_Products AS P WHERE P.StoreId = @p0 ',N'@p0 int',@p0=1 and returns one number, not all the products. That number shows in the header xx matching products and with the page size determines how many pages to show on the pager.
By setting the page size to 500, you are limiting the results to the first 500 products on the first page of the gridview. With the default page size of 20, only 20 products are returned on each page of the gridview. It doesn't load all 25,000 products then show only 20.
Other than that, you will need to write your own custom code.
Code: Select all
<asp:ObjectDataSource ID="ProductsDS" runat="server"
OldValuesParameterFormatString="original_{0}" SelectMethod="FindProducts" SelectCountMethod="FindProductsCount"
TypeName="CommerceBuilder.Products.ProductDataSource"
onselecting="ProductsDS_Selecting" SortParameterName="sortExpression" EnablePaging="true">
The SelectCountMethod FindProductsCount does a query like SELECT COUNT(DISTINCT P.ProductId) AS ProductCount FROM ac_Products AS P WHERE P.StoreId = @p0 ',N'@p0 int',@p0=1 and returns one number, not all the products. That number shows in the header xx matching products and with the page size determines how many pages to show on the pager.
By setting the page size to 500, you are limiting the results to the first 500 products on the first page of the gridview. With the default page size of 20, only 20 products are returned on each page of the gridview. It doesn't load all 25,000 products then show only 20.
Other than that, you will need to write your own custom code.
Judy Estep
Web Developer
jestep@web2market.com
http://www.web2market.com
708-653-3100 x209
New search report plugin for business intelligence:
http://www.web2market.com/Search-Report ... -P154.aspx
Web Developer
jestep@web2market.com
http://www.web2market.com
708-653-3100 x209
New search report plugin for business intelligence:
http://www.web2market.com/Search-Report ... -P154.aspx
-
- Commodore (COMO)
- Posts: 436
- Joined: Tue May 07, 2013 1:59 pm
Re: Cannot Limit Records on Manage Products Page
Your ObjectDataSource definition element is missing the SelectCountMethod parameter. Here is what mine looks like, I'm pretty sure this is as shipped (no customizations):
Not sure why that makes a difference, but in very brief testing it appears that it does.
EDIT: With this fixed, as Judy mentioned, you shouldn't have to worry about customizing anything, the query only returns as many records as are needed to fulfill the page size setting.
EDIT AGAIN: Oops, I should have read Judy's post instead of just skimming it, she also pointed out what was missing. Sorry Judy .
Code: Select all
<asp:ObjectDataSource ID="ProductsDS" runat="server"
OldValuesParameterFormatString="original_{0}" SelectMethod="FindProducts" SelectCountMethod="FindProductsCount"
TypeName="CommerceBuilder.Products.ProductDataSource"
onselecting="ProductsDS_Selecting" SortParameterName="sortExpression" EnablePaging="true">
EDIT: With this fixed, as Judy mentioned, you shouldn't have to worry about customizing anything, the query only returns as many records as are needed to fulfill the page size setting.
EDIT AGAIN: Oops, I should have read Judy's post instead of just skimming it, she also pointed out what was missing. Sorry Judy .
Jay