PDA

View Full Version : Moving through Recordset

djfenom
11-02-2005, 03:59 PM
I am doing a Case Studies page for a site I am working on. At the moment I have an initial page, "case_studies.asp", which lists the studies coming out of the database, with just the date and title and this then links off to another page "case_studies_display.asp". On this page, you get to see the full case study and picture.

My problem is on the display page, I would like "next" and "previous" buttons to get to the next or previous case study along. I've tried using the paging in Dreamweaver but because I've called the study from a previous page (ie the page name is case_studies_display.asp?id=5), it's not working.

Here is my current code:

case_studies.asp

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/dearing.asp" -->
<%
Dim RScase
Dim RScase_numRows

Set RScase = Server.CreateObject("ADODB.Recordset")
RScase.ActiveConnection = MM_dearing_STRING
RScase.Source = "SELECT * FROM casestudies ORDER BY caseDate DESC"
RScase.CursorType = 0
RScase.CursorLocation = 2
RScase.LockType = 1
RScase.Open()

RScase_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
RScase_numRows = RScase_numRows + Repeat1__numRows
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>
function DoDateTime(str, nNamedFormat, nLCID)
dim strRet
dim nOldLCID

strRet = str
If (nLCID > -1) Then
oldLCID = Session.LCID
End If

On Error Resume Next

If (nLCID > -1) Then
Session.LCID = nLCID
End If

If ((nLCID < 0) Or (Session.LCID = nLCID)) Then
strRet = FormatDateTime(str, nNamedFormat)
End If

If (nLCID > -1) Then
Session.LCID = oldLCID
End If

DoDateTime = strRet
End Function
</SCRIPT>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Dearing</title>
<script src="sifr.js" type="text/javascript"></script>
<script src="sifr-addons.js" type="text/javascript"></script>
<link href="sifr.css" rel="stylesheet" type="text/css" />
<link href="dearing.css" rel="stylesheet" type="text/css" />
</head>
<body id="case">
<!--#include file="nav.asp" -->
<div class="line"><img src="images/line-left.gif" /></div>
<div class="line" id="lineright"><img src="images/line-right.gif" /></div>
<div id="container">
<div class="column" id="double">
<h2>Case Studies</h2>
<div id="scrollable">
<%
While ((Repeat1__numRows <> 0) AND (NOT RScase.EOF))
%>
<h3><%= DoDateTime((RScase.Fields.Item("caseDate").Value), 1, 2057) %></h3>
<p class="bigger"><a href="case_studies_display.asp?id=<%=(RScase.Fields.Item("caseID").Value)%>"><%=(RScase.Fields.Item("caseTitle").Value)%></a></p>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
RScase.MoveNext()
Wend
%>
</div>
</div>
<div class="column" id="imgcol">
<img src="images/design-image.jpg" alt="Design and Manufacturing Image" width="220" height="262" />
<h3>Automotive Industry </h3>
</div>
</div>
<!--#include file="sifr_include.asp" -->
</body>
</html>
<%
RScase.Close()
Set RScase = Nothing
%>

case_studies_display.asp

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/dearing.asp" -->
<%
Dim RScase__MMColParam
RScase__MMColParam = "1"
If (Request.QueryString("id") <> "") Then
RScase__MMColParam = Request.QueryString("id")
End If
%>
<%
Dim RScase
Dim RScase_numRows

Set RScase = Server.CreateObject("ADODB.Recordset")
RScase.ActiveConnection = MM_dearing_STRING
RScase.Source = "SELECT * FROM casestudies WHERE caseID = " + Replace(RScase__MMColParam, "'", "''") + ""
RScase.CursorType = 0
RScase.CursorLocation = 2
RScase.LockType = 1
RScase.Open()

RScase_numRows = 0

