Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1910
  • Last Modified:

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
0
vdv
Asked:
vdv
1 Solution
 
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
 
cognitionCommented:
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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now