'go to next record in recordset one
rs1.movenext()
Loop
End Sub
Re: I am sure there is a better way
am 12.10.2004 08:00:41 von Ken Schaefer
a) Where are these two recordsets coming from? If from the same database,
can you do the comparison via SQL?
-or-
b) Use .GetRows, so you have two arrays rather than scrolling expensive
recordsets
Cheers
Ken
"Anthony Judd" wrote in message
news:%23vIriiBsEHA.3244@tk2msftngp13.phx.gbl...
>I am comparing the values in two recordsets.
> Does anyone have a beter approach!!!
>
> Very open to suggestions on this...
>
> Thanks...
>
> Public Sub db_select_with_recordset(rs1,rs2,field_value,field_label,msg )
>
> Dim found_match
> found_match = false
>
> 'loop through contents of first recordset
> Do While (NOT rs1.eof)
>
> 'for each item in recordset one, determine whether this is a matching
> value in recordset two
> Do While (Not rs2.eof)
>
> 'compare both recordset values
> If (rs1(field_value) = rs2(field_value)) Then
>
> 'if a match is found create option with selected attribute
> Response.Write("")
> found_match = true
>
> End If
>
> Loop
>
> 'rewind recordset so comparison can begin for next recordset in recordset
> one
> rs2.movefirst()
>
> 'if no match was found for current recordset one value, create option
> without selected attribute
> If (found_match = false) Then
>
> Response.Write("" & rs1(field_name) & "
> found_match = false
>
> End If
>
> 'go to next record in recordset one
> rs1.movenext()
>
> Loop
>
> End Sub
>
>
Re: I am sure there is a better way
am 12.10.2004 08:39:53 von Anthony Judd
These rows are coming from an SQL Server 2000 backend end.
Thanks Ken
"Anthony Judd" wrote in message
news:%23vIriiBsEHA.3244@tk2msftngp13.phx.gbl...
> I am comparing the values in two recordsets.
> Does anyone have a beter approach!!!
>
> Very open to suggestions on this...
>
> Thanks...
>
> Public Sub db_select_with_recordset(rs1,rs2,field_value,field_label,msg )
>
> Dim found_match
> found_match = false
>
> 'loop through contents of first recordset
> Do While (NOT rs1.eof)
>
> 'for each item in recordset one, determine whether this is a matching
> value in recordset two
> Do While (Not rs2.eof)
>
> 'compare both recordset values
> If (rs1(field_value) = rs2(field_value)) Then
>
> 'if a match is found create option with selected attribute
> Response.Write("")
> found_match = true
>
> End If
>
> Loop
>
> 'rewind recordset so comparison can begin for next recordset in
recordset
> one
> rs2.movefirst()
>
> 'if no match was found for current recordset one value, create option
> without selected attribute
> If (found_match = false) Then
>
> Response.Write("" & rs1(field_name) & "
> found_match = false
>
> End If
>
> 'go to next record in recordset one
> rs1.movenext()
>
> Loop
>
> End Sub
>
>
Re: I am sure there is a better way
am 12.10.2004 13:49:36 von Ken Schaefer
Why don't you write a query that does a JOIN on the two tables in question,
so that only rows that have a match are returned?
Cheers
Ken
"Anthony Judd" wrote in message
news:OvaSJZCsEHA.3244@tk2msftngp13.phx.gbl...
> These rows are coming from an SQL Server 2000 backend end.
>
> Thanks Ken
>
> "Anthony Judd" wrote in message
> news:%23vIriiBsEHA.3244@tk2msftngp13.phx.gbl...
>> I am comparing the values in two recordsets.
>> Does anyone have a beter approach!!!
>>
>> Very open to suggestions on this...
>>
>> Thanks...
>>
>> Public Sub db_select_with_recordset(rs1,rs2,field_value,field_label,msg )
>>
>> Dim found_match
>> found_match = false
>>
>> 'loop through contents of first recordset
>> Do While (NOT rs1.eof)
>>
>> 'for each item in recordset one, determine whether this is a
>> matching
>> value in recordset two
>> Do While (Not rs2.eof)
>>
>> 'compare both recordset values
>> If (rs1(field_value) = rs2(field_value)) Then
>>
>> 'if a match is found create option with selected attribute
>> Response.Write("")
>> found_match = true
>>
>> End If
>>
>> Loop
>>
>> 'rewind recordset so comparison can begin for next recordset in
> recordset
>> one
>> rs2.movefirst()
>>
>> 'if no match was found for current recordset one value, create option
>> without selected attribute
>> If (found_match = false) Then
>>
>> Response.Write("" & rs1(field_name) & "
>> found_match = false
>>
>> End If
>>
>> 'go to next record in recordset one
>> rs1.movenext()
>>
>> Loop
>>
>> End Sub
>>
>>
>
>
Re: I am sure there is a better way
am 12.10.2004 17:21:18 von Mark Schupp
Can the recordsets be sorted on the value to be compared?
If so you can do a "shuffle" compare.
determine which recordset (can also use arrays) has the "highest" value.
work forward through the other recordset and do whatever you needed to do
with the values that do not match. When you come to a value that matches or
exceeds the "high" value then switch recordsets.
You could also do an outer join (not exactly what Ken recommends since you
need the values that do not have a match also) and check for null in the
value for the second table.
Hope that's not too terribly unclear.
--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com
"Anthony Judd" wrote in message
news:%23vIriiBsEHA.3244@tk2msftngp13.phx.gbl...
> I am comparing the values in two recordsets.
> Does anyone have a beter approach!!!
>
> Very open to suggestions on this...
>
> Thanks...
>
> Public Sub db_select_with_recordset(rs1,rs2,field_value,field_label,msg )
>
> Dim found_match
> found_match = false
>
> 'loop through contents of first recordset
> Do While (NOT rs1.eof)
>
> 'for each item in recordset one, determine whether this is a matching
> value in recordset two
> Do While (Not rs2.eof)
>
> 'compare both recordset values
> If (rs1(field_value) = rs2(field_value)) Then
>
> 'if a match is found create option with selected attribute
> Response.Write("")
> found_match = true
>
> End If
>
> Loop
>
> 'rewind recordset so comparison can begin for next recordset in
recordset
> one
> rs2.movefirst()
>
> 'if no match was found for current recordset one value, create option
> without selected attribute
> If (found_match = false) Then
>
> Response.Write("" & rs1(field_name) & "
> found_match = false
>
> End If
>
> 'go to next record in recordset one
> rs1.movenext()
>
> Loop
>
> End Sub
>
>