How to page through a recordset one item at a time.

Your already passing the img ID through your main page, why make this more difficult?  Try this;

<%
imgID = Request("ImgID")
%>
<tr class="rslink">
    <td align="center"><a href="<%= "/largeimage.asp?"imgID - 1%> ">PREVIOUS</a>&nbsp;&nbsp;&nbsp;&nbsp; <a href="="<%= "/largeimage.asp?"imgID + 1%>">NEXT</a></td>
  </tr>

But why the database need?  Are you storing the imges in the dB?

Hello, mgfranz.  Thank you for your reply.  Your example looks very similar to the concept I had in my head, but I didn't know how to get there.  I have a couple of questions, before I attempt to implement:

1)  Should the "imgID = Request("ImgID")" you listed above be "request.querystring("ImgID")", since it is coming across the URL?

2)  The "ImgID" is just an "autonumber" of the database records.  Would the " -1 " and " +1 " matter if the record ID's are not incremented by "1"?   (i.e.  The first record ID may be "3" and the next may be "7" due to deletes, etc.).

Yes, images are stored in the database.


Thank you so much,
Shane

To try to answer your questions;

1)  Should the "imgID = Request("ImgID")" you listed above be "request.querystring("ImgID")", since it is coming across the URL?

Request("ImgID") and Request.Querystring("ImgID") are the same thing, QueryString is the default Request parameter.

2)  The "ImgID" is just an "autonumber" of the database records.  Would the " -1 " and " +1 " matter if the record ID's are not incremented by "1"?   (i.e.  The first record ID may be "3" and the next may be "7" due to deletes, etc.).

This will cause an issue, what you will probably have to do is to actually capture the ImgID, then when you write the previous and next buttons you will need to actually find the actual record in the dB, I am not sure what the best way to do this would be.  It might be possible to create an array using GetRows() to grab all the ImgID's that are in the dB then use this data to create a "previous record" an da "next record" querystring.  

Thanks, mgfranz.  I realized that I am actually sorting based on a date that is entered (A little manipulation so the user can order the images by "Newest", in case he has to delete).  Would this simplify matters?

Check out this page, you should be able to modify the code to work on one record at a time easily enough.  http://www.codefixer.com/codesnippets/recordsetpaging.asp

Look into what the RecordSet properties MoveNext and MovePrevious do, they should be able to solve your problem.  Basically what you need to do is pass the ImgID to a picture page and use it as your default SQL call, then build the MovePrevious() and MoveNext calls based on this value.  

YES!  That is exactly what I need to do (Regarding the passed value being my default SQL, or starting point, on the image page).  I was having a difficult time articulating my objective, but you summed it up.

I will look at the link and let you know.

Thanks so much!
Shane

Good reading;

http://msdn2.microsoft.com/en-us/library/ms676810(VS.85).aspx
http://docs.sun.com/source/817-2514-10/Ch11_ADO96.html

>> I realized that I am actually sorting based on a date that is entered (A little manipulation so the user can order the images by "Newest", in case he has to delete).  Would this simplify matters?

Have you try the query I posted above? try amend to something like this:

SELECT yourTable.*,
(Select top 1 id from yourTable as temp1 where yourDate < yourTable.yourDate order by yourDate desc) as previousID,
(Select top 1 id from yourTable as temp2 where yourDate > yourTable.yourDate order by yourDate) as nextID
 FROM yourTable
Where id = <currentID>

Will this help?

Hi, mgfranz.  I will definitely read those tomorrow.

Hi, ryancys.  Yes, I looked at your example, but it is still a little cryptic to me.  I'm not fluent in SQL, so I need a little extra assistance.  I mean, I see where I am to replace your placeholder fields with my actual Tables and Fields; but can I just Copy-and-Paste everything else As-Is, or are there some other syntax elements not shown?  I've never seen SQL syntax like what you have displayed; but then again, I haven't seen much at all outside of the basics of SQL.  If you could be as exact with your example as possible, that would help me greatly.  Also, bear in mind I am entering this in the SQL dialog box of DWMX.


Thanks so much,
Shane

Perhaps you can let us know your table's structure and post your script here, so that we can try refer and amend from there.

Certainly ryancys.  Thank you.  I will attach the code from the "LargeImage" page.  It is a basic page, with a basic DWMX query to show the Larger Image of the "ID" being passed via the URL.  As mgfranz said above, my goal is to create a query that will let me view "Previous" and "Next" from whatever image is being displayed on the "LargeImage" page.  It just needs to be able to search for whatever the Previous, or Next, record is in the database.

Thank you so much,
Shane

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/connjk.asp" -->
<%
Dim rsImageLarge__MMColParam
rsImageLarge__MMColParam = "1"
If (Request.QueryString("imgID") <> "") Then 
  rsImageLarge__MMColParam = Request.QueryString("imgID")
End If
%>
<%
Dim rsImageLarge
Dim rsImageLarge_numRows
 