%>
<!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=iso-8859-1" />
<title>Dearing</title>
<script src="sifr.js" type="text/javascript"></script>
<script src="sifr-addons.js" type="text/javascript"></script>
<link href="sifr.css" rel="stylesheet" type="text/css" />
<link href="dearing.css" rel="stylesheet" type="text/css" />
</head>
<body id="case">
<!--#include file="nav.asp" -->
<div class="line"><img src="images/line-left.gif" /></div>
<div class="line" id="lineright"><img src="images/line-right.gif" /></div>
<div id="container">
<div class="column" id="double">
<h2>Case Studies</h2>
<div id="scrollable">
<h3><%=(RScase.Fields.Item("caseTitle").Value)%></h3>
<p>
<% Response.Write Replace(RScase.Fields.Item("caseContent"), vbCrLf, "<br>")%>
</p>
</div>
</div>
<div class="column" id="imgcol">
<div id="caseimg">
<% if (RScase("caseImg").Value) <> "" then %>
<%'***** Display picture *********************************%>
<%strPath = Server.URLEncode(Server.MapPath("case-images") & "\" & RScase("caseImg"))
set fso = createobject("scripting.filesystemobject")
if (fso.FileExists(Server.MapPath("case-images") & "\" & RScase("caseImg"))) then%>
<img src="sendbinary.asp?path=<%=strPath%>" />
<%
set fso = nothing
end if
'*******************************************************%>
<% end if %>
</div>
<ul>
<li class="nobg"><a href="#" title="previous article">&lt; prev</a></li>
<li><a href="case_studies.asp" title="back to case studies index">index</a></li>
<li><a href="#" title="next article">next &gt; </a></li>
</ul>
</div>
</div>
<!--#include file="sifr_include.asp" -->
</body>
</html>
<%
RScase.Close()
Set RScase = Nothing
%>

Thanks in advance.

Chris
djfenom
11-04-2005, 09:30 AM
I've still not worked this out, if anyone can help me it would be much appreciated.

Thanks
hinch
11-09-2005, 02:49 PM
Dont really have time to explain this in detail but you should be able to work it out

only ammendment you need todo is pull out your initial id=5 header var and ammend it onto the bottom of the page buttons in the table at the bottome of the script


<ul>


<%Set objRS = Server.CreateObject("ADODB.Recordset")
Dim iPageSize 'How big our pages are
Dim iPageCount 'The number of pages we get back
Dim iPageCurrent 'The page we want to show
Dim strOrderBy 'A fake parameter used to illustrate passing them
Dim objPagingConn 'The ADODB connection object
Dim objPagingRS 'The ADODB recordset object
Dim iRecordsShown 'Loop controller for displaying just iPageSize records
Dim I 'Standard looping var


iPageSize = 5

' Retrieve page to show or default to 1
If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If

' Build our SQL String using the parameters we just got.
strSQL = "select * from tblPressRelease where Live=1 and (dateposted like '%"&year(now)&"') order by dateposted desc"

objRS.PageSize = iPageSize

objRS.CacheSize = iPageSize

' Open RS
objRS.Open strSQL, Conn, adOpenStatic, adLockReadOnly, adCmdText

' Get the count of the pages using the given page size
iPageCount = objRS.PageCount

' If the request page falls outside the acceptable range,
' give them the closest match (1 or max)
If iPageCurrent > iPageCount Then iPageCurrent = iPageCount
If iPageCurrent < 1 Then iPageCurrent = 1

If iPageCount = 0 Then
%>
</ul>
There are no press releases available for this year.
<%
else
objRS.AbsolutePage = iPageCurrent

iRecordsShown = 0
Do While iRecordsShown < iPageSize And Not objRS.EOF
dateArr1= split(objRS("DatePosted"),"/")
%>
<li><strong><%=objRS("Title")%></strong><br />
<strong>Posted on: <%=dateArr1(0)&" "&MonthName(dateArr1(1),FALSE)&" "&dateArr1(2)%>&nbsp;&nbsp;</strong><br />
<%=objRS("bodycopy")%>
<br />
<a href="download/files/<%=objRS("AttachedFile")%>">Click here to read the full Press Release</a><br /><br />
</li>
<%' Increment the number of records we've shown
iRecordsShown = iRecordsShown + 1
' Can't forget to move to the next record!
objRS.MoveNext
Loop


while not objRS.eof
objRS.MoveNext
wend
end if
objRS.Close%>
</ul>
<table border="0" cellpadding="0" cellspacing="0" id="mainTextarea">
<tr><td align=center colspan="2">
<%If iPageCurrent > 1 Then%>
<a href="press.asp?page=<%= iPageCurrent - 1 %>">[&lt;&lt; Prev]</a>
<%End If%>
Page&nbsp;
<%For I = 1 To iPageCount
If I = iPageCurrent Then
%>
<%= I %>
<%Else%>
<a href="press.asp?page=<%= I %>"><%= I %></a>
<%End If
Next
If iPageCurrent < iPageCount Then%>
<a href="press.asp?page=<%= iPageCurrent + 1 %>">[Next &gt;&gt;]</a>
<%End If%></td></tr>
</table>
djfenom
11-09-2005, 04:31 PM
I sort of get it, but if the client deletes one of the case studies, then the id isn't always going to follow on. For example in this code the next button adds 1 to the id, but what if that record has gone and the next one in the order isn't just 1 more.

I need it so that the next button moves to the next record in the set, not just the current record + 1, so if the current record is 123, the next record might be 125 or 130, not necessarily 124. The same applies for previous.

Hope this makes sense.

Thanks
hinch
11-09-2005, 04:41 PM
in my code its simple paging where it implements the ado page count.

the id+1 in my code isnt referencing to the database id field but instead referencing to the page count.

i believe in my code i set the page count to be 5 records displayed per page (or perhaps 10) if you simply change this to 1 then it'll display 1 item at once with forwards and backwards buttons at bottom.
djfenom
11-10-2005, 09:59 AM
Hi Hinch, i'm struggling trying to implement the code you so kindly posted yesterday.

For now I've just added the table code to the bottom of my script just to test it, but I don't know how to display the iPageCount like you have. It's currently displaying "[<< Prev] Page 1 ", but the prev link is going to 4, and the previous record is actually 3. I know I'm missing something, I just can't work out what it is.

Here is my code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/dearing.asp" -->
<%
Dim RScase__MMColParam
RScase__MMColParam = "1"
If (Request.QueryString("id") <> "") Then
RScase__MMColParam = Request.QueryString("id")
End If
%>
<%
Dim RScase
Dim RScase_numRows

Set RScase = Server.CreateObject("ADODB.Recordset")
RScase.ActiveConnection = MM_dearing_STRING
RScase.Source = "SELECT * FROM casestudies WHERE caseID = " + Replace(RScase__MMColParam, "'", "''") + ""
RScase.CursorType = 0
RScase.CursorLocation = 2
RScase.LockType = 1
RScase.Open()

RScase_numRows = 0

iPageCount = 1
%>
<!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=iso-8859-1" />
<title>Dearing Plastics</title>
<script src="sifr.js" type="text/javascript"></script>
<script src="sifr-addons.js" type="text/javascript"></script>
<link href="sifr.css" rel="stylesheet" type="text/css" />
<link href="dearing.css" rel="stylesheet" type="text/css" />
</head>
<body id="case">
<!--#include file="nav.asp" -->
<div class="line"><img src="images/line-left.gif" /></div>
<div class="line" id="lineright"><img src="images/line-right.gif" /></div>
<div id="container">
<div class="column" id="double">
<h2>Case Studies</h2>
<div id="scrollable">
<h3><%=(RScase.Fields.Item("caseTitle").Value)%></h3>
<p>
<% Response.Write Replace(RScase.Fields.Item("caseContent"), vbCrLf, "<br>")%>
</p>
</div>
</div>
<div class="column" id="imgcol">
<div id="caseimg">
<% if (RScase("caseImg").Value) <> "" then %>
<%'***** Display picture *********************************%>
<%strPath = Server.URLEncode(Server.MapPath("case-images") & "\" & RScase("caseImg"))
set fso = createobject("scripting.filesystemobject")
if (fso.FileExists(Server.MapPath("case-images") & "\" & RScase("caseImg"))) then%>
<img src="sendbinary.asp?path=<%=strPath%>" />
<%
set fso = nothing
end if
'*******************************************************%>
<% end if %>
</div>
<ul>
<% if currentPage > 1 then %>
<li class="nobg"><a href="#" title="previous article">&lt; prev</a></li>
<% else %>
<li class="nobg"><a href="#" title="previous article">&lt; prev</a></li>
<% end if %>
<li><a href="case_studies.asp" title="back to case studies index">index</a></li>
<li><a href="#" title="next article">next &gt; </a></li>
</ul>
</div>
</div>
<table border="0" cellpadding="0" cellspacing="0" id="mainTextarea">
<tr><td align=center colspan="2">
<%If RScase__MMColParam > 1 Then%>
<a href="case_studies_display.asp?id=<%= RScase__MMColParam - 1 %>">[&lt;&lt; Prev]</a>
<%End If%>
Page&nbsp;
<%For I = 1 To iPageCount
If I = RScase__MMColParam Then
%>
<%= I %>
<%Else%>
<a href="case_studies_display.asp?id=<%= I %>"><%= I %></a>
<%End If
Next
If RScase__MMColParam < iPageCount Then%>
<a href="case_studies_display.asp?id=<%= RScase__MMColParam + 1 %>">[Next &gt;&gt;]</a>
<%End If%></td></tr>
</table>
<div id="footer">
<p>Dearing Plastics Limited | Annie Reed Court | Annie Reed Road | Beverley | HU17 0LF<br />
Tel: 01482 339509 | Fax: 01482 470255 | Email: <a href="mailto:info@dearingplastics.co.uk">info@dearingplastics.co.uk</a></p>
</div>
<!--#include file="sifr_include.asp" -->
</body>
</html>
<%
RScase.Close()
Set RScase = Nothing
%>

Thank you very much.