Solved

ADO ResultSet pagination problems

Posted on 2004-08-06
7
862 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
  • 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
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
HTML in email body has extra  tick marks 3 87
SQL to update characters in table column 6 142
Using Classic ASP inside HTML pages 2 62
html Uncheck Checkbox 2 25
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…
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…

789 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