displaying 10 records per page in Jscript

[sono]

I have an asp page with like 100 records but i want to display 5 or 10 records at a time with a "Next" and "Previous". I had a go at translating a vbscript to Jscript but its not working. I put both versions below.

VBscript version -

<table>
<!--#INCLUDE VIRTUAL="/includes/connection.asp" -->

<%
DIM mySQL, objRS
mySQL = "SELECT * FROM tblCustomerInfo"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorType = 1
objRS.Open mySQL, objConn

DIM intPageRecords, intRecords, intRecordCount, intCurrentPage
DIM intNumberOfPages, intDisplayPage
intPageRecords = Request.Querystring("page")
IF intPageRecords = "" THEN intPageRecords = 1 : intRecords = 1
intRecords = intPageRecords
intPageRecords = ((intPageRecords - 1) * 10) +1
intRecordCount = 0

IF NOT objRS.EOF THEN
objRS.Move (intPageRecords - 1)
DO WHILE intRecordCount < 10 and NOT objRS.EOF
%>
<tr><td><%=objRS("Email")%> - <%=objRS("Name")%></td></tr>
<%
objRS.MoveNext
intRecordCount = intRecordCount +1
Loop
END IF
%>
</table>

<%=intPageRecords%> - <%=intPageRecords+(intRecordCount-1)%> of <%=(objRS.RecordCount)%> customers
<p>Scroll Through More Customers
<%
intCurrentPage = Request.Querystring("page")
IF intCurrentPage = "" THEN intCurrentPage = 1
intNumberOfPages = int(objRS.RecordCount \ 10)
IF objRS.RecordCount MOD 10<> 0 THEN intNumberOfPages = intNumberOfPages + 1
Response.Write("Pages: [")
FOR intDisplayPage = 1 TO intNumberOfPages
IF Cint(intDisplayPage) = Cint(intCurrentPage) THEN
Response.Write " <b>" & intDisplayPage & "</b> "
ELSE
Response.Write " <a href=""news.asp?page=" & intDisplayPage & """>" & intDisplayPage &_
"</a> "
END IF
NEXT
Response.Write ("]")
%>


JScript Version

<table>
<!--#INCLUDE VIRTUAL="/includes/connection.asp" -->

<%
var mysql, objRS
mySQL = "SELECT * FROM tblCustomerInfo"
objRS = Server.CreateObject("ADODB.Recordset");
objRS.CursorType = 1
objRS.Open (mySQL), (objConn)

var intPageRecords, intRecords, intRecordCount, intCurrentPage
var intNumberOfPages, intDisplayPage
intPageRecords = Request.Querystring("page")
if intPageRecords = ""
{
intPageRecords = 1 : intRecords = 1
intRecords = intPageRecords
intPageRecords = ((intPageRecords - 1) * 10) +1
intRecordCount = 0
}

if (!objRS.EOF)
{
objRS.Move (intPageRecords - 1)
DO WHILE intRecordCount < 10 && (!objRS.EOF)
}
%>
<tr><td><%=objRS("Email")%> - <%=objRS("Name")%></td></tr>
<%
objRS.MoveNext
intRecordCount = intRecordCount +1
Loop
}
%>
</table>

<%=intPageRecords%> - <%=intPageRecords+(intRecordCount-1)%> of <%=(objRS.RecordCount)%> customers
<p>Scroll Through More Customers
<%
intCurrentPage = Request.Querystring("page")
if intCurrentPage = ""
{
intCurrentPage = 1
intNumberOfPages = int(objRS.RecordCount \ 10)
}
if objRS.RecordCount MOD 10<> 0
{
intNumberOfPages = intNumberOfPages + 1
Response.Write("Pages: [")
FOR intDisplayPage = 1 TO intNumberOfPages
}
if Cint(intDisplayPage) = Cint(intCurrentPage)
{
Response.Write " <b>" & intDisplayPage & "</b> "
}
else
{
Response.Write " <a href=""news.asp?page=" & intDisplayPage & """>" & intDisplayPage &_
"</a> "
}
next
Response.Write ("]")
%>

[buntine]

Are you receiving an error? Is it not mandatory to end statements with a semi-colon in JScript?

[faamugol]

try this

http://www.codefixer.com/codesnippets/recordsetpaging.asp

[phpnovice]

Here's the first three things I can find wrong. This:

objRS.Open (mySQL), (objConn)

should be this:

objRS.Open(mySQL, objConn)

and this:

objRS.MoveNext

should be this:

objRS.MoveNext()

and this:

DO WHILE intRecordCount < 10 && (!objRS.EOF)

should be this:

do while (intRecordCount < 10 && !objRS.EOF)

[russell_g_1]

this works quite well too.

var sql = "select name,email from tblcustomerinfo order by name"

var rs = getrs(sql);

if (rs != null)
{

var perPage = 10;
var currentPage = cnum(Request("pageindex"));
var firstRecord = currentPage * perPage;

if (firstRecord > rs.recordcount)
{
currentPage = 0;
firstRecord = 0;
}

var lastRecord = firstRecord + perPage;
if (lastRecord > rs.recordcount)
lastRecord = rs.recordcount;

Response.write("Showing " + (firstRecord+1) + " to " + lastRecord + " out of " + rs.recordcount);

rs.move(firstRecord);

Response.write("<table border=1 cellpadding=5>");
Response.write("<tr><td>Name</td><td>Email</td></tr>");

var count = 0;

while(!rs.eof && count < 10)
{
Response.write("<tr><td>" + rs("name") + "</td><td>" + rs("email") + "</td></tr>");
rs.movenext();
count++;
}

Response.write("</table>");

var backPageIndex = currentPage - 1;
var nextPageIndex = currentPage + 1;

if (backPageIndex >= 0)
Response.write(getAnchor("Back",getAnchorURL("page1","pageindex=" + backPageIndex),"",""));

if (nextPageIndex * perPage < rs.recordcount)
Response.write(getAnchor("Next",getAnchorURL("page1","pageindex=" + nextPageIndex),"",""));

rs.close();
}

rs = null;

[phpnovice]

Technically, both of the methods demonstrated, though workable, don't accurately represent the true power of ADO Paging. The most accurate method for using ADO Paging encompases three steps. The first step is to establish the number of rows in a page before the record set is opened:

myRst.CursorLocation = 3;
myRst.PageSize = 5; //number of rows per page
myRst.Open(mySQL, myConn, 2, 3);
numPages = myRst.PageCount; //number of pages in record set

Then, you automatically position the record set to the beginning of the desired page:

myRst.AbsolutePage = curPage;

Lastly, you build out the page to the client:

while(!myRst.EOF && myRst.AbsolutePage == curPage)
{
// build HTML here
myRst.MoveNext();
}

[sono]

I get an error "object expected",


<%@ LANGUAGE="JAVASCRIPT" %>
<%

myconn=Server.CreateObject("ADODB.connection");
mydb = "Driver={Microsoft Access Driver (*.mdb)};DBQ=sts.mdb";
myconn.Open (mydb);
rs = Server.CreateObject("ADODB.Recordset");

var sql = "select Username, Password from tblTry order by Username"

var rs = getrs(sql); <-------- Error Line

if (rs != null)
{

var perPage = 10;
var currentPage = cnum(Request("pageindex"));
var firstRecord = currentPage * perPage;

if (firstRecord > rs.recordcount)
{
currentPage = 0;
firstRecord = 0;
}

var lastRecord = firstRecord + perPage;
if (lastRecord > rs.recordcount)
lastRecord = rs.recordcount;

Response.write("Showing " + (firstRecord+1) + " to " + lastRecord + " out of " + rs.recordcount);

rs.move(firstRecord);

Response.write("<table border=1 cellpadding=5>");
Response.write("<tr><td>Name</td><td>Email</td></tr>");

var count = 0;

while(!rs.eof && count < 10)
{
Response.write("<tr><td>" + rs("Username") + "</td><td>" + rs("Password") + "</td></tr>");
rs.movenext();
count++;
}

Response.write("</table>");

var backPageIndex = currentPage - 1;
var nextPageIndex = currentPage + 1;

if (backPageIndex >= 0)
Response.write(getAnchor("Back",getAnchorURL("page1","pageindex=" + backPageIndex),"",""));

if (nextPageIndex * perPage < rs.recordcount)
Response.write(getAnchor("Next",getAnchorURL("page1","pageindex=" + nextPageIndex),"",""));

rs.close();
}

rs = null;
%>

[phpnovice]

Originally posted by sono
I get an error "object expected",
Yep, he threw a function at you and didn't give you the code. :D

[russell_g_1]

that's because i used a function to open a connection to the db and return a recordset. replace it with your own recordset.open statement and it'll work.

i also used some functions at the bottom for the links that you'll need to replace. other than that it'll work though.

[russell_g_1]

this is what you'll need. not very exciting stuff. :-(

function getrs(sql)
{
var rs = Server.CreateObject("ADODB.Recordset");
try
{
rs.Open(sql, Application("connstr"), 1, 3);
}
catch(e)
{
Response.write("SQL Error : " + sql);
return null;
}
return rs;
}

