Adding To A Recordset

Hi Gang

I have to 2 SQL statments that grab data from 2 different tables. What
I need to do is create a recordset for each and then combine the 2 into
1 recordset.

Can I do this easily?

Thanks
Andy

Re: Adding To A Recordset

How about grabbing one recordset using a UNION?

SELECT column1, column2, ... , columnX
FROM tableA
UNION
SELECT column1, column2, ... , columnX
FROM tableB

Or fixing the design... if the data structures are identical, you could
probably store both sets of data in the same table.

--
http://www.aspfaq.com/
(Reverse address to reply.)




"Andy" wrote in message
news:1109861116.462248.71560@l41g2000cwc.googlegroups.com...
> Hi Gang
>
> I have to 2 SQL statments that grab data from 2 different tables. What
> I need to do is create a recordset for each and then combine the 2 into
> 1 recordset.
>
> Can I do this easily?
>
> Thanks
> Andy
>

Re: Adding To A Recordset

"Andy" wrote in message
news:1109861116.462248.71560@l41g2000cwc.googlegroups.com...
> Hi Gang
>
> I have to 2 SQL statments that grab data from 2 different tables. What
> I need to do is create a recordset for each and then combine the 2 into
> 1 recordset.
>
> Can I do this easily?
>
> Thanks
> Andy
>

Are the table related?
Would a JOIN work?

Otherwise, you could create your own recordset.

This should get you started. Watch for word-wrap.

Const cRST = "whatever"
'*
Const adChar = 129
Const adLockBatchOptimistic = 4
Const adOpenStatic = 3
Const adUseClient = 3
'*
'* Declare Variables
'*
Dim intRST
intRST = 0
'*
'* Declare Objects
'*
Dim objRST
Set objRST = CreateObject("ADODB.RecordSet")
objRST.CursorLocation = adUseClient
objRST.LockType = adLockBatchOptimistic
objRST.CursorType = adOpenStatic
objRST.ActiveConnection = Nothing
objRST.Fields.Append cRST, adChar, 255
'* [additional fields could be defined here]
objRST.Open
'*
'* Recordset 1
'*
Do While Not objRS1.EOF
objRST.AddNew
objRST.Fields(cRST) = "#1 " & objRS1("your_field")
objRST.Update
objRS1.MoveNext
Loop
'*
'* Recordset 2
'*
Do While Not objRS2.EOF
objRST.AddNew
objRST.Fields(cRST) = "#2 " & objRS2("your_field")
objRST.Update
objRS2.MoveNext
Loop
'*
'* Sort RecordSet
'*
objRST.Sort = cRST
objRST.MoveFirst
'*
'* Read RecordSet
'*
Do While Not objRST.EOF
intRST = intRST + 1
strRST = Trim(objRST.Fields(cRST))
WScript.Echo intRST & ". " & strRST
objRST.MoveNext
Loop
'*
'* Destroy Objects
'*
objRST.Close
Set objRST = Nothing
'*
WScript.Echo FormatNumber(intRST,0) & " records."

Re: Adding To A Recordset

Hi Aaron

The SELECT with the UNION clause works the best for me here and it gets
my 2 select statements into 1 record set. The only problem is that I
can't figure out a way to order the select statement. If I put an
ORDER BY into the select statement it just errors out. Is there a way
to order the select statement with a union clause?

Thanks
Andy

Re: Adding To A Recordset

Andy wrote:
> Hi Aaron
>
> The SELECT with the UNION clause works the best for me here and it
> gets my 2 select statements into 1 record set. The only problem is
> that I can't figure out a way to order the select statement. If I
> put an ORDER BY into the select statement it just errors out. Is
> there a way to order the select statement with a union clause?
>
> Thanks
> Andy

You have to use a single "order by", after the last "select" in the union:

select ...
union
select ...
order by

The "order" by will only recognize column names defined in the first
"select".
We cannot get more specific without knowing what database you are using.
"union" is pretty standard, but some details may depend on the database
used.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.