Solved

Need Help with Paging through record set - AbsolutePage is forbidden on the server

Posted on 2006-07-01
9
364 Views
Last Modified: 2008-02-20
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

%>
0
Comment
Question by:foreverdita
9 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17024094
Just search for "paging" in this Topic Area and you will get hundreds of solutions.
0
 

Author Comment

by:foreverdita
ID: 17024097
I did, however, the ones I found that looked like what I wanted to do all used AbsolutePage.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17024111
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.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:foreverdita
ID: 17024127
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.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17024161
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17024165
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.
0
 
LVL 20

Expert Comment

by:Gawai
ID: 17024188
0
 
LVL 11

Accepted Solution

by:
JohnModig earned 500 total points
ID: 17026255
In classic ASP I personally like using the getrows function to collect the results from db and put them into an array this method is very fast (and much better than using .movenext) and then it is easy to page thru the array. Code should look something like this:
------------------------------------------------

'----------------------------------
'  Get the records
'----------------------------------
public sub getCategories
 sql = "SELECT getdetails " & _
 "FROM properties " & _
 "WHERE criteria = '" & criteria1 & "'"
 set rs = Server.CreateObject("ADODB.Recordset")
 rs.Open sql,conn
 'dump records in a multi-dimensional array(col,row)
 arrResults = rs.getrows
 'close recordset
 rs.close
 set rs=nothing
end sub

'-------------------------
'  Record paging
'-------------------------
 dim iStart, iOffset, txtOffset
 iStart = request.querystring("start")
 iOffset = request.querystring("offset")
 if Not IsNumeric(iStart) or Len(iStart) = 0 then
   iStart = 0
 else
   iStart = CInt(iStart)
 end if
 if Not IsNumeric(iOffset) or Len(iOffset) = 0 then
   iOffset = 10
 else
   iOffset = Cint(iOffset)
 end if
 dim iRows, iRowLoop, iStop
 iRows = UBound(arrResults, 2)
 if iRows => (iOffset + iStart) then
   iStop = iOffset + iStart - 1
 else
   iStop = iRows
 end If

'-------------------------
'  Create table for the results
'-------------------------
Body = "<table>" & vblf & _
"<tr><th>Getdetails</th></tr>" & vblf
'loop thru the results
for iRowLoop = iStart to iStop
  Body = Body & "<tr>" & vblf & _
  "<td>" & arrResults(0,iRowLoop) & "</td>" & _
  "</tr>" & vblf
next
Body = Body & "</table>" & vblf

'-------------------------
'  Text for navigating
'-------------------------
Body = Body & "<p>Displaying record " & iStart + 1 & " to " & iStop + 1 & " of " & uBound(arrArticles,2) + 1 & " total. " & vblf
  if iStart > 0 then
    'previous page link
    Body = Body & "| <a href=""mypage.asp?start=" & iStart-iOffset & "&amp;offset=" & iOffset & _
    """>Previous page</a>" & vblf
  end if
  if iStop < iRows then
    'next page link
    Body = Body & " | <a href=""mypage.asp?start=" & iStart+iOffset & "&amp;offset=" & iOffset & _
    """>Next page</a>" & vblf
  end if

'print everything
response.write Body
------------------------------------------------

Ok? Try this out and if you need more fields, simply specify them in the sql of the recordset (line 6):

  sql = "SELECT field1, field2, field3 "  ...

... and then use respectively column of the array to display the results, like this:
 
  " field1=" & arrResults(0,iRowLoop) & " field2=" & arrResults(1,iRowLoop) & " field3=" & arrResults(2,iRowLoop)

Remember arrays always start with 0 (zero) and not with 1.

Why getrows?:
http://www.learnasp.com/freebook/asp/whygetrows.aspx

Regards,
John
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17027176
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 ...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with stored procedure 30 66
replace quotes with UTF-8 character 38 98
If-Then-Else ASP problem 6 63
Sudden decrease in performance when updating mysql using classic asp 6 23
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question