function cnum(x)
{
var res = parseInt(x,10);
if (isNaN(res))
res = 0;
return res;
}

[sono]

hey russell, what do i replace with what


<%@ LANGUAGE="JAVASCRIPT" %>
<%

myconn=Server.CreateObject("ADODB.connection");
mydb = "Driver={Microsoft Access Driver (*.mdb)};DBQ=e:\\webareas\\sts.mdb";
myconn.Open (mydb);
rs = Server.CreateObject("ADODB.Recordset");

function getrs(sql)
{
var rs = Server.CreateObject("ADODB.Recordset");
try
{
rs.Open(sql, Application("connstr"), 1, 3);
}
catch(e)
{
Response.write("SQL Error : " + sql);
return null;
}
return rs;
}

function cnum(x)
{
var res = parseInt(x,10);
if (isNaN(res))
res = 0;
return res;
}


var sql = "select Username, Password from tblTry order by Username";

var rs = getrs(sql);

if (rs != null)
{

var perPage = 10;
var currentPage = cnum(Request("pageindex"));
var firstRecord = currentPage * perPage;

if (firstRecord > rs.recordcount)
{
currentPage = 0;
firstRecord = 0;
}

var lastRecord = firstRecord + perPage;
if (lastRecord > rs.recordcount)
lastRecord = rs.recordcount;

Response.write("Showing " + (firstRecord+1) + " to " + lastRecord + " out of " + rs.recordcount);

rs.move(firstRecord);

Response.write("<table border=1 cellpadding=5>");
Response.write("<tr><td>Name</td><td>Email</td></tr>");

var count = 0;

while(!rs.eof && count < 10)
{
Response.write("<tr><td>" + rs("Username") + "</td><td>" + rs("Password") + "</td></tr>");
rs.movenext();
count++;
}

Response.write("</table>");

var backPageIndex = currentPage - 1;
var nextPageIndex = currentPage + 1;

if (backPageIndex >= 0)
Response.write(getAnchor("Back",getAnchorURL("page1","pageindex=" + backPageIndex),"",""));

if (nextPageIndex * perPage < rs.recordcount)
Response.write(getAnchor("Next",getAnchorURL("page1","pageindex=" + nextPageIndex),"",""));

rs.close();
}

rs = null;
%>

[russell_g_1]

you can remove these lines from your code because this bit's done by the getrs function.

myconn=Server.CreateObject("ADODB.connection");
mydb = "Driver={Microsoft Access Driver (*.mdb)};DBQ=e:\\webareas\\sts.mdb";
myconn.Open (mydb);
rs = Server.CreateObject("ADODB.Recordset");

you'll need to replace Application("connstr") with your own connection string as well.

at the bottom the links in my example are generated by a couple of routines. just replace these with <a href= etc.

hopefully it might even work after that. :D

[sono]

ok russell, done what u said, still getting an error, look below in the bold writing.


<%@ LANGUAGE="JAVASCRIPT" %>
<%

function getrs(sql)
{
var rs = Server.CreateObject("ADODB.Recordset");
try
{
rs.Open(sql, Application("DBQ=e:\\webareas\\mw904\\prog\\sts.mdb"), 1, 3);
}
catch(e)
{
Response.write("SQL Error : " + sql);
return null;
}
return rs;
}

function cnum(x)
{
var res = parseInt(x,10);
if (isNaN(res))
res = 0;
return res;
}


var sql = "select Username, Password from tblTry order by Username";

var rs = getrs(sql);

if (rs != null)
{

var perPage = 10;
var currentPage = cnum(Request("pageindex"));
var firstRecord = currentPage * perPage;

if (firstRecord > rs.recordcount)
{
currentPage = 0;
firstRecord = 0;
}

var lastRecord = firstRecord + perPage;
if (lastRecord > rs.recordcount)
lastRecord = rs.recordcount;

Response.write("Showing " + (firstRecord+1) + " to " + lastRecord + " out of " + rs.recordcount);

rs.move(firstRecord);

Response.write("<table border=1 cellpadding=5>");
Response.write("<tr><td>Name</td><td>Email</td></tr>");

var count = 0;

while(!rs.eof && count < 10)
{
Response.write("<tr><td>" + rs("Username") + "</td><td>" + rs("Password") + "</td></tr>");
rs.movenext();
count++;
}

Response.write("</table>");

var backPageIndex = currentPage - 1;
var nextPageIndex = currentPage + 1;

if (backPageIndex >= 0)
Response.write(getAnchor("Back",getAnchorURL("<a href=""news.asp?page=" + backPageIndex),"","")); <-------- Error: Expected ')'

if (nextPageIndex * perPage < rs.recordcount)
Response.write(getAnchor("Next",getAnchorURL("<a href=""news.asp?page=" + nextPageIndex),"",""));

rs.close();
}

