Recordset Help from the GODS!

SQL WHERE clause incorporating another recordset field value

[Hobnob]

Once more i venture into the world of the gods to seek your guidance!

Guys (or gods) I'm trying to create a record set from a table that is specified by a WHERE clause that incorporates a value from a previous recordset on the same page.

I currently have:

Products.Source = "SELECT * FROM tbl_Products WHERE Product_Name = '" & Basket.Fields.Product_Name("Product_Name").value & "'"

But as usual I'm missing something (other than a brain!). Am sure it is something little, but I'm totally stumped - HELP

Obviously i have the following setup:

tbl_Products with a field name of "Product_Name"
(RS) Basket with same field name already set up and fine.

THANKS BE TO THE GODS!

[macrost]

Hobnob,
Could you post the code that you are trying to get working?

Mac

[Hobnob]

Hey Mac

The code as detailed in the above post is the Recordset line... the full RS looks like this:

<%
set Products = Server.CreateObject("ADODB.Recordset")
Products.ActiveConnection = DB_STRING
Products.Source = "SELECT * FROM tbl_Products WHERE Product_Name = '" & Basket.Fields.Product_Name("Product_Name").value & "'"
Products.CursorType = 0
Products.CursorLocation = 2
Products.LockType = 3
Products.Open()
Products_numRows = 0
%>
<%
set Basket = Server.CreateObject("ADODB.Recordset")
Basket.ActiveConnection = DB_STRING
Basket.Source = "SELECT * FROM tbl_Basket WHERE Order_Status='Incomplete' AND Username = '" & str_users_name & "'"
Basket.CursorType = 0
Basket.CursorLocation = 2
Basket.LockType = 3
Basket.Open()
Basket_numRows = 0
%>

The RS "Basket" is working fine, so I really do not see the problem :(

[macrost]

Do this
Products.Source = "SELECT * FROM tbl_Products WHERE Product_Name = '" & Basket.Fields.Product_Name("Product_Name").value & "'"

response.write(Products.Source)
right below it and see if your product_name is being filled out.