ADO ResultSet pagination problems


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:


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


      // 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;


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

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!

Who is Participating?
alorentzConnect With a Mentor Commented:
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: SQLQueryString, oConn, 3, 3
>>pagedResults.RecordCount returns -1

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

See here for paging:
pee_ceeAuthor Commented:
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?
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.

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
pee_ceeAuthor Commented:
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;

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.
pee_ceeAuthor Commented:
that got it.

In jscript:

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

it had got to the point i was confusing myself!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.