Merge two recordset objects

I have two recordsets objects which are currently open.
One of these has xx no. of dummy fields . I want to add
column values of the other recordset object in these dummy columns for each row. In short i want to merge the result of these two recordset objects
coz i have to send a single VARIANT object back to the client App from my DLL
Here is my sample code which fails for some reason.

'
'Pseudo Code
'col1 is int and col2 is text
'MSSQL server 7.0 ADO 2.0
'
cmd.CommandText = "select col1,col2 from table Sample1"

Dim record As New ADODB.Recordset
record.CursorLocation = adUseClient
record.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdText

cmd.CommandText = "select 0,'x', col3, col4 from Sample2"

Dim record1 As New ADODB.Recordset
record1.CursorLocation = adUseClient
record1.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdText

if record.GetCount > record1.GetCount Then
      do while not record.EOF
            record1.Fields(0).Value = record.Fields(0).Value
            record1.Fields(1).Value = record.Fields(1).Value            
            record.MoveNext
            record1.MoveNext
      loop
else
'
'
End if
vdvAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
cognitionConnect With a Mentor Commented:
From looking at your code, you only go into the loop if the first recordset has more records than the second, but you continue to do a movenext on the second recordset which will have EOF set to True before EOF is set to True on the first recordset.

if record.GetCount > record1.GetCount Then
    do while not record1.EOF
        record1.Fields(0).Value = record.Fields(0).Value
        record1.Fields(1).Value = record.Fields(1).Value
        record.MoveNext
        record1.MoveNext
    loop
else
    do while not record.EOF
        record1.Fields(0).Value = record.Fields(0).Value
        record1.Fields(1).Value = record.Fields(1).Value
        record.MoveNext
        record1.MoveNext
    loop
End if

Other problems may be that you need to use RecordCount with ADO not GetCount, or that RecordCount will return -1 if it cannot determine the number of rows returned.
0
 
StapleheadCommented:
why can't you just union the two samples together in one recordset?

larry
0
 
chicheCommented:
I guess Larry meant: join
0
 
pagladasuCommented:
What exactly is going wrong?
0
 
vdvAuthor Commented:
I want to merge the results of recordset A into another recordset B after B is opened. I want to know if this possible.

I cannot use composite recordset as i will lose the result of first recordset when i use the nextrecordset command to execute the next query in the commandtext property  

larry could u give more detail about u'r solution
0
All Courses

From novice to tech pro — start learning today.