Entry

How do I do a 2 table field comparison in a RS Do loop and return data from the second table?

Feb 14th, 2002 03:29
Luiz Paulo Rosa, Dave Morrison,

'Create Objects
set Conn = Server.CreateObject("ADODB.Connection")
set RecSet1 = Server.CreateObject("ADODB.Recordset")
set RecSet2 = Server.CreateObject("ADODB.Recordset")
'Open Connection
Conn.Open "MyDSN"
'Execute SELECT statement on both tables
RecSet1.Open "SELECT Field1 FROM Table1"
RecSet2.Open "SELECT Field1 FROM Table2"
'Loop through recordsets
While NOT (RecSet1.EOF OR RecSet2.EOF)
  'Do comparisson
  If RecSet1("Field1") = RecSet2("Field1") Then
    'Write value from Table2 (RecSet2)
    response.write "Field1 of Table 2: " & RecSet2("Field1") & "<BR>"
  End If
  'Never forget this!
  RecSet1.MoveNext
  RecSet2.MoveNext
Wend