foreverdita
asked on
Need Help with Paging through record set - AbsolutePage is forbidden on the server
I usually use absolutepage to page through my results - however, on this server, it is not allowed.
I need a way to page through, count records and add page numbers to the top and bottom of results.
I want to display the total records found at the top like "There were X peoperties that matched your criteria", then display 10 records per page - here is a code example that I already have in place. Can someone help?
<%
rs.open "select * from properties where criteria = '"&criteria1&"'",conn,3,3
while not rs.eof
getdetails = rs("getdetails")
%>
The table and all the details goes here
<%
rs.movenext
wend
rs.close
%>
I need a way to page through, count records and add page numbers to the top and bottom of results.
I want to display the total records found at the top like "There were X peoperties that matched your criteria", then display 10 records per page - here is a code example that I already have in place. Can someone help?
<%
rs.open "select * from properties where criteria = '"&criteria1&"'",conn,3,3
while not rs.eof
getdetails = rs("getdetails")
%>
The table and all the details goes here
<%
rs.movenext
wend
rs.close
%>
Anthony Perkins
Just search for "paging" in this Topic Area and you will get hundreds of solutions.
ASKER
I did, however, the ones I found that looked like what I wanted to do all used AbsolutePage.
I came up with over 4000 hits in this Topic Area alone using the following search criteria:
Title: Contains All 'paging'
Everything: Does Not Contain 'absolutepage'
But probably your best choice if AbsolutePage is "not allowed" (this rule seems insane, was there any reason given) is to save the recordset to an array using the GetRows method and keeping tabs of where you are within the recordset.
A probably more important question is what database are you using, as the answer will no doubt be influenced by that.
Title: Contains All 'paging'
Everything: Does Not Contain 'absolutepage'
But probably your best choice if AbsolutePage is "not allowed" (this rule seems insane, was there any reason given) is to save the recordset to an array using the GetRows method and keeping tabs of where you are within the recordset.
A probably more important question is what database are you using, as the answer will no doubt be influenced by that.
ASKER
It is sqlserver 2000
Yes, I searched through about 6 pages before posting my question and tried 4 examples of a GetRow function - they all seem to use some type of absolute. I just tried one that did not use absolutepage, said it used getrow and then used a absoluteposition, which gave me the same error.
Security was the reason given by the provider and there is no ifs ands or buts they said - they won't allow it.
The arrays seemed like they would work, but it used the absoluteposition in both examples I found. There was another example using javascript, but I would like to avoid that if possible.
I am still searching as well, but thought it would help if I posted and someone knew of a solution while I tried to find one as well - because each time I code out an example and it fails, I get more frustrated.
Yes, disallowing absolute seems absurd to me - I have never done it any differently.
Yes, I searched through about 6 pages before posting my question and tried 4 examples of a GetRow function - they all seem to use some type of absolute. I just tried one that did not use absolutepage, said it used getrow and then used a absoluteposition, which gave me the same error.
Security was the reason given by the provider and there is no ifs ands or buts they said - they won't allow it.
The arrays seemed like they would work, but it used the absoluteposition in both examples I found. There was another example using javascript, but I would like to avoid that if possible.
I am still searching as well, but thought it would help if I posted and someone knew of a solution while I tried to find one as well - because each time I code out an example and it fails, I get more frustrated.
Yes, disallowing absolute seems absurd to me - I have never done it any differently.
This is how I would do it: Write a stored procedure that accepts the parameters to search, as well as the page size, page index and optionally sort by and sort direction. In addition it will have an output parameter of the total row count.
Your stored procedure will then query the tables and insert all the rows into a variable of type table that has a structure that matches the query with an additional column: ID which has an IDENTITY. The insert will give you the total row count (@@ROWCOUNT) and the ID is used to only return the number of rows desired. This should give you an idea:
Declare @StartID integer,
@EndID integer
-- Your Insert query goes here
Set @RowCount = @@ROWCOUNT
If (@PageIndex Is Null Or @PageSize Is Null)
Begin
Set @StartID = 0
Set @EndID = @RowCount
End
Else
Begin
Set @StartID = @PageIndex * @PageSize + 1
Set @EndID = (@PageIndex + 1) * @PageSize
End
Select t.Column1,
t.Column2,
t.Column3,
t.Column4,
t.Column5,
t.Column6,
...
t.Columnn
From @Temp t
Where t.ID Between @StartID And @EndID
Order By t.ID
One caveat, in your ASP code you must close the recordset, before you attempt to read the Output parameter (@RowCount). The best way to accomplish this is to use GetRows, close the recordset and then read @RowCount.
Your stored procedure will then query the tables and insert all the rows into a variable of type table that has a structure that matches the query with an additional column: ID which has an IDENTITY. The insert will give you the total row count (@@ROWCOUNT) and the ID is used to only return the number of rows desired. This should give you an idea:
Declare @StartID integer,
@EndID integer
-- Your Insert query goes here
Set @RowCount = @@ROWCOUNT
If (@PageIndex Is Null Or @PageSize Is Null)
Begin
Set @StartID = 0
Set @EndID = @RowCount
End
Else
Begin
Set @StartID = @PageIndex * @PageSize + 1
Set @EndID = (@PageIndex + 1) * @PageSize
End
Select t.Column1,
t.Column2,
t.Column3,
t.Column4,
t.Column5,
t.Column6,
...
t.Columnn
From @Temp t
Where t.ID Between @StartID And @EndID
Order By t.ID
One caveat, in your ASP code you must close the recordset, before you attempt to read the Output parameter (@RowCount). The best way to accomplish this is to use GetRows, close the recordset and then read @RowCount.
You will still have to keep track of the PageIndex as the goes to the next or previous page, but this is just an integer and can be easily saved in a Session variable.
how about asp .net paging ?
http://www.asp101.com/samples/db_paging_aspx.asp
http://www.codeproject.com/aspnet/PagingLarge.asp
this too
http://www.4guysfromrolla.com/webtech/041206-1.shtml
http://www.asp101.com/samples/db_paging_aspx.asp
http://www.codeproject.com/aspnet/PagingLarge.asp
this too
http://www.4guysfromrolla.com/webtech/041206-1.shtml
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The problem with that approach with large data sets is that you have to return all the data every time, this would imply a heavy network performance hit. Of course, if you are using the same server to host the IIS server as well as the database, you do not have these network issues, but then you have bigger problems ...