recordset 3

[Glitchen]

Here is what I am trying to do

What I have created is a simple photoalbum. when you click on a picture it takes you to another page called say freinds_detail.asp. Now depending on what pitcure you click on the url will look like this freinds_detail.asp?pic_id=1

All of the information on the freinds_detail.asp page is coming from a access db in a table called freinds.

here is the table set up

Field Name Data Type
-------------------------
pic_id AutoNumber (primary key)
thum_pic text
large_pic text
pic_title text
pic_descrip Memo
date_taken date/time


But I also have another table called comments.

here is the table set up

Field Name Data Type
-------------------------
comm_id Autonumber(primary key)
pics_id number
by_name text
by_date date/time
comm_text memo

now I want to display the comments for each picture on freinds_detail.asp.

so say for instance I click on the first picture and it takes me to the freinds_detail page the url is going to look like this
freinds_detail.asp?pic_id=1
now i want every record in the comments table to display that has a 1 in the pic_id field.

The one in the pic_id field in the comments table corosponds to the pic_id field in the freinds table.
so freinds_detail.asp?pic_id=1 would be record one in the freinds table.pic_id and I would want all of the recods in the comments.pic_if table to display.

here is a query I tried but it didn't bring the results I wanted...or I am just not clear on how to make it work correctly.

SELECT pic_id, comm_text, pics_id
FROM freinds, comments
WHERE comments.pics_id = freinds.pic_id

Here is a link of the results
[L=Click here]

http://www.balzoutonline.com/freinds_detail.asp?pic_id=1

[/L]

as you can see in the bottom right it is displaying all of the records where both comments.pics_id and freinds.pic_id.
But I only want it to display for whatever record I am displaying...

Thanks

 

[rac2]

You need to specify the value of pic_id or pics_id to be equal to the value in the querystring. The query needs another condition.

SELECT pic_id, comm_text, pics_id
FROM freinds, comments
WHERE comments.pics_id = freinds.pic_id
AND freinds.pic_id = 1

Of course it must change when the pic_id changes. This is achieved by building a query based on pic_id. To do this you need to write something like this.

<%
Dim vPicID, qGetPerson
vPicID = Request.Querystring(&quot;pic_id&quot;)
...

qGetPerson = &quot;SELECT pic_id, comm_text, pics_id
FROM freinds, comments
WHERE comments.pics_id = freinds.pic_id
AND freinds.pic_id = &quot; & vPicID
...
rsGetPerson.Open(qGetPerson, objConn)
...
%>

And you could write the query using JOIN like this

SELECT pic_id, comm_text, pics_id
FROM freinds
JOIN comments ON comments.pics_id = freinds.pic_id
WHERE  freinds.pic_id = 1

Furthermore, be aware that the query will seem to fail to produce a picture id when there are no comments. So you might want to do this.

SELECT pic_id, comm_text, pics_id
FROM freinds
LEFT JOIN comments ON comments.pics_id = freinds.pic_id
WHERE  freinds.pic_id = 1

The LEFT JOIN insures that you will always get the freinds info regardless of the presence or absence of comments.

 

[TonyU]

try creating a query in access pulling the relevant informaton thus creating the recordset from the query.

^{There's never time to do it right, but there's always time to do it over!}

 

[Genimuse]

rac2's got it right, good for a star.

 

[Glitchen]

rac2 answer does the job when i run the query. But now when i put his code in the asp page i am getting the error
Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/freinds_detail.asp, line 248, column 47
qGetPerson = "SELECT pic_id, comm_text, pics_id
----------------------------------------------^


here is the code i used
<%
Dim vPicID, qGetPerson
vPicID = Request.Querystring(&quot;pic_id&quot


qGetPerson = &quot;SELECT pic_id, comm_text, pics_id
FROM freinds, comments
WHERE comments.pics_id = freinds.pic_id
AND freinds.pic_id = &quot; & vPicID

rsGetPerson.Open(qGetPerson, objConn)

%>


thanks

 

[Genimuse]

In VBScript you can't break a string across multiple lines like that, so

qGetPerson = "SELECT pic_id, comm_text, pics_id
FROM freinds, comments
WHERE comments.pics_id = freinds.pic_id
AND freinds.pic_id = " & vPicID

should be either

qGetPerson = "SELECT pic_id, comm_text, pics_id FROM freinds, comments WHERE comments.pics_id = freinds.pic_id AND freinds.pic_id = " & vPicID

(that is, all one line), or

qGetPerson = "SELECT pic_id, comm_text, pics_id " &_
"FROM freinds, comments " &_
"WHERE comments.pics_id = freinds.pic_id " &_
"AND freinds.pic_id = " & vPicID

or, (and this is a little more clear than the one above to me but requires a tiny bit more processing power)

qGetPerson = "SELECT pic_id, comm_text, pics_id "
qGetPerson = qGetPerson & "FROM freinds, comments "
qGetPerson = qGetPerson & "WHERE comments.pics_id = freinds.pic_id "
qGetPerson = qGetPerson & "AND freinds.pic_id = " & vPicID

 

[Glitchen]

When I do that now I get the error

Error Type:
Microsoft VBScript compilation (0x800A0414)
Cannot use parentheses when calling a Sub
/freinds_detail.asp, line 249, column 37
rsGetPerson.Open(qGetPerson, objConn)
------------------------------------^


Thanks!!!

 

[Genimuse]

The error explains it pretty well. Use:

rsGetPerson.Open qGetPerson, objConn

 

[rac2]

Genimuse,

Thanks for the star. And for cleaning up my code. What can I say, I code in JScript, not VBScript.

Regards,