Whats the best method to do paging in my ASP page when displaying a list of items ?

I just want to add one more feature to Mr. Wheat s answer. Why not u are trying to use the Take () and Skip() feature of linq(obviously if u are using dotnet framework 3.5+)

It is indeed helpful while working with large datasets.

Have a look Using Take and Skip method in LINQ queries

I knew that there is no MySQL LIMIT clause present in SQL server(both 2000 and 2005 should support).how can i retrieve some particular records (Record 20 -30) from DB ?

You can do this in SQLSERVER 2005+ by using ranking function Row_Number() among other alternatives. A sample example is included herewith

First I am creating a dummy table and inserting some 50 records

declare @tbl table(name varchar(50),age int)
;with num_cte as
(   select 1 as rn
    union all
    select rn+1 from num_cte where rn<50
)  
insert @tbl
select  names ,rn + 20 ageval                
from num_cte
cross apply( select   name  + CAST(rn as varchar(2))  AS names) names
select * from @tbl

Now by using the Row_Number() function I am picking up records between 20 & 30

select name,age from(
select ROW_NUMBER()over (order by age) as rownum,name,age from @tbl) X
where X.rownum between 20 and 30

However, for achieving the same in SQL SERVER 2000 the below query will help

select name,age from(
select t1.name,t1.age,
(select count(*)+1 from @tbl where name<>t1.name and age<=t1.age) rownum
from @tbl t1
)X(name,age,rownum)
where rownum between 20 and 30

Please see:

• Paging In ASP.NET

• Data Access Tutorials

• GridView Examples for ASP.NET 2.0: Paging and Sorting the GridView s Data

• SQL Server 2005 Paging – The Holy Grail

Youd need to use ROW_NUMBER (SQL Server 2005+)

 SELECT * FROM
    (SELECT a.*, ROW_NUMBER() OVER (ORDER BY hire_date) rn
    FROM hr.employees AS OF TIMESTAMP (TIMESTAMP  2009-01-29 10:30:00 ) a)
 WHERE rn BETWEEN 10 AND 19

_{Related answer}

Using ROW_NUMBER, you are numering and sorting the inherently unsorted group (the table). Once you have an ordered set instead of just a set, you can now the sentence "I want all the rows from 10 to 19" makes sense.

You will have to use ASP code to keep both the upper and lower elements, so you can ask for the next or previous subset of rows to show.