Is there anyway where I can join two recordsets on some common fields and 

get the result as another recordset? I want to do it like executing a 

SELECT command on two tables. 



For example, I have a recordset oRs1 containing the fields ProductID and 

price. I have another recordset oRs2 containing the fields ProductID and 

ProductName. I want to join oRs1 and oRs2 on ProductID to give me 

ProductName, ProductID and price. 



oRs1 an oRs2 are returned from some SAP remote functions which 

unfortunately I don't have a lot of control on how they should behave. 



Thanks,

Wilson Li

Australia 

Maybe this will do it.



SELECT table1.price AS price, table2.ProductName AS ProductName, _

table2.ProductID AS ProductID FROM table1 INNER JOIN table2 ON _ 

table1.ProductID = table2.ProductID





Something like that should work, I dont know the name of your tables.

Good luck.



//Jonas





> Is there anyway where I can join two recordsets on some common fields 

and 

> get the result as another recordset? I want to do it like executing a 

> SELECT command on two tables. 

> 

> For example, I have a recordset oRs1 containing the fields ProductID 

and 

> price. I have another recordset oRs2 containing the fields ProductID 

and 

> ProductName. I want to join oRs1 and oRs2 on ProductID to give me 

> ProductName, ProductID and price. 

> 

> oRs1 an oRs2 are returned from some SAP remote functions which 

> unfortunately I don't have a lot of control on how they should behave. 

> 

> Thanks,

> Wilson Li

> Australia 

> 

Thanks for you reply.



The problem is I don't see the tables. I don't see the database at all. 



All I can see are recordsets (i.e. objects) and the fields in these 

recordsets returned from some COMs. So the question is what I can do AFTER 

I have obtained these recordsets oRs1, oRs2 having fields oRs1

("ProductID"), oRs1("Price"), oRs2("ProductID") and oRs2("ProductName"). 



Is there a quick way where I can obtain another recordset oRs3 having 

fields oRs3("ProductID"), oRs3("ProductName"), oRs3("Price") from the two 

recordsets oRs1 and oRs2?



Thanks,

Wilson





> Maybe this will do it.

> 

> SELECT table1.price AS price, table2.ProductName AS ProductName, _

> table2.ProductID AS ProductID FROM table1 INNER JOIN table2 ON _ 

> table1.ProductID = table2.ProductID

> 

> 

> Something like that should work, I dont know the name of your tables.

> Good luck.

> 

> //Jonas

> 

> 

> > Is there anyway where I can join two recordsets on some common fields 

> and 

> > get the result as another recordset? I want to do it like executing a 

> > SELECT command on two tables. 

> > 

> > For example, I have a recordset oRs1 containing the fields ProductID 

> and 

> > price. I have another recordset oRs2 containing the fields ProductID 

> and 

> > ProductName. I want to join oRs1 and oRs2 on ProductID to give me 

> > ProductName, ProductID and price. 

> > 

> > oRs1 an oRs2 are returned from some SAP remote functions which 

> > unfortunately I don't have a lot of control on how they should behave. 

> > 

> > Thanks,

> > Wilson Li

> > Australia 

> > 

Wilson Li



   It is not necessary to write two quary for two record set.You can join 

two table with the innor join with Product ID and write a single record 

set like this



SELECT book.accession_no, book.pub_id, supplier.supplier_name

FROM book INNER JOIN supplier ON book.accession_no supplier.accession_no 

where a.accession_no='1234';



Where BOOK and SUPPLIER are two TABLE



Thanks'



Nabin Sahu

Nabin,



Thanks for your reply. However I am still stuck. I think I have some 

difficulties in explaining my problem. 



The recordsets oRs1, oRs2 I mentioned are obtained from some COMs, not 

from the execution of SELECT command. For example in VBScript, 



---------------------------------------------------------------

Dim oRs1, oRs2                 

Dim oSapObj



' SAP.product.1 is COM+ installed on the server

Set oSapObj = Server.CreateObject ( "SAP.product.1" ) 



' invoke method which returns a recordset containing product ID and price

Call oSapObj.pricelist( oRs1 )



' invoke method which returns a recordset containing product ID and name

Call oSapObj.namelist ( oRs2 )



' house cleaning etc.

Set oSapObj = Nothing

...

-----------------------------------------------------------------



There are many of these objects. I can obtain a lot of recordsets by 

calling some methods. But I do not have control over how these methods 

behave.



So what do I do when I get these recordsets? How do I combine them? I did 

searches on some discussion forums. But it does not seem to be any 

answers. It seems that I have to write some customed functions to combine 

these recordsets.



Thanks,

Wilson Li 





> Wilson Li

> 

>    It is not necessary to write two quary for two record set.You can 

join 

> two table with the innor join with Product ID and write a single record 

