ASP - Access Database paging links <prev 1234......

techskool · Jan 2, 2003

Hi Guys.

Still relatively new to programming, Ive nearly finished my first asp page.

It just displays some records from an access database, and Im trying to write code to page the information.

ideally I would like <prev 1 2 3 * * * * next>

but at the moment the code just does PREV & NEXT, and even that is causing a problem. It displays the same 5 records as I cycle through the next link??



<%@ Language = "VBScript"%>
<%
'Declare all local variables

'connections
dim conn
dim rs
dim strconn
dim sql

'page numbering
dim currentPage
dim NumRows

'set a local variable to my DSN-less connection String
strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("recordsnorthwest.mdb&quot

'Create the Connection object
set conn = server.createobject("adodb.connection&quot

conn.open strconn

sql = "SELECT * FROM stock"

'Create the recordset object
set rs = server.createobject("adodb.recordset&quot

'This statement opens the table so we can add a record notice the addnew
'The 2, 2 is how the table is opened
rs.open sql, conn, 3
rs.PageSize=5

%>

<html>
<head>
<title>View Database Contents</title>
</head>
<body>

<table >
<tr>
<th>Picture </th>
<th>Artist </th>
<th>Title </th>
<th>Label </th>
<th>Comment </th>
<th>Category </th>
<th>Year Made </th>
<th>Price </th>
<th>Mp3 </th>
<th>Cost </th>
<th>DateLogged </th>
<th>InStock </th>
<th>Owner </th>
</tr>

<%
NumRows=0
Do Until RS.EOF OR NumRows>=RS.PageSize
%>
<tr>
<td><img src=

http://<%=RS("Picture")%>></td>

<td> <%=RS("Artist&quot

%> </td>
<td> <%=RS("Title&quot

%> </td>
<td> <%=RS("Label&quot

%> </td>
<td> <%=RS("Comment&quot

%> </td>
<td> <%=RS("Category&quot

%> </td>
<td> <%=RS("YearMade&quot

%> </td>
<td> <%=RS("Price&quot

%> </td>
<td><a href="

http://<%=RS("Mp3")%>">Audio</a></td>

<td> <%=RS("Cost&quot

%> </td>
<td> <%=RS("DateLogged&quot

%> </td>
<td> <%=RS("InStock&quot

%> </td>
<td> <%=RS("Owner&quot

%> </td>
</tr>

<%
RS.MoveNext
NumRows=NumRows+1
Loop
%>

</table>
<br>
<a href="default.asp">Return</a> to main menu

<br>
<br>


<div align=center>
<%
If currentPage>1 Then
%>
<a href="query.asp?page=<%=currentPage-1%>">Prev</a>
<%
End If
%>
<%
If currentPage<RS.PageCount Then
%>
<a href="query.asp?page=<%=currentPage+1%>">Next</a>
<%
End If
%>
</div>
</body>
</html>

If someone could help suggest how I can get this working, or even include numbers in between the prev and next links (like google uses) that would be awesome.

Ive been pulling my hair out.

MANY THANKS

Dave

tty0 · Jan 3, 2003

Hi there,

The page you need is as follows, I have added a few bits in and changed some of the code to suit but what you have intended is there. It will give you a set of links at the bottom of the page which allows you to go to the first and last record in the set as well as move forwards and backwards.

I have put in a query variable so you can use it to pass a query through o the recordet. If you wish just change the SQL statement to something like

STRSql = “SELECT * from stock WHERE Artist=’” & query & “’”

That’s should pull up the query ok if an artist name was passed through.

If you need anything nesle just shout up
cheers.

'****************************************************
<%@language=vbscript%>
<%option explicit

Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
dim strSQL, objRS, I, icount, records
Dim objConnection

objConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="
objConnection = objConnection & Server.MapPath("\recordsnorthwest.mdb &quot

records = request.querystring("records&quot

set objRS = server.CreateObject("ADODB.Recordset&quot

objRS.PageSize = 6
objRS.CacheSize = 300
objRS.CursorLocation = adUseClient

query = ""
strSQL = ""
icount = ""

'take all the form details and store them in the variables
query = Request.querystring("query&quot

strSQL = "SELECT * FROM stock"

objRS.Open strSQL, objConnection, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect
%>

<html>
<head>
<title>Paging</title>
</head>
<body bgcolor="#ffffff">
<table width="600" border="0" cellspacing="0" cellpadding="0" align="center">
<tr>
<td width="58" height="37"></td>
<td bgcolor="#f7ca73" height="37" width="157"><img src="IMAGES/Header.jpg" width="151" height="50"></td>
<td bgcolor="#f7ca73" height="37" class="header">
<div align="right">HEADER</div>
</td>
<td width="39" height="37"></td>
</tr>
<tr>
<td width="58" valign="top"></td>
<td valign="top" colspan="2"><%if objRS.EOF and objRS.BOF then
response.write("Your search has returned no results, please re search the database using a different criteria.&quot

else
'***************************************
'start the loop in the recordset here
'***************************************
If Len(request.querystring("records&quot

) = 0 then
objRS.AbsolutePage = 1
Else
If CInt(request.querystring("records&quot

) <= objRS.PageCount then
objRS.AbsolutePage = request.querystring("records&quot

Else
objRS.AbsolutePage = 1
End if
End if

Dim abspage, pagecnt

abspage = objRS.AbsolutePage
pagecnt = objRS.PageCount

response.write("<TABLE WIDTH='100%' cellpadding=0 cellspacing=0 border=0>&quot

Dim Fields
For I = 1 to objRS.PageSize
If Not objRS.EOF Then
'write the table data onto the page with the recordset details.
%>
<tr>
<td><img src=

http://<%=RS("Picture")%>></td>

<td> <%=RS("Artist&quot

%> </td>
<td> <%=RS("Title&quot

%> </td>
<td> <%=RS("Label&quot

%> </td>
<td> <%=RS("Comment&quot

%> </td>
<td> <%=RS("Category&quot

%> </td>
<td> <%=RS("YearMade&quot

%> </td>
<td> <%=RS("Price&quot

%> </td>
<td><a href="

http://<%=RS("Mp3")%>">Audio</a></td>

<td> <%=RS("Cost&quot

%> </td>
<td> <%=RS("DateLogged&quot

%> </td>
<td> <%=RS("InStock&quot

%> </td>
<td> <%=RS("Owner&quot

%> </td>
</tr>

<%
objRS.movenext
end if
Next

'***************************************
'end the loop in the recordset here
'***************************************
response.write("</table>&quot

end if

objRS.close
set objRS = nothing

%>
</td>
<td width="39"></td>
</tr>
<tr>
<td width="58" height="2"></td>
<td height="2" class="footer" bgcolor="#ffffff" colspan="2">
<div align="right">
<%
'********************************************************
'put the navigation links on the bottom of the page here
'********************************************************

Response.Write "<div align='right'>" & vbcrlf
Response.Write "<a href='"
Response.Write "search.asp"
Response.Write "?records=1&query="& request.querystring("query&quot

& "'>First Page</a>"
Response.Write " | "

If abspage = 1 Then
Response.Write "<span style='color:silver;'>Previous Page</span>"
Else
Response.Write "<a href='" & Request.ServerVariables("SCRIPT_NAME&quot

Response.Write "?records=" & abspage - 1 & "& query =" & request.querystring("query &quot

& "'>Previous Page</a>"
End If

Response.Write " | "
If abspage < pagecnt Then
Response.Write "<a href='" & Request.ServerVariables("SCRIPT_NAME&quot

Response.Write "?query =" & request.querystring("query&quot

& "&records=" & abspage + 1 & "'>Next Page</a>"
Else
Response.Write "<span style='color:silver;'>Next Page</span>"
End If

Response.Write " | "
Response.Write "<a href='" & Request.ServerVariables("SCRIPT_NAME&quot

Response.Write "?query=" & request.querystring("query&quot

& "&records=" & pagecnt & "'>Last Page</a>"
Response.Write "</div>" & vbcrlf

'********************************************************
'end the navigation links on the bottom of the page here
'********************************************************
%></div>
</td>
</tr>
<tr>
<td width="58" height="2"></td>
<td height="2" class="footer" bgcolor="#f7ca73" colspan="2">
</td>
</tr>
<tr>
<td width="58" height="2"></td>
<td height="2" bgcolor="#f7ca73" colspan="2">

</td>
</tr>
</table>
</body>
</HTML>
'mi casa es su casa'
]-=tty0=-[
ICQ:82621399

techskool · Jan 4, 2003

Thanks so much tty0.

I havent implemented it yet, Im going to today, but I understand where you are coming from, I think.

Thanks again

Dave

tty0 · Jan 4, 2003

Your welcome, to see it in action try this:

http://www.mousemortgages.co.uk

and do a select all search to get a decent amount of data.

Any probs just shout up
cheers 'mi casa es su casa'
]-=tty0=-[
ICQ:82621399

techskool · Jan 4, 2003

Im going to use that script on part of my site, yet theres another paging script ive tried to include for a different part of the site, that I cant get working.

Ive written an asp page that searches a caterogy of music genre on my access database.

When the results are returned , Artist Title, Comments etc, I would like to be able to page through the results.

I have included the code for one of my query pages.

It works, but must have an error in it, and I cannot for the life of me, figure it out.

It should display all records up to 5 per page, and add next and previous links at the bottom of the page, if more than 5 records have been found, and in the case I am testing I know it should return 10 records!

The strange thing is lets say I successfully query 10 records.

Only 5 are returned, however their is no next button?

I change the constant iPageSize=5 to iPageSize=6 or 7 or 8, and it is the same.

However if I change iPageSize=5 to iPageSize=4 it still displays the 5 records from the first page, only now it has a next button that takes me to a second page with one record on, a duplicate of the last record from the first page!

Also if I change iPageSize=5 to iPageSize=3 it still displays the 5 records from the first page, again it has a next button that takes me to a second page with one record on, this time a duplicate of the last two records from the first page!

I am absolutely confused.

I am looking at the loop, but I cant see any errors, as it all views fine.

If anyone can offer some advise, and have a look at this code, it would be smashing.

Its prety well anotated (i think)

CODE
-----------------------

<%
dim conn
dim rs
dim strconn
dim sql
dim searchTerm

Const iPageSize=5 'How many records To show
Dim CPage 'Current Page No.
Dim TotPage 'Total No. of pages if iPageSize records are displayed per page.
Dim i 'Counter

'Paging
CPage=Cint(Request.Form("CurrentPage&quot

) 'get CPage value from form's CurrentPage field
Select Case Request.Form("Submit&quot

Case "Previous" 'if prev button pressed
CPage = Cint(CPage) - 1 'decrease current page
Case "Next" 'if next button pressed
CPage = Cint(CPage) + 1 'increase page count
End Select

'set a local variable to my DSN-less connection String
strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("recordsnorthwest.mdb&quot

'Create the Connection object
set conn = server.createobject("adodb.connection&quot

conn.open strconn

searchTerm="Old Skool"
'Create the recordset object
set rs = server.createobject("adodb.recordset&quot

sql="SELECT * FROM stock WHERE Category LIKE '%"&searchTerm&"%'"
rs.open sql, conn, 3

'paging
Rs.PageSize=iPageSize
if CPage=0 Then CPage=1 'initially make current page = first page
if Not(Rs.EOF) Then Rs.AbsolutePage=CPage 'specifies that current record resides In CPage
TotPage=Rs.PageCount 'stores total no. of pages

%>

<head><title>Search For Old Skool</title>
<link href="css/main.css" rel="stylesheet" type="text/css">
</head>
<body>
<div align=center><img src="images/recordsnorthwest_logo.jpg" width="422" height="34"></div>
<h2>Old Skool Search</h2>

<table border="1" >
<tr>
<th>Picture </th>
<th>Artist </th>
<th>Title </th>
<th>Label </th>
<th>Comment </th>
<th>Year Made </th>
<th>Price </th>
<th>Mp3 </th>
</tr>

<%
For i=1 To Rs.PageSize
DO Until RS.EOF
%>
<tr align="center">
<td><img src="<%=RS("Picture&quot

%>"></td>
<td> <%=RS("Artist&quot

%> </td>
<td> <%=RS("Title&quot

%> </td>
<td> <%=RS("Label&quot

%> </td>
<td> <%=RS("Comment&quot

%> </td>
<td> <%=RS("YearMade&quot

%> </td>
<td> <%=RS("Price&quot

%> </td>
<td><a href="<%=RS("Mp3&quot

%>"><img src=images/mp3_button.gif border=0></a></td>
</tr>

<%
Rs.MoveNext
loop
%>

<%
if Rs.EOF Then Exit For
Next
'close all connections and recordsets
Rs.Close
Conn.Close
Set Rs = Nothing
Set Conn = Nothing
%>

</table>
<br>


<br><br><br>
<a href="default.asp">Cancel</a> back to menu

<br><br>
Page <%=CPage %> of <%=TotPage %><P>

<FORM Action="<%=Request.ServerVariables("SCRIPT_NAME&quot

%>" Method=POST>

<INPUT Type=Hidden name="CurrentPage" Value="<%=CPage%>" >
<% if CPage > 1 Then %>
<INPUT type=Submit Name="Submit" Value="Previous">
<% End if%>
<% if CPage <> TotPage Then %>
<INPUT type=Submit Name="Submit" Value="Next">
<% End if %>
</FORM>
</body>
</html>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

ASP - Access Database paging links <prev 1234......

techskool

Technical User

tty0

Programmer

techskool

Technical User

tty0

Programmer

techskool

Technical User

Similar threads

Part and Inventory Search

Sponsor

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

ASP - Access Database paging links &lt;prev 1234......

techskool

Technical User

tty0

Programmer

techskool

Technical User

tty0

Programmer

techskool

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor

ASP - Access Database paging links <prev 1234......