Solved

ADO ResultSet pagination problems

Posted on 2004-08-06
7
863 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
Technology Partners: 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!

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Time Conversions...both ways 2 24
ASP exit 10 26
Validating Date Part2 2 27
Help with Classic ASP - Parameterizing Query 16 21
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 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