Posted by rhungund on 10/01/13 11:33

Hi all...I have a stored proc that works fine except I can't get the
paging property to work in ASP. It seems that because I'm building the
SQL withing the SP is causing the problem. If I don't build the SQL as
a string paging works. In my ASP page, the pagecount property returns
-1 for some reason.

Here's the proc:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Procedure LS_DisplayAllCategoriesBoolean
@aSections varchar(255),
@field varchar(255)

As

declare @sql varchar(1000)
SET NOCOUNT ON

set @sql = 'SELECT DISTINCT ls_product_catalog.id,
ls_product_catalog.ItemNumber, ls_product_catalog.Name,
ls_product_catalog.[Descriptor], ls_product_catalog.Price,
ls_product_catalog.Shade_Name, ls_product_catalog.Sale_Price,
ls_product_catalog.Sale_Desc
FROM dbo.ls_brand INNER JOIN
dbo.ls_product_catalog ON dbo.ls_brand.nID =
dbo.ls_product_catalog.CategoryID INNER JOIN
dbo.ls_product_category ON dbo.ls_product_catalog.id =
dbo.ls_product_category.nProductID
WHERE dbo.ls_brand.bVisible = 1 and
dbo.ls_product_category.nCategoryID in (Select IntValue
from dbo.CsvToInt(''' + @aSections + ''') ) '

if @field = 'bNew'
begin
set @sql = @sql + ' AND bNew = 1 AND
(dbo.ls_product_catalog.bGiftOnly is null or
dbo.ls_product_catalog.bGiftOnly = 0)'
set @sql = @sql + ' order by
ls_product_catalog.[Descriptor]'
end
if @field = 'bGift'
begin
set @sql = @sql + ' AND bGift = 1 or bGiftOnly =1'
set @sql = @sql + ' order by
ls_product_catalog.[Descriptor]'
end

if @field = 'bGiftOnly'
begin
set @sql = @sql + ' AND bGiftOnly= 1'
set @sql = @sql + ' order by
ls_product_catalog.[Descriptor]'
end

if @field = 'sale_price'
begin
set @sql = @sql + ' AND (sale_price is not null OR
sale_desc is not
null)'
set @sql = @sql + ' order by ls_product_catalog.Name'
end

exec(@sql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON

[Back to original message]