> set like this

> 

> SELECT book.accession_no, book.pub_id, supplier.supplier_name

> FROM book INNER JOIN supplier ON book.accession_no supplier.accession_no 

> where a.accession_no='1234';

> 

> Where BOOK and SUPPLIER are two TABLE

> 

> Thanks'

> 

> Nabin Sahu

> 

It cannot be done.



If you want this you need to have access to the SQL Statements generating

the recordsets. Then you can use UNION or rewrite them as fits your needs.



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: wai.keung.li@a...

[mailto:wai.keung.li@a...]

Sent: Tuesday, 30 October 2001 11:28 AM

To: ASP Databases

Subject: [asp_databases] Re: joining two recordsets





Nabin,



Thanks for your reply. However I am still stuck. I think I have some 

difficulties in explaining my problem. 



The recordsets oRs1, oRs2 I mentioned are obtained from some COMs, not 

from the execution of SELECT command. For example in VBScript, 



---------------------------------------------------------------

Dim oRs1, oRs2                 

Dim oSapObj



' SAP.product.1 is COM+ installed on the server

Set oSapObj = Server.CreateObject ( "SAP.product.1" ) 



' invoke method which returns a recordset containing product ID and price

Call oSapObj.pricelist( oRs1 )



' invoke method which returns a recordset containing product ID and name

Call oSapObj.namelist ( oRs2 )



' house cleaning etc.

Set oSapObj = Nothing

...

-----------------------------------------------------------------



There are many of these objects. I can obtain a lot of recordsets by 

calling some methods. But I do not have control over how these methods 

behave.



So what do I do when I get these recordsets? How do I combine them? I did 

searches on some discussion forums. But it does not seem to be any 

answers. It seems that I have to write some customed functions to combine 

these recordsets.



Thanks,

Wilson Li 





> Wilson Li

> 

>    It is not necessary to write two quary for two record set.You can 

join 

> two table with the innor join with Product ID and write a single record 

> set like this

> 

> SELECT book.accession_no, book.pub_id, supplier.supplier_name

> FROM book INNER JOIN supplier ON book.accession_no supplier.accession_no 

> where a.accession_no='1234';

> 

> Where BOOK and SUPPLIER are two TABLE

> 

> Thanks'

> 

> Nabin Sahu

How do you want to join the recordset together? I assume you want to append

one recordset to the other. Have you thought about getting the recordsets

into arrays or strings?



Eg you could you .getString on both recordsets. Append the 2nd string to the

1st string, then use Split() statements to create an array of records.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: <wai.keung.li@a...>

Subject: [asp_databases] Re: joining two recordsets





: Nabin,

:

: Thanks for your reply. However I am still stuck. I think I have some

: difficulties in explaining my problem.

:

: The recordsets oRs1, oRs2 I mentioned are obtained from some COMs, not

: from the execution of SELECT command. For example in VBScript,

:

: ---------------------------------------------------------------

: Dim oRs1, oRs2

: Dim oSapObj

:

: ' SAP.product.1 is COM+ installed on the server

: Set oSapObj = Server.CreateObject ( "SAP.product.1" )

:

: ' invoke method which returns a recordset containing product ID and price

: Call oSapObj.pricelist( oRs1 )

:

: ' invoke method which returns a recordset containing product ID and name

: Call oSapObj.namelist ( oRs2 )

:

: ' house cleaning etc.

: Set oSapObj = Nothing

: ...

: -----------------------------------------------------------------

:

: There are many of these objects. I can obtain a lot of recordsets by

: calling some methods. But I do not have control over how these methods

: behave.

:

: So what do I do when I get these recordsets? How do I combine them? I did

: searches on some discussion forums. But it does not seem to be any

: answers. It seems that I have to write some customed functions to combine

: these recordsets.

:

: Thanks,

: Wilson Li



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Not Quite.

Here is code that uses the PUBS database in SQL Server

Just create an ODBC to PUBS and try it



It merges the two records sets into one



Someone else can tweak it.



Dallas



<%

dim fields(2,3), MergedRS,oRS1, oRS2,X,Y,bExists,title_id,price,title



set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open("DSN=PUBS;uid=sa;pwd=;")



set oRS1 = Server.CreateObject("ADODB.RecordSet")

set oRS2 = Server.CreateObject("ADODB.RecordSet")



oRS1.ActiveConnection = oConn

oRS1.CursorType = 2 'adOpenDynamic

oRS1.CursorLocation = 2 'adUseServer

oRS1.Open "SELECT title_id,price FROM titles where price is not null"



oRS2.ActiveConnection = oConn

oRS2.CursorType = 2

oRS2.CursorLocation = 2

oRS2.Open "SELECT title_id,title FROM titles where price is not null"



