asked on

How do I page through a recordset using a next and previous button?

I have an asp webpage attached to a MS Access database. I want to display 20 records (images) per page in rows of five. I have a next and previous button at the top of the page, which are rollover images. These are used to page through the recordset. The page is coded using Javascript.

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="65001"%>
<!--#include file="fso.asp" -->
 
<%
var Conn=Server.CreateObject("ADODB.Connection");
var stringConnect = "Driver={Microsoft Access Driver (*.mdb)};" + 
           "DBQ=" + Server.MapPath("/TestSite/Test01/dbase/stock.mdb") ;
Conn.Open(stringConnect);
 
 
  sql = "SELECT * FROM Prod INNER JOIN ProdTypes ON Prod.TypeID = ProdTypes.TypeID WHERE Prod.Available=True ";
  rs = Conn.Execute(sql);  
%>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Test: Paging</title>
</head>
 
<script type="text/javascript">
<!--
function mouseOver1()
{
document.getElementById("next").src ="images/btn_next_over.jpg";
}
function mouseOut1()
{
document.getElementById("next").src ="images/btn_next_up.jpg";
}
function mouseOver()
{
document.getElementById("previous").src ="images/btn_previous_over.jpg";
}
function mouseOut()
{
document.getElementById("previous").src ="images/btn_previous_up.jpg";
}
 
//-->
</script>
 
<body>
<table width="1003" height="542" border="0" align="center" cellpadding="0" cellspacing="0" bgcolor="#006600">
  <tr>
    <td height="39">&nbsp;</td>
    <td align="right" valign="top"><table width="140" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td valign="top"><img src="images/spacer_18.gif" width="1" height="12" /></td>
        <td>&nbsp;</td>
        <td valign="top">&nbsp;</td>
      </tr>
      <tr>
        <td valign="top"><a href="products_list.asp"><img src="images/btn_previous_up.jpg" id="previous" width="79" height="24" border="0" onmouseover="mouseOver()" onmouseout="mouseOut()" /></a></td>
        <td><img src="images/spacer_21.gif" width="15" height="1" /></td>
        <td valign="top"><a href="products_list.asp"><img src="images/btn_next_up.jpg" id="next" width="46" height="24" border="0" onmouseover="mouseOver1()" onmouseout="mouseOut1()" /></a></td>
      </tr>
    </table></td>
    <td width="57">&nbsp;</td>
  </tr>
  <tr>
    <td width="74"><img src="images/spacer_19.gif" width="64" height="1" /></td>
    <td width="882" valign="top"><table width="882" border="0" cellspacing="0" cellpadding="0">
<%
var rowsize = 0;
         while (!rs.EOF) {
%>
<% if (rowsize == 0) {%>
      <tr>
	  <% } %>
        <td align="center" valign="top">
        <table width="136" height="112" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td valign="top"><a href="products_list.asp?TypeID=<%= rs("TypeID") %>"><img src="<%= prodImageFile(rs("ProdID"), rs("Image"),"") %>" width="136" height="84" border="0" alt="<%=rs("Type")%>" /></a></td>
          </tr>
          <tr>
            <td height="28"><%=rs("Type")%></td>
          </tr>
        </table></td>
<% var id = new Number(rs("TypeID"));
   while ((!rs.EOF) && (id.valueOf() == rs("TypeID"))) rs.MoveNext();
%>
<% rowsize ++ ;
   if (rowsize == 5) rowsize = 0;
   if (rowsize == 0) { %>
      </tr><% } 
   } %>
    </table></td>
    <td><img src="images/spacer_20.gif" width="57" height="1" /></td>
  </tr>
</table>
</body>
</html>

Open in new window

Pravin Asar

Are you using ASPX ?

You may look at

http://msdn.microsoft.com/en-us/library/aa479350.aspx

LLC0706

ASKER

I'm using basic asp, not aspx or asp.net

Emad Gawai

ref
http://www.aspin.com/home/tutorial/database/display/pagingre
or
http://www.asp101.com/samples/db_paging.asp

LLC0706

ASKER

I have added paging to the page, but this appears under the images. I want it to appear above the images in the table row marked "place page navigation here". This is probably very obviuos but I'm doing something wrong as when i try to move it it doesn't display.

<body>
<table width="1003" border="0" cellspacing="0" cellpadding="0" align="center" bgcolor="#FFFFFF">
  <tr> 
    <td colspan="3" valign="top">"place page navigation here"&nbsp;</td>
  </tr>
  <tr> 
    <td width="64" valign="top"></td>
    <td valign="top">
