compare two record sets in vb6

Question

HI,

I have two recordsets rs1 and rs2 in vb6. i want to compare each record in rs1 with each record in rs2. If rtn(column) in rs1 is same as in rtn in rs2.then set date = now and claim = c.

This is what i wan to do. How to compare each row in rs1 with each row in rs2. Help me pls.

Answer 1

Just loop over both recordsets in an inner and outer loop similar to the following

rs1.MoveFirst
While not rs1.EOF
    rs2.MoveFirst 
    While not rs2.EOF
        'Do your comparisons here'
         if rs1("colum").value = rs2("column").value then
            'do other stuff
         end if  
        rs2.MoveNext
    Wend
rs1.MoveNext
Wend

If you need to compare each field you can iterate over the fields similar to the following This assumes the recordsets have the same fields in the same order

Dim i as integer
for i = 0 to rs1.Fields.Count -1
  if rs.fields(i).Value = rs2.fields(i).value then
      'DO other stuff'
  End if
Next i

Answer 2

A much better solution would be to do the comparison in the Query/SQL instead of the code such that each mismatched row is returned from a single query.

For example

SELECT T1.*,T2.* 
FROM T1,T2
WHERE (T1.ID=T2.ID) AND 
     ((T1.Field1<>T2.Field1) OR (T1.Field2<>T2.Field2) ...)