pee_cee
asked on
ADO ResultSet pagination problems
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.CursorLocatio n = 3; //adUseClient
pagedResults.CursorType = 3; //adOpenStatic
pagedResults.ActiveConnect ion = oConn;
pagedResults = oConn.Execute(SQLQueryStri ng);
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.AbsolutePosit ion;
Response.write(pagedResult s.Absolute Position);
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!
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
pagedResults.CursorLocatio
pagedResults.CursorType = 3; //adOpenStatic
pagedResults.ActiveConnect
pagedResults = oConn.Execute(SQLQueryStri
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.AbsolutePosit
Response.write(pagedResult
if(intPage == intPageCount){
intFinish = numberOfRecords
}
else{
intFinish = intStart + (pagedResults.PageSize - 1)
}
}
if(debug==true){
Response.write("intStart:"
}
--------------------------
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!
ASKER
thats the site i was using to base my code on, and i have used
pagedResults.CursorLocatio n = 3; //adUseClient
pagedResults.CursorType = 3; //adOpenStatic
which i believe are the correct cursor settings.
is this right?
pagedResults.CursorLocatio
pagedResults.CursorType = 3; //adOpenStatic
which i believe are the correct cursor settings.
is this right?
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.CursorLocatio n = 3; //adUseClient
pagedResults.CursorType = 3; //adOpenStatic
In order to assign the rs type you have to use pagedResults.open.
var pagedResults = Server.CreateObject("ADODB
pagedResults.CursorLocatio
pagedResults.CursorType = 3; //adOpenStatic
In order to assign the rs type you have to use pagedResults.open.
Bingo!
ASKER
apologies i forgot this info:
many lines earlier i opened the connection using
var oConn = Server.CreateObject("ADODB .Connectio n");
var strConn = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta 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.
many lines earlier i opened the connection using
var oConn = Server.CreateObject("ADODB
var strConn = "Provider=Microsoft.Jet.OL
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that got it.
thanks.
In jscript:
pagedResults.Open(SQLQuery String, oConn, 3, 3);
it had got to the point i was confusing myself!!
thanks.
In jscript:
pagedResults.Open(SQLQuery
it had got to the point i was confusing myself!!
Usually that is due to CursorType, and probably causing your problem.
See here for paging:
www.asp101.com/articles/recordsetpaging/index.asp