Datalist pagination - How?

[VB Programmer]

I am using a datalist on an ASP.NET 2.0 website. Any idea (or code
examples) how I can use pagination on a datalist?

Thanks!

[Eliyahu Goldin]

You can consider getting just one page of the data from the database with
sql parameters TOP and START AT.

Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]

"VB Programmer" <do**********@s omewhere.comwro te in message
news:ei******** *****@TK2MSFTNG P03.phx.gbl...

>I am using a datalist on an ASP.NET 2.0 website. Any idea (or code
examples) how I can use pagination on a datalist?

Thanks!

[Muhammad Mosa]

Here is an example:
1) A stored procedure that gets page of records from products table in
Northwind database
--------------------------------------------------------------------------------------
ALTER PROC usp_GetProducts Page
(
@pgNdx int,
@pgSize int,
@catId int = NULL
)
AS
-- create a temporary table with the columns we are interested in
CREATE TABLE #ProductsPage
(
RecordID int IDENTITY PRIMARY KEY,
ProductID int,
ProductName nvarchar(40),
UnitPrice money,
UnitsInStock smallint,
CategoryName nvarchar(15),
CompanyName nvarchar(40),
QuantityPerUnit nvarchar(20),
UnitsOnOrder smallint,
ReorderLevel smallint,
Discontinued bit,
CategoryId int,
SupplierId int
)
-- fill the temp table with all the employees
INSERT INTO #ProductsPage
(
ProductID,
ProductName,
UnitPrice,
UnitsInStock,
CategoryName,
CompanyName,
QuantityPerUnit ,
UnitsOnOrder,
ReorderLevel,
Discontinued,
CategoryID,
SupplierID
)
SELECT ProductID,Produ ctName,UnitPric e,UnitsInStock, CategoryName,
CompanyName,Qua ntityPerUnit,Un itsOnOrder,Reor derLevel,Discon tinued,
CategoryID,Supp lierID
FROM vw_ProductsList
WHERE CategoryID = @catId OR @catId IS NULL OR @catId=-1
ORDER BY ProductID

-- declare two variables to calculate the range of records
-- to extract for the specified page

DECLARE @fromId int
DECLARE @toId int
SET @fromId= (@pgNdx * @pgSize)
SET @toId= @fromId + @pgSize -1

-- select the page of records
SELECT * FROM #ProductsPage
WHERE (RecordID BETWEEN @fromId AND @toId)AND
(CategoryID = @catId OR @catId IS NULL OR @catId=-1)
GO
-- usp_GetProducts Page 0,10
---------------------------------------------------------------------------------
You will also need another procedures that returns total number of records
in your products table.

2)then I suggest for simplisity, to add a dropdownlist to your DataList
header or any where in the page, fill this dropdownlist with numbers from 1
to total number of pages which should be
pages = totalNumOfRecor ds / pageRecordsCoun t
increase the pages by 1 if the totalNumOfRecor ds Mod pageRecordsCoun t is
greater that 0

3)Now when the user change the page from the dropdownlist, you need to
submit the page number along with the page size to the above stored procedure.

I wish I could make clearer.. Please contact me by your e-mail and I'll
submit a full example to you if you wish.

Reagrds

[VB Programmer]

Thanks. I'll look this over!

"Muhammad Mosa" <Muhammad Mo**@discussion s.microsoft.comwrote in message
news:2D******** *************** ***********@mic rosoft.com...

Here is an example:
1) A stored procedure that gets page of records from products table in
Northwind database
--------------------------------------------------------------------------------------
ALTER PROC usp_GetProducts Page
(
@pgNdx int,
@pgSize int,
@catId int = NULL
)
AS
-- create a temporary table with the columns we are interested in
CREATE TABLE #ProductsPage
(
RecordID int IDENTITY PRIMARY KEY,
ProductID int,
ProductName nvarchar(40),
UnitPrice money,
UnitsInStock smallint,
CategoryName nvarchar(15),
CompanyName nvarchar(40),
QuantityPerUnit nvarchar(20),
UnitsOnOrder smallint,
ReorderLevel smallint,
Discontinued bit,
CategoryId int,
SupplierId int
)
-- fill the temp table with all the employees
INSERT INTO #ProductsPage
(
ProductID,
ProductName,
UnitPrice,
UnitsInStock,
CategoryName,
CompanyName,
QuantityPerUnit ,
UnitsOnOrder,
ReorderLevel,
Discontinued,
CategoryID,
SupplierID
)
SELECT ProductID,Produ ctName,UnitPric e,UnitsInStock, CategoryName,
CompanyName,Qua ntityPerUnit,Un itsOnOrder,Reor derLevel,Discon tinued,
CategoryID,Supp lierID
FROM vw_ProductsList
WHERE CategoryID = @catId OR @catId IS NULL OR @catId=-1
ORDER BY ProductID

