Using ASP script in an SQL statement?

[Chandler_90]

I'm attempting to filter a recordset using 2 values.
The first value is "Booked = False"
The second value refers to a line of script produced by another recordset "Showing_ID = <%=(rs_ID.Fields.Item("Show_ID").Value)%>"

However when I enter the SQL line like this -

rs_booking.Source = "SELECT Seat FROM qrybooking WHERE Booked = False AND Showing_ID = <%=(rs_ID.Fields.Item("Show_ID").Value)%>"

It displays an error saying there are too few parameters.
This to me makes me think that the way I have entered the asp line in the sql statement is incorrect.

Within the showing ID field there will be a value ranging from 1 - 19 that will be filtered through to select the right records.

What I am trying to achieve here is a dynamic display of seats in a cinema that are free for a certain showing of a film.

The script <%=(rs_ID.Fields.Item("Show_ID").Value)%> produces the correct value as I have it displayed on the page elsewhere.

I understand this isn't really well explained but can anybody help me?

 

[sfx]

Surely you cannot do that as you would first need to query the Database to get the value for <%=(rs_ID.Fields.Item("Show_ID").Value)%>

In which case you would need to run a query to find out what <%=(rs_ID.Fields.Item("Show_ID").Value)%> is, then you would need to store the value in a variable then run your original query replacing <%=(rs_ID.Fields.Item("Show_ID").Value)%> with your variable.

Not really a good way of doing things.

sfx

 

[Chandler_90]

I know it isn't a good way of doing things but I spent 8 hours on it today and its the only way I can see of doing it without scrapping 3 weeks worth of work.

I'll try your suggestions and report back after the match
Thanks!

 

[sfx]

Also if <%=(rs_ID.Fields.Item("Show_ID").Value)%> is returning more than one value then it will not work either for obvious reasons.

sfx

 

[ClearChaos]

its late i cant sleep and found this on page 2 so i thought i may as well reply, beats watching anything that freview can offer this time of night

rs_booking.Source = "SELECT Seat FROM qrybooking WHERE Booked = False AND Showing_ID = " & rs_ID("Show_ID")

Or

rs_booking.Source = "SELECT Seat FROM qrybooking WHERE Booked = False AND Showing_ID = '" & rs_ID("Show_ID") & "'"

depending on the content of rs_ID("Show_ID")