set MergedRS = Server.CreateObject("ADODB.RecordSet")



'/* get the field information from oRS1 */

X = 0

For each fld in oRS1.Fields

      fields(x,0) = fld.name

      fields(x,1) = fld.type

      fields(x,2) = fld.definedsize

      x = x + 1

Next



'/* get field information from oRS2 */

For each fld in oRS2.Fields

      bExists = false

      for y = 0 to ubound(fields)

           if fld.name = fields(y,0) then

              bExists = true

              exit for

          end if

      next

      if NOT bExists then

          fields(x,0) = fld.name

          fields(x,1) = fld.type

          fields(x,2) = fld.definedsize

          x = x + 1

      end if

Next



'/ add fields to recordset object */

Y = ubound(fields)

for X = 0 to Y

     MergedRS.fields.append fields(x,0),fields(x,1),fields(x,2)

next



'/* merged recordsets, oRS1 and oRS2 */

MergedRS.open

oRS1.MoveFirst

while not oRS1.eof

    title_id = oRS1("title_id")

    price = oRS1("price")

    title = ""

    oRS2.find "title_id='" & title_id & "'"

    if not oRS2.eof then

      title = oRS2("title")

    end if

    MergedRS.addnew

    MergedRS("title_id") = title_id

    MergedRS("price") = price

    MergedRS("title") = title

    MergedRS.UpDate

    oRS1.MoveNext

wend

oRS1.Close

oRS2.Close

Set oRS1 = Nothing

Set oRS2 = Nothing



MergedRS.MoveFirst

while not MergedRS.eof

   response.write(MergedRS("title_id"))

   response.write(" - ")

   response.write(MergedRS("title"))

   response.write(" - ")

   response.write(MergedRS("price"))

   response.write("<br>")

   MergedRS.MoveNext

wend

MergedRS.close

set MergedRS = Nothing

%>



----- Original Message -----

From: "David Cameron" <dcameron@i...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, October 30, 2001 5:57 PM

Subject: [asp_databases] Re: joining two recordsets





> It cannot be done.

>

> If you want this you need to have access to the SQL Statements generating

> the recordsets. Then you can use UNION or rewrite them as fits your needs.

>

> regards

> David Cameron

> nOw.b2b

> dcameron@i...

>

> -----Original Message-----

> From: wai.keung.li@a...

> [mailto:wai.keung.li@a...]

> Sent: Tuesday, 30 October 2001 11:28 AM

> To: ASP Databases

> Subject: [asp_databases] Re: joining two recordsets

>

>

> Nabin,

>

> Thanks for your reply. However I am still stuck. I think I have some

> difficulties in explaining my problem.

>

> The recordsets oRs1, oRs2 I mentioned are obtained from some COMs, not

> from the execution of SELECT command. For example in VBScript,

>

> ---------------------------------------------------------------

> Dim oRs1, oRs2

> Dim oSapObj

>

> ' SAP.product.1 is COM+ installed on the server

> Set oSapObj = Server.CreateObject ( "SAP.product.1" )

>

> ' invoke method which returns a recordset containing product ID and price

> Call oSapObj.pricelist( oRs1 )

>

> ' invoke method which returns a recordset containing product ID and name

> Call oSapObj.namelist ( oRs2 )

>

> ' house cleaning etc.

> Set oSapObj = Nothing

> ...

> -----------------------------------------------------------------

>

> There are many of these objects. I can obtain a lot of recordsets by

> calling some methods. But I do not have control over how these methods

> behave.

>

> So what do I do when I get these recordsets? How do I combine them? I did

> searches on some discussion forums. But it does not seem to be any

> answers. It seems that I have to write some customed functions to combine

> these recordsets.

>

> Thanks,

> Wilson Li

>

>

> > Wilson Li

> >

> >    It is not necessary to write two quary for two record set.You can

> join

> > two table with the innor join with Product ID and write a single record

> > set like this

> >

> > SELECT book.accession_no, book.pub_id, supplier.supplier_name

> > FROM book INNER JOIN supplier ON book.accession_no supplier.accession_no

> > where a.accession_no='1234';

> >

> > Where BOOK and SUPPLIER are two TABLE

> >

> > Thanks'

> >

> > Nabin Sahu

>

>

>




$subst('Email.Unsub')

>

Hi there,

Can any body tell me how to display the records from a

stored procedure.It will be very helpful if you can

quote some example.

Thanx

--Arshad--

objRS.Open "usp_MySproc", objConn, adOpenForwardOnly, adLockReadOnly,

adCmdStoredProc



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "arshad siddiqui" <ash_arshad@y...>

Subject: [asp_databases] Re: joining two recordsets





: Hi there,

: Can any body tell me how to display the records from a

: stored procedure.It will be very helpful if you can

: quote some example.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~