v3.30 - Incorrect syntax near 'ProductLoadAllPaged'. when trying to view a product.

This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.
9 years ago
This Error is happening in SQLServer\SQLEXPRESS 2008
but working in SQL Server 2008

nopCommerce Ver: nopCommerce_3.40_Source
9 years ago
Hi,

I m also facing the same problem can any body guide me how i can resolve this problem.

i m using nopcommerce V3.50 source and using SQL Server 2012.

Thank You..
9 years ago
Dude ...you are the best....super

tuhincse24 wrote:
I have executed above sql in SQL Server 2005 and  2008R2 and got same error message as like..
Running the sql above  returns the following on 2005

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ProductLoadAllPaged'.

(1 row(s) affected)


It's seems above syntax is not supported by SQL Server 2008R2 and lower version.
This error occurred when ExecuteStoredProcedureList function called in code base.



Most possible solution is:
Add Exec keyword just before  ProductLoadAllPaged in ProductServices.cs file.
var products = _dbContext.ExecuteStoredProcedureList<Product>(
                    "Exec ProductLoadAllPaged",
                    pCategoryIds,
                    pManufacturerId,
                    pStoreId,
                    pVendorId,
                    pWarehouseId,
                    pParentGroupedProductId,
                    pProductTypeId,
                    pVisibleIndividuallyOnly,
                    pProductTagId,
                    pFeaturedProducts,
                    pPriceMin,
                    pPriceMax,
                    pKeywords,
                    pSearchDescriptions,
                    pSearchSku,
                    pSearchProductTags,
                    pUseFullTextSearch,
                    pFullTextMode,
                    pFilteredSpecs,
                    pLanguageId,
                    pOrderBy,
                    pAllowedCustomerRoleIds,
                    pPageIndex,
                    pPageSize,
                    pShowHidden,
                    pLoadFilterableSpecificationAttributeOptionIds,
                    pFilterableSpecificationAttributeOptionIds,
                    pTotalRecords);


It would produce below sql and execute without any syntax error.

declare @p29 nvarchar(max)
set @p29=NULL
declare @p30 int
set @p30=0
exec sp_executesql N'exec ProductLoadAllPaged @CategoryIds, @ManufacturerId, @StoreId, @VendorId, @WarehouseId, @ParentGroupedProductId, @ProductTypeId, @VisibleIndividuallyOnly, @ProductTagId, @FeaturedProducts, @PriceMin, @PriceMax, @Keywords, @SearchDescriptions, @SearchSku, @SearchProductTags, @UseFullTextSearch, @FullTextMode, @FilteredSpecs, @LanguageId, @OrderBy, @AllowedCustomerRoleIds, @PageIndex, @PageSize, @ShowHidden, @LoadFilterableSpecificationAttributeOptionIds, @FilterableSpecificationAttributeOptionIds output, @TotalRecords output',N'@CategoryIds nvarchar(4000),@ManufacturerId int,@StoreId int,@VendorId int,@WarehouseId int,@ParentGroupedProductId int,@ProductTypeId int,@VisibleIndividuallyOnly int,@ProductTagId int,@FeaturedProducts bit,@PriceMin decimal(29,0),@PriceMax decimal(29,0),@Keywords nvarchar(4),@SearchDescriptions bit,@SearchSku bit,@SearchProductTags bit,@UseFullTextSearch bit,@FullTextMode int,@FilteredSpecs nvarchar(4000),@LanguageId int,@OrderBy int,@AllowedCustomerRoleIds nvarchar(5),@PageIndex int,@PageSize int,@ShowHidden bit,@LoadFilterableSpecificationAttributeOptionIds bit,@FilterableSpecificationAttributeOptionIds nvarchar(max)  output,@TotalRecords int output',@CategoryIds=N'',@ManufacturerId=0,@StoreId=0, @VendorId=0, @WarehouseId=0,@ParentGroupedProductId=0, @ProductTypeId=NULL,@VisibleIndividuallyOnly=0, @ProductTagId=0,@FeaturedProducts=NULL,@PriceMin=NULL,@PriceMax=NULL, @Keywords=N'test',@SearchDescriptions=0,@SearchSku=1,@SearchProductTags=0,@UseFullTextSearch=0, @FullTextMode=0, @FilteredSpecs=N'',@LanguageId=0,@OrderBy=0,@AllowedCustomerRoleIds=N'1,2,3',@PageIndex=0,@PageSize=15, @ShowHidden=1,@LoadFilterableSpecificationAttributeOptionIds=0,@FilterableSpecificationAttributeOptionIds=@p29 output,@TotalRecords=@p30 output
select @p29, @p30
8 years ago
I got the solution,

Put keyword "EXEC" at beginning -
EXEC ProductLoadAllPaged ...........

now, it will run successfully...
8 years ago
Hello,

I am facing the same issue with nopcommerce 3.6 and ran the above script and getting the same error on SQLSERVER 2008 R2 express edition.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ProductLoadAllPaged'.

Does it mean that latest version cant work with SQLSERVER 2008 R2?
8 years ago
Thank you ManishNayak!!

That worked. Just to add/clarify:

Edit ProductService.cs at line 647

From:
var products = _dbContext.ExecuteStoredProcedureList<Product>(
                    "ProductLoadAllPaged",
                    pCategoryIds,
                    ...

To:
var products = _dbContext.ExecuteStoredProcedureList<Product>(
                    "EXEC ProductLoadAllPaged",
                    pCategoryIds,
                    ...

Thank you again ManishNayak!
7 years ago
Hello! Thanks! it is not fixed in version 3.70 yet.
7 years ago
I am using 3.70 version, it is on sql 2012 (host by godaddy) and I get this error when trying to click into one of the categories from the home page. I cannot locate ProductServices.cs ; i installed the easier web-ready version. Where would this reference to call the stored procedure ProductLoadAllPaged be in the 3.70 version?
7 years ago
IN 3.7 and 3.8 ProductService is in Nop.Services.Catalog in Folder Libraries/Nop.Services project and folder Catalog
7 years ago
I played with it some more this morning and it is not an issue with the stored proc (which has not changed from version 3.2) but with the method ExecuteStoredProcedureList in \libraries\nop.data\NopObjectContext. If I take the code for ExecuteStoredProcedureList  from 3.2 and stick it in the 3.3 code base it fixes the error in the two scenarios above for the MSSQL 2005 database
This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.