<%
  sqrs = null;
 
  // paging
  var page = 0;
  var PageSize=10;
  if (Request.QueryString("Page")>=0) page=new Number(Request.QueryString("Page"));
 
  var start = page*PageSize, end = start + PageSize, records = 0;
 
 
  var sq = "SELECT * FROM Items WHERE Available=TRUE AND TypeID=" + Request.QueryString("TypeID") + " ORDER BY Name";
  var rs = Conn.Execute(sq);
 
%>
 
      <table width="882" border="0" cellspacing="0" cellpadding="0">
<%  
  var items = 0; 
 
  while (!rs.EOF) {
    if ((records >= start) && (records < end)) { 
%> 
 
<%   if (items == 0) { %> <tr align="center">  <% } %>
 
          <td align="left"> 
            <table width="160" border="0" cellspacing="0" cellpadding="0">
              <tr> 
                <td><a href="products_detail.asp?ItemID=<%=rs("ItemID")%>"><img src="<%= itemImageFile(rs("ItemID"), rs("Image"),"") %>" width="160" border="0" alt="<%= rs("Name")%>"></a>                </td>
              </tr>
              <tr> 
                <td height="20"><a href="products_detail.asp?ItemID=<%=rs("ItemID")%>"><font color="#000000"><%= rs("Name")%></font></a></td>
              </tr>
            </table></td>
 
<%   items ++;
     if (items == 5) items = 0;
     if (items == 0) { %>  </tr> <% } 
    }
     records++;
     rs.MoveNext;
   }
%>
 
<% 
  var loc;
  loc = "products.asp?TypeID="+tid;
 
%>
        <tr align="center"> 
          <td colspan="2" align="left"> 
            <table width="95" border="0" cellspacing="0" cellpadding="0" height="25">
              <tr> 
<% if (page>0) { %>
                <td width="58" ><a href="<%= loc+"&Page="+(page-1)%>"><font color="#000000"><small>previous</small></font></a></td>
<% } %>
 
<%   if ((page+1)*PageSize < records  ) { %>
                <td width="37" ><a href="<%= loc+"&Page="+(page+1)%>"><font color="#000000"><small>next</small></font></a></td>
<%  } %>
              </tr>
            </table></td>
        </tr>
      </table></td>
    <td width="57" valign="top">&nbsp;</td>
  </tr>
</table>
</body>
</html>

Open in new window

Wayne Barron

Something like this (Untested)
Should work, just simply changing the code around and making sure that everything stays together.
(I hope that I got everything to stay together)

Good Luck
Carrzkiss

<body>
<table width="1003" border="0" cellspacing="0" cellpadding="0" align="center" bgcolor="#FFFFFF">
  <tr> 
    <td colspan="3" valign="top">"place page navigation here"&nbsp;</td>
  </tr>
  <tr> 
    <td width="64" valign="top"></td>
    <td valign="top">
<%
  sqrs = null;
 
  // paging
  var page = 0;
  var PageSize=10;
  if (Request.QueryString("Page")>=0) page=new Number(Request.QueryString("Page"));
 
  var start = page*PageSize, end = start + PageSize, records = 0;
 
 
  var sq = "SELECT * FROM Items WHERE Available=TRUE AND TypeID=" + Request.QueryString("TypeID") + " ORDER BY Name";
  var rs = Conn.Execute(sq);
 
%>
 
      <table width="882" border="0" cellspacing="0" cellpadding="0">
<%  
  var items = 0; 
 
  while (!rs.EOF) {
    if ((records >= start) && (records < end)) { 
%> 
 
<%   if (items == 0) { %> <tr align="center">  <% } %>
 
<%   items ++;
     if (items == 5) items = 0;
     if (items == 0) { %>  </tr> <% } 
    }
     records++;
     rs.MoveNext;
   }
%>
 
<% 
  var loc;
  loc = "products.asp?TypeID="+tid;
 
%>
 
          <td align="left"> 
          <table width="95" border="0" cellspacing="0" cellpadding="0" height="25">
              <tr> 
<% if (page>0) { %>
                <td width="58" ><a href="<%= loc+"&Page="+(page-1)%>"><font color="#000000"><small>previous</small></font></a></td>
<% } %>
 
<%   if ((page+1)*PageSize < records  ) { %>
                <td width="37" ><a href="<%= loc+"&Page="+(page+1)%>"><font color="#000000"><small>next</small></font></a></td>
<%  } %>
              </tr>
            </table>
            <table width="160" border="0" cellspacing="0" cellpadding="0">
              <tr> 
                <td><a href="products_detail.asp?ItemID=<%=rs("ItemID")%>"><img src="<%= itemImageFile(rs("ItemID"), rs("Image"),"") %>" width="160" border="0" alt="<%= rs("Name")%>"></a>                </td>
              </tr>
              <tr> 
                <td height="20"><a href="products_detail.asp?ItemID=<%=rs("ItemID")%>"><font color="#000000"><%= rs("Name")%></font></a></td>
              </tr>
            </table></td>
 
 
        <tr align="center"> 
          <td colspan="2" align="left"> 
            </td>
        </tr>
      </table></td>
    <td width="57" valign="top">&nbsp;</td>
  </tr>
