Professional Web Applications Themes

I am sure there is a better way - ASP Database

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("<option value=" & chr(34) & rs1(field_value) & chr(34) & "selected>" & rs1(field_label) & "</option>") found_match = true End If Loop 'rewind ...

  1. #1

    Default I am sure there is a better way

    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("<option value=" & chr(34) & rs1(field_value) &
    chr(34) & "selected>" & rs1(field_label) & "</option>")
    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("<optio value=" & chr(34) & rs1(field_value) & chr(34)
    & ">" & rs1(field_name) & "</option")
    found_match = false

    End If

    'go to next record in recordset one
    rs1.movenext()

    Loop

    End Sub


    Anthony Guest

  2. #2

    Default Re: I am sure there is a better way

    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" <com.au> wrote in message
    news:%phx.gbl... 


    Ken Guest

  3. #3

    Default Re: I am sure there is a better way

    These rows are coming from an SQL Server 2000 backend end.

    Thanks Ken

    "Anthony Judd" <com.au> wrote in message
    news:%phx.gbl... 
    recordset 
    chr(34) 


    Anthony Guest

  4. #4

    Default Re: I am sure there is a better way

    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" <com.au> wrote in message
    news:phx.gbl... 
    > recordset 
    > chr(34) 
    >
    >[/ref]


    Ken Guest

  5. #5

    Default Re: I am sure there is a better way

    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" <com.au> wrote in message
    news:%phx.gbl... 
    recordset 
    chr(34) 


    Mark Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139