rs = null;
%>

[russell_g_1]

replace

Response.write(getAnchor("Back",getAnchorURL("<a href=""news.asp?page=" + backPageIndex),"",""));

with

Response.write("<a href=\"news.asp?page=" + backPageIndex + "\">Back</a>");

and do something similar with the next link.

[sono]

The browser displayed this: SQL Error : select Username, Password from tblTry order by Username

[russell_g_1]

did you replace the connection string with your access one?

[sono]

this is it, tell me if im wrong

<%@ LANGUAGE="JAVASCRIPT" %>
<%

function getrs(sql)
{
var rs = Server.CreateObject("ADODB.Recordset");
try
{
rs.Open(sql, Application("DBQ=e:\\webareas\\mw904\\prog\\sts.mdb"), 1, 3);
}
catch(e)
{
Response.write("SQL Error : " + sql);
return null;
}
return rs;
}

[russell_g_1]

yours should look like this.

rs.Open(sql, "DBQ=e:\\webareas\\mw904\\prog\\sts.mdb", 1, 3);

in my example i'm storing the connection string in the application object, whereas you're not.

[sono]

tried that, still it displays the sql statement.

[russell_g_1]

replace the

Response.write("SQL Error : " + sql);

with

Response.write("SQL Error : " + e.description + "<br>");
Response.write("SQL = " + sql);

and you'll get the error description. should help you see what's going wrong.

[russell_g_1]

just noticed that part of your connection string is missing. shouldn't it say

Driver={Microsoft Access Driver (*.mdb)};DBQ=e:\\webareas\\sts.mdb

?

[sono]

ok here is the error message

"Operation is not allowed when the object is closed."

and this is the line


if (firstRecord > rs.recordcount)

[russell_g_1]

and the getrs function looks like this?

function getrs(sql)
{
var rs = Server.CreateObject("ADODB.Recordset");
try
{
rs.Open(sql, "Driver={Microsoft Access Driver (*.mdb)};DBQ=e:\\webareas\\mw904\\prog\\sts.mdb", 1, 3);
}
catch(e)
{
Response.write("SQL Error : " + e.description + "<br>");
Response.write("SQL = " + sql);
return null;
}
return rs;
}

[sono]

it works!

but..

the 'next' button isn't working

it stays at 1 - 10, it doesnt go to 11 - 21

[russell_g_1]

is it definitely passing a pageindex value of 1 when you click on the next link?

[russell_g_1]

ah. i see the problem. its passing the new page index in a variable called page, but the asp code is trying to read in a value from pageindex. just change one or the other to get it going.

:D

[sono]

russell, you are the king!

[phpnovice]

I'm glad you got it going but, I think it bears repeating that both of the methods initially demonstrated, though workable, don't accurately represent the true power of ADO Paging. The most accurate method for using ADO Paging encompases three steps. The first step is to establish the number of rows in a page before the record set is opened:

myRst.CursorLocation = 3;
myRst.PageSize = 5; //number of rows per page
myRst.Open(mySQL, myConn, 2, 3);
numPages = myRst.PageCount; //number of pages in record set

Then, you automatically position the record set to the beginning of the desired page:

myRst.AbsolutePage = curPage;

Lastly, you build out the page to the client:

while(!myRst.EOF && myRst.AbsolutePage == curPage)
{
// build HTML here
myRst.MoveNext();
}

[russell_g_1]

yeah. the reason i did it using a separate counter was because i use a generic routine to execute queries and return a recordset instead of writing the rs.open bit every time. helps for error handling/reporting, otherwise i'd just end up writing the same bit of code over and over again. if you're not interested in handling db errors specifically then i agree that you should probably go down the route explained by phpnovice and use the features provided by ado.

[phpnovice]

Originally posted by russell_g_1
yeah. the reason i did it using a separate counter was because i use a generic routine to execute queries and return a recordset instead of writing the rs.open bit every time.
Hey, all you'd have to do is pass the value for the number of rows per page to your function -- no sweat. In fact, it would even handle no value passed to mean no paging. Haven't you heard of having your cake and eating it, too? :D

[sono]

for me the ADO way seems a bit confusing, i will have to look at it again, the method i used is very easy to understand and i can add and modify any part of it.

But i won't argue with you two, you know alot more than me.

[phpnovice]

I'm not arguing. Every programmer has the right to program the way they see fit. ;)
IMHO, programming is not a science -- it is an art.