Passing value from one query to another

I'm creating a product display page on which I want to include a box featuring the most recent product in the subject of the product featured.
The product ID is passed in from the URL, and I'm trying to take the subject of that product and display its most recent title.
Here's my code: all I get at the moment is the same product on every single page, regardless of what product ID is passed in.

<%
productID=Request.Querystring("pID")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("/xxx.mdb")
sql = "SELECT kbs.subject FROM kbs WHERE id = " & productID & ";"
set rs = Server.CreateObject("ADODB.Recordset")
set rs = conn.execute(sql2)
subj = ("kbs.subject")
sql2 = "SELECT * FROM kbs, media where kbs.media=media.mediaTypeID AND kbs.subject = " & subj & " ORDER BY publicationDate desc;"
set rs2 = Server.CreateObject("ADODB.Recordset")
set rs2 = conn.execute(sql2)
%>

Any ideas?

what is subj = ("kbs.subject") suppose to do?

I thought I needed to create a new variable that was set to be kbs.subject of the first query and then to run a query based on that variable.

I dunno really - I'm pretty new to this stuff and I'm working with code that was written before I took on this project, so I'm just trying to work it out as I go along.

i think what you want is:

what your previous code is doing is setting sub to the string kbs.subject. then in your where clause you have where kbs.subject = kbs.subject which is always true

I tried that before: I get a Type mismatch: 'rs' error message when I do that.

could it be like this


could be your typo error.

probably your Type mismatch : 'rs' occurs/appears cuz you did not have any "Select...." statement, and that leads to your line 7.

if i'm wrong, pls let us know.

Now I get:

Item cannot be found in the collection corresponding to the requested name or ordinal.

/education/featprod3.asp, line 20

which is this line: subj = rs("kbs.subject")

Does that mean it can't find the variable 'subj' in my query?

PS: Fixed up 'sql2' typo.

if this is the error line, then that means your 'id' does not exist.

or you could try this, remove your kbs. from the subject

For anyone that's interested, with help from someone I finally got it to work using this:

sql = "SELECT kbs.subject FROM kbs WHERE id = " & productID & ";"
set rs = Server.CreateObject("ADODB.Recordset")
set rs = conn.execute(sql)
subj = rs.fields.item(0)
sql2 = "SELECT * FROM kbs, media where kbs.media=media.mediaTypeID AND kbs.subject = " & subj & " ORDER BY publicationDate desc;"
set rs2 = Server.CreateObject("ADODB.Recordset")
set rs2 = conn.execute(sql2)