Solved

ADO ResultSet pagination problems

Posted on 2004-08-06
7
868 Views
Last Modified: 2012-06-22
Hi

I'm using a MS Access db with ASP on IIS on windows 2000.
I'm trying to add pagination to my result display but i'm having lots of little problems.

heres the offending code section:
-------------------------------------------------------------------------------
var intPageCount            // The number of pages in the recordset.
      var numberOfRecords = 0            // The number of records in the recordset.
      var intPage            = 0      //The current page that we are on.
      var intRecord                  // Counter used to iterate through the recordset.
      var intStart                  // The record that we are starting on.
      var intFinish                  //The record that we are finishing on.

var pagedResults = Server.CreateObject("ADODB.Recordset");
      pagedResults.CursorLocation = 3; //adUseClient
      pagedResults.CursorType = 3; //adOpenStatic
      pagedResults.ActiveConnection = oConn;

      pagedResults = oConn.Execute(SQLQueryString);

pagedResults.PageSize = 10;

pagedResults.CacheSize = 10 //pagedResults.PageSize;
intPageCount = pagedResults.PageCount;

//as -- intRecordCount = pagedResults.RecordCount; -- does not work (returns -1) do:

if(!pagedResults.EOF){

            //how many results? RecordCount returns -1. seems to not work

            pagedResults.movefirst;
            while(!pagedResults.EOF){
            pagedResults.movenext;
                  numberOfRecords++;
            }
}

      // check to make sure not before the start or beyond end of the recordset.
      //If beyond the end, set the current page equal to the last page of the recordset.
      //If before the start, set the current page equal to the start of the recordset.
      if(intPage > intPageCount){intPage = intPageCount};
      if(intPage <= 0){intPage = 1};

// Make sure that the recordset is not empty.
//If it is not, then set the AbsolutePage property and populate the intStart and the intFinish variables.


      if(numberOfRecords > 0){
            pagedResults.AbsolutePage = intPage;
            intStart = pagedResults.AbsolutePosition;

            Response.write(pagedResults.AbsolutePosition);

            if(intPage == intPageCount){
                  intFinish = numberOfRecords
            }
            else{
                  intFinish = intStart + (pagedResults.PageSize - 1)
            }
      }


if(debug==true){
Response.write("intStart:" + intStart + " intFinish:" + intFinish);
}
----------------------------------------

apologies its a bit messy, i've been trying lots of different things.

The main problems are:

pagedResults.RecordCount returns -1
pagedResults.CacheSize = 10  gives ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Comment out pagedResults.CacheSize = 10 and then
pagedResults.AbsolutePage = intPage gives ADODB.Recordset (0x800A0CB3)
Current Recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype.


agghhhh i'm going crazeeee!


0
Comment
Question by:pee_cee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 31

Expert Comment

by:alorentz
ID: 11734534
>>pagedResults.RecordCount returns -1

Usually that is due to CursorType, and probably causing your problem.

See here for paging:
www.asp101.com/articles/recordsetpaging/index.asp
0
 
LVL 3

Author Comment

by:pee_cee
ID: 11734913
thats the site i was using to base my code on, and i have used

pagedResults.CursorLocation = 3; //adUseClient
pagedResults.CursorType = 3; //adOpenStatic

which i believe are the correct cursor settings.
is this right?
0
 
LVL 15

Expert Comment

by:joeposter649
ID: 11735653
I think the problem is you assign those values but then you reassign the rs to oConn.execute.  These lines may as well not even be there...
var pagedResults = Server.CreateObject("ADODB.Recordset");
     pagedResults.CursorLocation = 3; //adUseClient
     pagedResults.CursorType = 3; //adOpenStatic

In order to assign the rs type you have to use pagedResults.open.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 31

Expert Comment

by:alorentz
ID: 11735699
Bingo!
0
 
LVL 3

Author Comment

by:pee_cee
ID: 11736009
apologies i forgot this info:

many lines earlier i opened the connection using

      var oConn = Server.CreateObject("ADODB.Connection");
      var strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + database;
      oConn.Open(strConn);

and then did a couple of:
rs1 = oConn.Execute("SELECT DISTINCT textvalue FROM priority");
rs2 = oConn.Execute("SELECT DISTINCT textvalue FROM area");

and they all worked ok (not paginated).

do i need to close oConn and re-open or it? i assumed i'd be able to just execute again.
0
 
LVL 31

Accepted Solution

by:
alorentz earned 125 total points
ID: 11736097
No, it means when you do this:

pagedResults = oConn.Execute(SQLQueryString);


You are creating a whole new instance of pagedResults.  Would need to do this:

pagedResults.open SQLQueryString, oConn, 3, 3
0
 
LVL 3

Author Comment

by:pee_cee
ID: 11736363
that got it.
thanks.

In jscript:

pagedResults.Open(SQLQueryString, oConn, 3, 3);

it had got to the point i was confusing myself!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

724 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