Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Recordset and asp

Author  Topic 

sonofmarzipan
Starting Member

7 Posts
Posted - 2005-09-02 : 05:10:37
Hi guys,

my first post. So please don't kill me!

I am having some problems. I'm pretty new to sql and really dont know how to achieve more than the basic selects etc.,

My problem is that I have a recordset on one page http://www.photoghetto.com/photo-images/animals.asp that returns the results of all the images in one category. In this case it's animals and wildlife.

The user can click on any image and go to a page that shows a larger detail version of the image. http://www.photoghetto.com/photo-images/animals-photo-detail.asp

What I do is post the ProductID number to this page so that the selected thumb is shown. So for exmaple for the image of the wild cat it is http://www.photoghetto.com/photo-images/animals-photo-detail.asp?ProductID=6

My problem is that on the animals-photo-detail.asp the user has to be able to "scroll" through all the images from the category.

I.e should be able to hit the previous image button and see the stallion image, or the next button to see the butterfly etc., and thus scroll through all the images on this age if he/ she wishes to.

I understand the principles of having a results page and then being able to click on one of the results and getting a detail page. Such as I have it here. With the http://www.photoghetto.com/photo-images/animals.asp as a results page listing all the results of the category, and then when the user clicks on one of the results, goes to a detail page, for example, http://www.photoghetto.com/photo-images/animals-photo-detail.asp?ProductID=6.

My problem is that what I need is the recordset from the listing page to function on the detail page so that the user can scroll through the results in the same order that they were on the results page.

I have searched now for a couple of days online and every tutorial I find shows the same structure. Results Page > Detail Page.

The sql i am using on the detail page is simply,

<%
Dim photos_rs__MMColParam
photos_rs__MMColParam = "1"
If (Request.QueryString("ProductID") <> "") Then
photos_rs__MMColParam = Request.QueryString("ProductID")
End If
%>

<%
Dim photos_rs
Dim photos_rs_numRows

Set photos_rs = Server.CreateObject("ADODB.Recordset")
photos_rs.ActiveConnection = MM_photo_STRING
photos_rs.Source = "SELECT * FROM PHOTOCOLLECTIONS WHERE ProductID = " + Replace(photos_rs__MMColParam, "'", "''") + ""
photos_rs.CursorType = 0
photos_rs.CursorLocation = 2
photos_rs.LockType = 3
photos_rs.Open()
photos_rs_numRows = 0
%>

So I understand why it will only display the one result since thats the detail page.

Is it possible to be able to scroll through the results using the previous and next buttons as I have setup in the display on the http://www.photoghetto.com/photo-images/animals-photo-detail.asp page. For example, http://www.photoghetto.com/photo-images/animals-photo-detail.asp=ProductID=6.

Since the resutls are gathered from across the database its not possible to have a href tage that does a <<< http://www.photoghetto.com/photo-images/animals-photo-detail.asp=ProductID=(6 -1) or a >>> http://www.photoghetto.com/photo-images/animals-photo-detail.asp=ProductID=(6+1).

I guess it has to be something with a recordset index but does anyone know how to do it? And does anyone have the ability to help me do it?

My boss is kicking my butt now to get this thing online at some point today, and I'm turning to you guys for help if possible.

I'm sorry if this is a stupid question. I've really ran out of ideas.

-SOM

Kristen
Test

22859 Posts
Posted - 2005-09-02 : 05:50:46
How about selecting the IDs of the next Higher and Lower records when you select the Detail data?

SELECT *,
       (SELECT TOP 1 ProductID 
        FROM PHOTOCOLLECTIONS 
        WHERE ProductID > 6 
        ORDER BY ProductID
       ) AS NextProductID,
       (SELECT TOP 1 ProductID 
        FROM PHOTOCOLLECTIONS 
        WHERE ProductID < 6 
        ORDER BY ProductID DESC
       ) AS PreviousProductID
FROM  PHOTOCOLLECTIONS 
WHERE ProductID = 6

(You would need to put your code for the Field Value in place of the red 6 above)

Note that you should not use SELECT * - that will retrieve all columns; change it to just the columns you need. Otherwise:

1) You will be retrieving columns you don't need and reducing performance
2) When you add half a dozen huge text columns to this table in the future it will serious wreck your existing application performance

Kristen
Go to Top of Page

sonofmarzipan
Starting Member

7 Posts
Posted - 2005-09-02 : 05:59:31
Hi Kristen,

firstly, thank you so much for taking the time to respond to my problem. It's really of you, and I wanted to say thank you first.

