[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

ADO ResultSet pagination problems

Posted on 2004-08-06
7
Medium Priority
?
876 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
Industry Leaders: 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 500 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

656 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