-- declare two variables to calculate the range of records
-- to extract for the specified page

DECLARE @fromId int
DECLARE @toId int
SET @fromId= (@pgNdx * @pgSize)
SET @toId= @fromId + @pgSize -1

-- select the page of records
SELECT * FROM #ProductsPage
WHERE (RecordID BETWEEN @fromId AND @toId)AND
(CategoryID = @catId OR @catId IS NULL OR @catId=-1)
GO
-- usp_GetProducts Page 0,10
---------------------------------------------------------------------------------
You will also need another procedures that returns total number of records
in your products table.

2)then I suggest for simplisity, to add a dropdownlist to your DataList
header or any where in the page, fill this dropdownlist with numbers from
1
to total number of pages which should be
pages = totalNumOfRecor ds / pageRecordsCoun t
increase the pages by 1 if the totalNumOfRecor ds Mod pageRecordsCoun t is
greater that 0

3)Now when the user change the page from the dropdownlist, you need to
submit the page number along with the page size to the above stored
procedure.

I wish I could make clearer.. Please contact me by your e-mail and I'll
submit a full example to you if you wish.

Reagrds

[Patrick.O.Ige]

VB P
See a sample here at:-
http://www.dotnetjohn.com/articles.aspx?articleid=48
PAtrick

"VB Programmer" <do**********@s omewhere.comwro te in message
news:ue******** ******@TK2MSFTN GP02.phx.gbl...

Thanks. I'll look this over!

"Muhammad Mosa" <Muhammad Mo**@discussion s.microsoft.comwrote in message
news:2D******** *************** ***********@mic rosoft.com...

>Here is an example:
1) A stored procedure that gets page of records from products table in
Northwind database
--------------------------------------------------------------------------------------
ALTER PROC usp_GetProducts Page
(
@pgNdx int,
@pgSize int,
@catId int = NULL
)
AS
-- create a temporary table with the columns we are interested in
CREATE TABLE #ProductsPage
(
RecordID int IDENTITY PRIMARY KEY,
ProductID int,
ProductName nvarchar(40),
UnitPrice money,
UnitsInStock smallint,
CategoryName nvarchar(15),
CompanyName nvarchar(40),
QuantityPerUni t nvarchar(20),
UnitsOnOrder smallint,
ReorderLevel smallint,
Discontinued bit,
CategoryId int,
SupplierId int
)
-- fill the temp table with all the employees
INSERT INTO #ProductsPage
(
ProductID,
ProductName,
UnitPrice,
UnitsInStock ,
CategoryName ,
CompanyName,
QuantityPerUni t,
UnitsOnOrder ,
ReorderLevel ,
Discontinued ,
CategoryID,
SupplierID
)
SELECT ProductID,Produ ctName,UnitPric e,UnitsInStock, CategoryName,
CompanyName,Qu antityPerUnit,U nitsOnOrder,Reo rderLevel,Disco ntinued,
CategoryID,Sup plierID
FROM vw_ProductsList
WHERE CategoryID = @catId OR @catId IS NULL OR @catId=-1
ORDER BY ProductID

-- declare two variables to calculate the range of records
-- to extract for the specified page

DECLARE @fromId int
DECLARE @toId int
SET @fromId= (@pgNdx * @pgSize)
SET @toId= @fromId + @pgSize -1

-- select the page of records
SELECT * FROM #ProductsPage
WHERE (RecordID BETWEEN @fromId AND @toId)AND
(CategoryID = @catId OR @catId IS NULL OR @catId=-1)
GO
-- usp_GetProducts Page 0,10
---------------------------------------------------------------------------------
You will also need another procedures that returns total number of
records
in your products table.

2)then I suggest for simplisity, to add a dropdownlist to your DataList
header or any where in the page, fill this dropdownlist with numbers from
1
to total number of pages which should be
pages = totalNumOfRecor ds / pageRecordsCoun t
increase the pages by 1 if the totalNumOfRecor ds Mod pageRecordsCoun t is
greater that 0

3)Now when the user change the page from the dropdownlist, you need to
submit the page number along with the page size to the above stored
procedure.

I wish I could make clearer.. Please contact me by your e-mail and I'll
submit a full example to you if you wish.

Reagrds