recordset/SQL question!!!

[highlife]

I am trying to use a recordset as sort of a subquery (below), but as i have it, it doesnt work. error returned is "Type mismatch". Is there anyway to pass a recordset to an SQL query? I cant use a typical subquery because the the data is from different servers!
-----
Set rs2 = Server.CreateObject("ADODB.Recordset")
Set rs = Server.CreateObject("ADODB.Recordset")

sql2="select nCompanyID from company_online, online where Online_Name like '%network%' and company_online.sOnlineID=online.online_ID"
rs2.Open sql2, Application("LDS_DB_CONN"), adOpenForwardOnly, adLockReadOnly

sql="select name, sites.SiteID from Sites where CompanyID in " & rs2
rs.Open sql, Application("DB_CONN"), adOpenForwardOnly, adLockReadOnly
-----

[Clunietp]

Nope, can't do it that way, but you can do it in a single SQL statement

something like:

select name, sites.SiteID from Sites where CompanyID in (select nCompanyID from company_online, online where Online_Name like '%network%' and company_online.sOnlineID=online.online_ID)

[highlife]

i said in my original post that i cant use a typical subquery because each query is a different database on a different server

Well you could loop through the first recordset and remember all records from the second record set.


Set rs2 = Server.CreateObject("ADODB.Recordset")
Set rs = Server.CreateObject("ADODB.Recordset")

sql2 = "select nCompanyID from company_online, online where Online_Name like '%network%' and company_online.sOnlineID=online.online_ID"
rs2.Open sql2, Application("LDS_DB_CONN"), adOpenForwardOnly, adLockReadOnly

Do Until rs2.EOF
sql = "select name, sites.SiteID from Sites where CompanyID = " & rs2("nCompanyID")
rs.Open sql, Application("DB_CONN"), adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
DoStuffWith rs("Name"), rs("SiteID")
End If
rs2.movenext
Loop

rs2.Close
rs.Close


Hope it helps.

[Clunietp]

Sorry Highlife, my reading skills deteriorate as the night gets older :)