quote:

---

Originally posted by RoLYroLLs

Hello all. I've been up and down Google and searched this site as well, but haven't run into any 'good' information on this issue.

The heart of the issue is paging, which I did find a good article for paging in SQL 2000 a while back, and just recently one for SQL2005, which supports paging.

Anyhow here's the situation. When I've come across paging, I always used client-side paging (using ASP) since I was naive about doing it at the SQL (server-side) level. Now I want to optimize my apps to take advantage of this server-side paging. I can successfully only get the records I want from SQL, but my issue is this. Say I want 10 records per page...fine. But without returning the total number of records, how do I know how many 'more' records are there in order to display a next button, or maybe display all the possible pages available with the page size? I've successfully writen a sproc that first returns a recordset with the records, and then another select returning the total count, but I get an ASP error, when I use "set rs= rs.NextRecordset" saying "ADODB.Recordset (0x800A0CB3) Current provider does not support returning multiple recordsets from a single execution". I've also tried returning the value as a returned value, and an output value, but it seems the issue lies somewhere else.

By the error message itself, I'm sure I can go ahead and execute another sproc/query to return the count, but I'm trying to determine a way to do it with a single execution. I've tried using the SQLOLEDB provider, using an SQL Native Client DSN connection. I'm all outta ideas. Unless it I'm going wrong about it and the provider the error speaks of is ADODB.

Thanks everyone.

- RoLY roLLs

---

Select  
	totalRecords = (Select count (distinct assetID) from #TempItems), 
	AssetID, 
FROM  
	#TempItems 
Where  ID >  @FirstRec  AND ID < @LastRec
Order By ID ASC

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[proc_ShowClient]
	@Page int
	, @PageSize int
as
	declare
	 @PageStart int
	, @PageEnd int

	set @PageStart = (@PageSize * @Page) - @PageSize + 1;
	set @PageEnd = @PageSize * @Page;

begin
	select
		Client.ClientID
		, dbo.DeFormatGUID(Client.ClientUID) as ClientUID
		, Client.ClientAccountNumber
		, Client.ClientCompany
		, Client.ClientFirstName
		, Client.ClientMiddleName
		, Client.ClientLastName
	from
		(
		select top (@PageEnd)
			a.*
			, ROW_NUMBER() over(order by a.ClientCompany) as TheCount
		from
			Client a
		order by
			a.ClientCompany) Client
	where
		Client.TheCount between @PageStart and @PageEnd
	
end

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[proc_ShowClient]
	@Page int
	, @PageSize int
as
	declare
	 @PageStart int
	, @PageEnd int
	, @Count int
	
	set nocount on --is this correct what i do here, setting nocount on?
	select @Count = count(Client.ClientID) from client
	set nocount off

	set @PageStart = (@PageSize * @Page) - @PageSize + 1;
	set @PageEnd = @PageSize * @Page;

begin
	select
		@Count as TotalCount
		, Client.ClientID
		, Client.ClientUID
		, Client.ClientAccountNumber
		, Client.ClientCompany
		, Client.ClientFirstName
		, Client.ClientMiddleName
		, Client.ClientLastName
	from
		(
		select top (@PageEnd)
			a.*
			, ROW_NUMBER() over(order by a.ClientCompany) as TheCount
		from
			Client a
		order by
			a.ClientCompany) Client
	where
		Client.TheCount between @PageStart and @PageEnd
	
end

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[proc_ShowClient]
	@Page int
	, @PageSize int
as
	declare
	 @PageStart int
	, @PageEnd int
	, @Count int
	
	set nocount on --is this correct what i do here, setting nocount on?
	select @Count = count(Client.ClientID) from client
	set nocount off

	set @PageStart = (@PageSize * @Page) - @PageSize + 1;
	set @PageEnd = @PageSize * @Page;

begin
	select
		@Count as TotalCount
		, Client.ClientID
		, Client.ClientUID
		, Client.ClientAccountNumber
		, Client.ClientCompany
		, Client.ClientFirstName
		, Client.ClientMiddleName
		, Client.ClientLastName
	from
		(
		select top (@PageEnd)
			a.*
			, ROW_NUMBER() over(order by a.ClientCompany) as TheCount
		from
			Client a
		order by
			a.ClientCompany) Client
	where
		Client.TheCount between @PageStart and @PageEnd
	
end

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[proc_ShowClient]
	@Page int
	, @PageSize int
as
	declare
	 @PageStart int
	, @PageEnd int

	set @PageStart = (@PageSize * @Page) - @PageSize + 1;
	set @PageEnd = @PageSize * @Page;

begin
	select
		(select count(Client.ClientID) from client) as TotalCount
		, Client.ClientID
		, Client.ClientUID
		, Client.ClientAccountNumber
		, Client.ClientCompany
		, Client.ClientFirstName
		, Client.ClientMiddleName
		, Client.ClientLastName
	from
		(
		select top (@PageEnd)
			a.*
			, ROW_NUMBER() over(order by a.ClientCompany) as TheCount
		from
			Client a
		order by
			a.ClientCompany) Client
	where
		Client.TheCount between @PageStart and @PageEnd
	
end