Set rsImageLarge = Server.CreateObject("ADODB.Recordset")
rsImageLarge.ActiveConnection = MM_connjk_STRING
rsImageLarge.Source = "SELECT * FROM images WHERE imgID = " + Replace(rsImageLarge__MMColParam, "'", "''") + ""
rsImageLarge.CursorType = 0
rsImageLarge.CursorLocation = 2
rsImageLarge.LockType = 1
rsImageLarge.Open()
 
rsImageLarge_numRows = 0
%>
<html>
<head>
<title>Jason Kelly Tattoos and Paintings</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="test/jkstyles.css" rel="stylesheet" type="text/css">
</head>
 
<body topmargin="0" leftmargin="0" marginheight="0" marginwidth="0" background="images/pageBG.gif">
<table width="450" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td align="center"><img src="<%=(rsImageLarge.Fields.Item("imgLarge").Value)%>" border="0" align="absmiddle"></td>
  </tr>
  <tr>
    <td><hr></td>
  </tr>
  <tr>
    <td><strong><font color="#FFFFFF"><%=(rsImageLarge.Fields.Item("imgDesc").Value)%></font></strong></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
  </tr>
  <tr class="rslink">
    <td align="center"><a href="#">PREVIOUS</a>&nbsp;&nbsp;&nbsp;&nbsp; <a href="#">NEXT</a></td>
  </tr>
</table>
</body>
</html>
<%
rsImageLarge.Close()
Set rsImageLarge = Nothing
%>
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:

>>try something like this, but you still need amend accordingly (table name, date field name, etc)

sorry, the table name is not need to change here as already been changed..

Hi, ryancys.  Sorry for the delay.  My hard drive crashed last weekend and I've spent all week installing another and reinstalling all my applications.  I'm just now going to be able to reconfigure all my sites tonight.  I hope to possibly try your latest suggestion(s) later tonight.

I'll let you know if that works OK.

(Guess I'll be "Ghosting" my HDD this time around).  :|

Thanks,
Shane

No prob Shane, I hope we could make some helps here : )

Hi, ryancys.  I tweaked the fields to match mine and then had to tweak the "<" and ">" operators to achieve the desired "Previous" and "Next" functions.   I think we're almost there, but a couple of things are happening.

I have 2 thumbnail pages (tattoos.asp and paintings.asp).
One table that contains all images [images].
[images] also contains a foreign field for a "Category" type: {imgCat}.

When clicking a thumb from either of the 2 pages, it passes the "imgID" via the URL to the "largeimage.asp" page.

It was working fine when all had were images of one category type "1" in the table; HOWEVER, as soon as I added other images with a different category type "2" it gave me the following message:
=======================================================
Error Type:
Microsoft JET Database Engine (0x80004005)
At most one record can be returned by this subquery.
/jasonkelly/largeimage.asp, line 29
=======================================================
I have attached that code.  Line "29" is this:  "rsImageLarge.Open()"
I pasted Lines 1 - 36.

If I remove all images with one or the other category types (leaving only one type in the table), then it displays fine again.

I tried creating a separate page for the main image of "paintings" (using your same code), but it gives the same problem.  It's almost like if it sees more that one Category type in that table [images], it flags the error.

Any ideas?

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/connjk.asp" -->
<%
Dim rsImageLarge__MMColParam
rsImageLarge__MMColParam = "1"
If (Request.QueryString("imgID") <> "") Then 
  rsImageLarge__MMColParam = Request.QueryString("imgID")
End If
%>
<%
Dim rsImageLarge
Dim rsImageLarge_numRows
 
Set rsImageLarge = Server.CreateObject("ADODB.Recordset")
rsImageLarge.ActiveConnection = MM_connjk_STRING
 
SQLStr = "SELECT images.*, " & _
	"(Select top 1 imgID from images as temp1 where imgDate > images.imgDate order by imgDate) as previousID, " & _
	"(Select top 1 imgID from images as temp2 where imgDate < images.imgDate order by imgDate desc) as nextID " & _
	"FROM images " & _
	"Where imgID = " & rsImageLarge__MMColParam
 
rsImageLarge.Source = SQLstr
 
 
rsImageLarge.CursorType = 0
rsImageLarge.CursorLocation = 2
rsImageLarge.LockType = 1
rsImageLarge.Open()
 
//response, etc if no record was found
if rsImageLarge.eof then response.redirect "err.asp?msg=norecord"
 
 
rsImageLarge_numRows = 0
%>

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:

Hi, ryancys.  OK - I figured out the conflict.  All images are in the one table [images], but different Category types can be assigned to each image (i.e. "1" or "2", etc.).  However, since your query is based on DATE, if I have "3/1/2008" assigned for an image with a Category type of "1" and then have "3/1/2008" assigned to an image with a Category type of "2", it conflicts.

How can we tweak your code to work as it does, but additionally to only display based on the URL parameter of "imgCat"?

Hi there,

How's your latest Query looks like? Can you post it here?

And can you list the fields in your table "images" ?

Hi, ryancys.  Thank you - you've been most helpful.  Yes, I'll post the info below:

1) Table [images] fields:  {imgID, imgLarge, imgCat, imgDesc, imgDate}
2) I will attach the snippet of the latest query.

I guess the tweak should pretty much accomplish the following:

The 2 pages "tattoos.asp" and "paintings.asp" have Category types of "1" and "2", respectively.

When clicking on the "tattoos.asp" page, there is a recordset that displays all records with an "imgCat" equal to "1" - and the same for the "paintings.asp" page displaying records with an "imgCat" equal to "2".

You click on a thumbnail and it takes you to the main "largeimage.asp" page where your query kicks in.
With the query you provided (attached), it allows the selected thumbnail to be the default image and then allows you to scroll "Previous" by DATE Descending and "Next" by DATE Ascending.

That works great, except when there is another record with the same DATE.  I tried changing all the "imgDate" fields to "imgID" to try the same thing, but in that instance, it scrolled through ALL the images --- whether Category "1" or "2".

I guess firstly the "Large Image" should be displayed based on the thumbnail selection from the previous page, and then should scroll "Previous" and "Next" with the added criteria of "imgCat".  I could just append the "imgCat=1" or "imgCat=2" to the thumbnail link and then do some kind of "request.querystring("imgCat")" query, or something, but I'm getting lost at that point.  I tried several things like that, but not quite achieving the goal.

Let me know if you need anything else from me.

Thanks so much!
Shane

<%
Dim rsImageLarge__MMColParam
rsImageLarge__MMColParam = "1"
If (Request.QueryString("imgID") <> "") Then 
  rsImageLarge__MMColParam = Request.QueryString("imgID")
End If
%>
<%
Dim rsImageLarge
Dim rsImageLarge_numRows
 
Set rsImageLarge = Server.CreateObject("ADODB.Recordset")
rsImageLarge.ActiveConnection = MM_connjk_STRING
 
SQLStr = "SELECT images.*, " & _
	"(Select top 1 imgID from images as temp1 where imgDate > images.imgDate order by imgDate) as previousID, " & _
	"(Select top 1 imgID from images as temp2 where imgDate < images.imgDate order by imgDate desc) as nextID " & _
	"FROM images " & _
	"Where imgID = " & rsImageLarge__MMColParam
 
rsImageLarge.Source = SQLstr
 
 
rsImageLarge.CursorType = 0
rsImageLarge.CursorLocation = 2
rsImageLarge.LockType = 1
rsImageLarge.Open()
 
//response, etc if no record was found
if rsImageLarge.eof then response.redirect "err.asp?msg=norecord"
 
rsImageLarge_numRows = 0
%>

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:

Hi, you may try this:

<%
Dim imgID, imgCat

imgID = Request.QueryString("imgID")
imgCat = Request.QueryString("imgCat")

Dim rsImageLarge
Dim rsImageLarge_numRows
 
Set rsImageLarge = Server.CreateObject("ADODB.Recordset")
rsImageLarge.ActiveConnection = MM_connjk_STRING
 
SQLStr = "SELECT images.*, " & _
        "(Select top 1 imgID from images where imgID < " & imgID & " and imgCat = " & imgCat & " order by imgID desc) as previousID, " & _
        "(Select top 1 imgID from images where imgID > " & imgID & " and imgCat = " & imgCat & " order by imgID)  as nextID " & _
        "FROM images " & _
        "Where imgID = " & imgID

rsImageLarge.Source = SQLstr
 
 
rsImageLarge.CursorType = 0
rsImageLarge.CursorLocation = 2
rsImageLarge.LockType = 1
rsImageLarge.Open()
 
//response, etc if no record was found
if rsImageLarge.eof then response.redirect "err.asp?msg=norecord"
 
rsImageLarge_numRows = 0
%>



then your hyperlink should be something like:

yourpage.asp?imgID=5&imgCat=2

this query order by imgID now, however think it's not perfect solution, but this should able to resolve your problem.

Hi, ryancys.  Yeah - this does still create a problem.  I guess the only way to truly use your first solution and avert this problem is to just have 2 tables:  [tattoo_images] and [painting_images] and just insert the images accordingly in the INSERT forms.

Your first solution worked great by DATE, but the Category issue was causing a problem.  Since the client will only have the 2 Categories, having 2 tables seems like a simple solution.

Would that sound logical to you?

In fact, I tested above and it should works.. at least for my test database..

>>Would that sound logical to you?
Your images data should reside in a single table..

can you post a copy of your database somewhere on internet, so that we can refer to it? or you can email (zipped) directly to me (you can find my email from my profile..)

Hi, ryancys.  Thank you.  I zipped and E-mailed the database to you.

Thank you,
Shane

Hi, ryancys.  I went ahead and awarded the points because your solution accomplished the goal 100%.

I know typically I would have all images in one table, but for this one, since these are the only 2 categories the client will have, this was a simple solution and I just modified and applied your solution to 2 separate pages, as opposed to one... and it works great.

Thanks so much,
Shane