</table>
</body>
</html>

Open in new window

Wayne Barron

I would like to add one more thing here if I may?

Code snippet
===========
var sq = "SELECT * FROM Items WHERE Available=TRUE AND TypeID=" + Request.QueryString("TypeID") + " ORDER BY Name";
===========
Using the wildcard *
Is fine when dealing with small queries that do not return to many records.
But, since you are doing a paging system that means that you are returning a LOT of records.
So this routine needs to be performing quickly and without lag.
So.
Define each field that is going to be displayed to the page.
Example Statement
===========
var sq = "SELECT Available, TypeID, Name FROM Items WHERE Available=TRUE AND TypeID=" + Request.QueryString("TypeID") + " ORDER BY Name";
===========

What happens using the Wildcard that most people do not take into consideration is that
When a query is made against the database for a record, let's say for: Name
The Query will have look through the database table to find the [Field=Name] and then display the information to the page.
This takes up valuable server resources regardless of how fast the server is, this is a bad thing.
To where if you add in all the [Field] Names to the Statement, then when you run a Query against the Database, it will know where to look, and this puts less strain on your Server, and makes for a quicker response time.

Anyway.
Just thought that I would pass that onto you, and to anyone else that might like to know this information.
(A majority of the sample codes that are available for download (Excluding mine) use the Wildcard *
For the table.Names
This is done to make the writing of the sample faster for the developer.
But is not good practice in a productive environment)

Carrzkiss

LLC0706

ASKER

Hi
I tried that but it doesn't work. I get an error message on this line:
<td><a href="products_detail.asp?ItemID=<%=rs("ItemID")%>"><img src="<%= itemImageFile(rs("ItemID"), rs("Image"),"") %>" width="160" border="0" alt="<%= rs("Name")%>"></a>

Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Wayne Barron

I have tried compiling the code that you have provided here and it is just way to buggy.
Did you get this from a site? If so, then please provide the URL to it, and I will check it out
And see about fixing to meat your needs.

Carrzkiss

LLC0706

ASKER

It's code from an existing set of three pages that's been given to me to work on, but I just cannot get on with it, hense posting the question here. The page displays product images from a database in rows of four. The page is set to a size of 20, and the remaining images are displayed on following pages.

Can you suggest alternative coding that would achieve the same result, either in javascript of vbscript?

Wayne Barron

Send me over a sample of your Database (Access) Along with some of the images
(Just about 5 images will be fine)
And I will put something together for you within a few hours, after I get some sleep.
It is 4:16am here.
1:16am where you are at.

Sleep Well.

Carrzkiss

LLC0706

ASKER

I've sent a sample of the Items table and the ItemsType table from the database and some sample images. In the fields that have text in them I've just written "text", to simplify matters.
It's 9.55am where I am.
sample.zip

LLC0706

ASKER

I've sent the wrong images. I've attached the correct ones.
StockImages.zip

ASKER CERTIFIED SOLUTION

Wayne Barron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wayne Barron

hello LLC0706
How is this going?
The demo that I provided does what you need.

If you need to change it from being:

1 2
3 4

to

1 2 3 4

That is done in the CSS. Q_24395918.asp Please read it.
#pagingcontainer
{/*Choose the size layout from here on how your want your images to be layed out on the page.*/
/*width:1500px;*/ /*1 row x 4 columns*/
width:500px;/*2 rows x 2 columns*/
}

It is not width:1500
Needs to be changed to: width:1000;

Let me know how things are going?

Carrzkiss

LLC0706

ASKER

Sorry for the delay in replying.
This works fine.
Can the next and previous buttons only be displayed when needed and not visible or greyed out on the page all the time?

So the next button will only appear when there is more than one page to view and not on the last page.
The previous button will only appear when there is more than one page to view and not on the first page.

Wayne Barron

Not really sure.
I have used this same script for a while now, and have used it on several commerical sites.
And have never been asked that question before, so I really do not know.
I am pretty sure that it can be, but that would be a different coding procedure.
And right now I do not have the time to dive into it and look on implementing something like that.

Sorry.
The only suggestion that I can give you, is to accept http:#24373499 as answer.
And repost the question asking if someone can maybe implement that for you.
And you can post my links in the question if you like for people to download and test with.

Carrzkiss

Wayne Barron

What is the deal with the [B]?
I answered your original question, you did not specify the issue with showing and showing buttons
In your original questions.

That is not right.
This should have been an [A] to much work.

Carrzkiss