ASP how to inner two recordset

Bharat Guru
Bharat Guru used Ask the Experts™
on
In ASP I have two record sets, Both record set has field called "CustomerID "
now How can I build third record set where  rs1.CustomerID  = rs2.CustomerID

Rs1 : All orders  
Rs2 : All Customer

How can I write R3 as following
    Select Rs1.*
    from  RS1
    Inner Join RS2 On RS1.CustomerID = RS2.CustomerID

RS1 and RS2 are stored in different DB system therefore I cannot write the join directly.  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can first get all the id from Rs2 put them in a comma seperated string.
#commaseperatedstring# = "1,5,7,10,14"
than call Rs1 like select * from rs1 where CustomerID in(#commaseperatedstring#)
G_H

Commented:
Two other ways...

ASP has it's own RecordSet object, you could build a custom RecordSet to hold the info:
set Result = Server.CreateObject("ADODB.recordset")
Result.Fields.Append "FieldName", adVarChar
Result.Fields.Append "CustID", adInteger

You could also create a Temporary Table in the database containing the orders, and populate it with the Customer info...

It depends how much of what you want...

GH

Author

Commented:
Recordsets are already created. I cannot modify the sql for existing recordset becaseu one record set is result from DB2 and another recordset is from SQL Server. Now I want to add filter on recordset1 base on recordset2


OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

set all id'sin in a comma serated string by using a while loop

and then use Rs1.filter = "Id in ("& string &")"
Oh and Rs1.filter = 0 removes the filter and you can set a other filter on it
Commented:
patel100

I know you already have the SQL RecordSets, my suggestion is to create an "ADODB" Recordset, which you can build yourself. You can then populate the newly created recordset with whatever data you like...

GH

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial