Filtering multiple records from one recordset

[fintan]

I have 4 recordsets the first pulling out the key info that I need to filter by. Mainly a number which the other 3 recordset filter by. Whats the best way to approach this?

What I can do is hard code a number into a varible like

Dim Buildcode
Buildcode = "B935"

Then the other recordsets do filter by this number.

WHERE spbldinf_code= '" + Buildcode + "'"

What need to do is make "B935" dynamic. Any ideas?

Thanks

fintan.

[garann]

If you mean that you need to query for the value of "BuildCode", you can do this in a stored procedure, using a variable in the proc, like:

declare @buildCode char(100)
select someField as @buildCode from myTable where [some condition]
select * from myTable where someField = @buildCode 

or just with a more complicated SQL squery, like:

select * from myTable where someField = (select buildCode from anotherTable where [some condition]) 

hth,
g.

[mattglet]

if you have the option, i would use a stored procedure. they are a lot faster, and a lot less work for your server.

[mattglet]

Dim Buildcode
Buildcode = "B935"

Then the other recordsets do filter by this number.

WHERE spbldinf_code= '" + Buildcode + "'"

and to answer your question, why not do this:

store "B935" in a variable (i.e. dynamicnumber)

dim Buildcode
Buildcode = dynamicnumber

WHERE spbldinf_code = '" + Buildcode + "'"

would that work for you?

-Matt

[fintan]

Sorry for the delay in getting back, I was in Amsterdam for the weekend.

I tried making the "B935" dynamic with

Buildcode = rsBuildconByCounty.Fields.Item("spbldinf_code").Value

It doesn't seem to render it. I'll try the stored procedure and see what happens

[davegerard]

Is this what you're looking for?

' --primary recordset--
set rs1 = server.createobject("adodb.recordset")
sql1 = "select * from table where field = ' " & value & " ' "
rs1.open sql1, cn
if not rs1.eof then
do while not rs1.eof

BuildCode = rs1("field")

' --get nested values--
set rs2 = server.createobject("adodb.recordset")
sql2 = "select field1, field2, field3"
sql2 = sql2 & " from table where spbldinf_code = ' " & BuildCode & " ' "
rs2.open sql2, cn
if not rs2.eof then

Value1 = rs2("field1")
Value2 = rs2("field2")
Value3 = rs2("field3")

end if
rs2.close
set rs2 = nothing

rs1.movenext
loop
end if
rs1.close
set rs1 = nothing

Let me know if it helps.

Dave