What's the best method to do paging in my ASP page when displaying a list of items?
I knew that there is no equivalent to MySQL's LIMIT clause present in SQL Server, either 2000 or 2005.
How can I retrieve some particular records (Records 20 to 30) from the DB?
Do i need to fetch all the records from the database and do something in my ASP page to display the required records?
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