I copied over your code and I got a

Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/photo-images/animals-photo-detail.asp, line 37

backgrounds_rs.Source = "SELECT *,

error.

Did I do something wrong here?

-SOM
Go to Top of Page

sonofmarzipan
Starting Member

7 Posts
Posted - 2005-09-02 : 06:10:17
Hi again Kristen,

I'm sorry, it was my fault. I did warn you I was new. SO sorry for any confusion, I had to delete all the line spacing from the Select statement to get it onto one line then it work ok :)

But I guess thats actually my real problem. I need it to only select the results from a category, for example, 'Animals & Wildlife'

I have to be totally honest Kristen, your select statement is much more advanced than I've ever seen.

I'm sorry to annoy you further, but do you know how to add the select only from category='Animals & Wildlife' to the statement so that it will still work?

- SOM
Go to Top of Page

sonofmarzipan
Starting Member

7 Posts
Posted - 2005-09-02 : 06:28:46
Using the

SELECT *,
(SELECT TOP 1 ProductID
FROM PHOTOCOLLECTIONS
WHERE ProductID > 6
ORDER BY ProductID
) AS NextProductID,
(SELECT TOP 1 ProductID
FROM PHOTOCOLLECTIONS
WHERE ProductID < 6
ORDER BY ProductID DESC
) AS PreviousProductID
FROM PHOTOCOLLECTIONS
WHERE ProductID = 6


I tried adding the category into the select statement wth the AND kristen variable,


SELECT *, (SELECT TOP 1 ProductID FROM PHOTOCOLLECTIONS WHERE category='Animals & Wildlife' AND ProductID > "&kristen&" ORDER BY ProductID) AS NextProductID, (SELECT TOP 1 ProductID FROM PHOTOCOLLECTIONS WHERE category='Animals & Wildlife' AND ProductID < "&kristen&" ORDER BY ProductID DESC) AS PreviousProductID FROM PHOTOCOLLECTIONS WHERE ProductID = "&kristen&" AND category='Animals & Wildlife'


where

<%
Dim photos_rs__MMColParam
photos_rs__MMColParam = "1"
If (Request.QueryString("ProductID") <> "") Then
photos_rs__MMColParam = Request.QueryString("ProductID")
End If

Dim kristen
kristen = (photos_rs.Fields.Item("ProductID").Value)
%>


however, the sql statement seems to still select from all the database not the category='Animals & Wildlife' category, as you can see if you try

http://www.photoghetto.com/photo-images/animals-photo-detail.asp?ProductID=6

and scroll back a couple or forward a couple.

Any ideas?

-SOM
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-09-02 : 06:53:38
OK, so the NEXT and PREVIOUS ProductID have got to be for the SAME Category as the CURRENT record. Reverting to my original SQL it would need the following changes:

SELECT *,
       (SELECT TOP 1 P2.ProductID 
        FROM PHOTOCOLLECTIONS AS P2
        WHERE P2.ProductID > 6
              AND P2.category = P1.category
        ORDER BY P2.ProductID
       ) AS NextProductID,
       (SELECT TOP 1 P3.ProductID 
        FROM PHOTOCOLLECTIONS AS P3
        WHERE P3.ProductID < 6 
              AND P3.category = P1.category
        ORDER BY P3.ProductID DESC
       ) AS PreviousProductID
FROM  PHOTOCOLLECTIONS AS P1
WHERE ProductID = 6
      AND category='Animals & Wildlife'

Note that I do NOT think your want a category constraint at all. The user has asked to see Product 6. The next / previous Product ID will be for higher/lower Product ID numbers for the same category as Product 6. Lets assume the Next = 8. If I click on that it will find Product 8, and then the Next/Previous Product IDs for the same category as Product 8 - which will still be 'Animals & Wildlife'

Watch out when you get to the end and the NextProductID / PreviousProductID columns will be NULL - you'll need to modify your "HREF Link" when you reach the end (and have a NULL value in the resultset)

Technical stuff: What we are doing here is referencing the table PHOTOCOLLECTIONS three times - once for the Product ID we want, and again for the Next / Previous values. To avoid ambiguity we now need to "Alias" the tables - I've used alias names of P1=Main table, P2=Next and P3=Previous.

Using these aliases I can explictly reference columns from one or other aliased tables. So when finding the "Next" product I can say "The category on the Next product must be the same as the category on the Main product" - this bit in fact:

AND P2.category = P1.category

Kristen
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-09-02 : 07:00:20
Further thought:

I suggest you use a SQL tool to test the SQL before you put it in your PHP code. Its much harder to debug once in PHP and you have to worry about concatenating string fields, sorting out quote marks, and so on. You want something you can just cut & paste my example into, play around with it, check the "edge conditions", etc. and THEN paste it into PHP and stick the
" & MyFormField & "
bits into it!

Your best bet would be Query Analyser, which comes with the Client Tools that are shipped with SQL Server.

Failing that I expect a freeware / shareware tool would do just fine.

Kristen
Go to Top of Page

sonofmarzipan
Starting Member

7 Posts
Posted - 2005-09-02 : 07:19:36
Hi again Kristen,

I think something is wrong.

You can see if you go to http://www.photoghetto.com/photo-images/animals-photo-detail.asp?ProductID=6

I have implemented your code with the aliases, however, when the user comes to the end (either the beginning, or the last entry from the category that the previous and next buttons contiune. ie. When you reach the first entry in the Animals & Wildlife category and press the previous button, it doesnt stay within the Animals & Wildlife category, instead it goes into the Urban Decay category, which it cannot do.

The previous/ next buttons can only provide the browsing for the selected category, for example, Animals & Wildlife in this case.

On the previous/ next buttons I am simply using

href="animals-photo-detail.asp?ProductID=current ProductID -1 %>"

and

href="animals-photo-detail.asp?ProductID= current PorductID +1%>"

Could this be causing the problems?



What we have now is basically the following:


<%
Dim kristen
kristen = Request.QueryString("ProductID")

Dim backgrounds_rs
Dim backgrounds_rs_numRows
Set backgrounds_rs = Server.CreateObject("ADODB.Recordset")
backgrounds_rs.ActiveConnection = MM_photo_STRING
backgrounds_rs.Source = "SELECT *, (SELECT TOP 1 P2.ProductID FROM PHOTOCOLLECTIONS AS P2 WHERE P2.ProductID > "&kristen&" AND P2.category = P1.category ORDER BY P2.ProductID) AS NextProductID, (SELECT TOP 1 P3.ProductID FROM PHOTOCOLLECTIONS AS P3 WHERE P3.ProductID < "&kristen&" AND P3.category = P1.category ORDER BY P3.ProductID DESC) AS PreviousProductID FROM PHOTOCOLLECTIONS AS P1 WHERE ProductID = "&kristen&""
backgrounds_rs.CursorType = 0
backgrounds_rs.CursorLocation = 2
backgrounds_rs.LockType = 1
backgrounds_rs.Open()
backgrounds_rs_numRows = 0
%>

<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = 1
Repeat1__index = 0
backgrounds_rs_numRows = backgrounds_rs_numRows + Repeat1__numRows
%>


<a href="animals-photo-detail.asp?ProductID=<%=(backgrounds_rs.Fields.Item("ProductID").Value -1)%>">Previous button</a>
<a href="animals-photo-detail.asp?ProductID=<%=(backgrounds_rs.Fields.Item("ProductID").Value +1)%>"Next button</a>


And these previous/ next buttons don't stay within the current selected category.

I was thinking about the EOF requirement for the Previous Forward buttons as in if EOF then greyed out non clickable button else continue with the clickable button etc.,

But I just dont know how to keep the browsing within the selected category.

I've implemented your code as I say Kristen, here at http://www.photoghetto.com/photo-images/animals-photo-detail.asp?ProductID=6

Again, I'm so grateful for all your help and assistance Kristen. I think I simply reached the limit of what I can do, so I am so very grateful for your expert help and time you're spending with me on this prblem.

-SOM

-SOM
Go to Top of Page

sonofmarzipan
Starting Member

7 Posts
Posted - 2005-09-02 : 08:42:22
Hi Kristen,

I don't know if you are still out there, but I've been wokring on things, and well...the previous button works, but for some reason, the NEXT returns no ProductID value.

Ive placed an EOF catch on the previous button to stop it going back once its at the beginning of the catgeory.

http://www.photoghetto.com/photo-images/animals-photo-detail.asp

any ideas?

-SOM
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-09-02 : 09:40:44
"On the previous/ next buttons I am simply using

href="animals-photo-detail.asp?ProductID=current ProductID -1 %>"

and

href="animals-photo-detail.asp?ProductID= current PorductID +1%>"

Could this be causing the problems?"

Yup - I reckon. Uou need to use

backgrounds_rs.Fields.Item("PreviousProductID").Value
and
backgrounds_rs.Fields.Item("NextProductID").Value

they are evaluated in my SQL Query as "additional" columns in your resultset - and will correpond to the previous/next products for the current category.

However, you will need to test them and NOT output an HREF if they are NULL

Kristen
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site