Link to home
Start Free TrialLog in
Avatar of shadie
shadie

asked on

opposite of while not rs.eof

Hopefully I can explain this the way it's happening and how I'd like it to work...

The code below is a small piece of the larger code set but it's the part that blows up. I have two strings, APCColor and APCPrice. Each one is made up from concatenated rs results and are comma-delimited. I delimit both into uBound(arrays) and compare the results against each other. The example is APCColor = "123,124,125" and APCPrice = "123,124,128,129,130,150". After the lines are split by comma, compared the results are printed to the web page. This example would print 123 & 124 as they are the only two that match!

Here's the code to do this:
----------------------------
Dim strAryWords, strAryWordsPrice
Dim strValue, strValuePrice

strValue = APCColor
strAryWords = Split(strValue, ",")

strValuePrice = APCPrice
strAryWordsPrice = Split(strValuePrice, ",")

Dim i, k
For i = 0 to Ubound(strAryWords)
      For k = 0 to Ubound(strAryWordsPrice)
            if strAryWordsPrice(k) = strAryWords(i) then
            response.Write strAryWordsPrice(k) & "<BR>"
            end if
      Next
Next
--------------------

Now, what I want to do is in the second loop (for k = 0 to uBound....)
create a record set from a different table, with a different set of results. Staying with the same example, my rs would only return 123. Now, if I do:

while not rs.eof
      if strAryWordsPrice(k) = strAryWords(i) then
      response.Write strAryWordsPrice(k) & "<BR>"
      end if
rs.movenext
wend

I get 123 as the only result and I understand why this works! But, what I want to do is return the one that is NOT contained in this recordset, which would be 124. Remember, it was a match from the compare, but not in the nested recordset. I've tried different variations of while not isnull, or if isnull(rs but nothing seems to work.

BTW, my select for the nested recordset is:
"select product from tblImageStatus where product = '" & strAryWordsPrice(k) & "'"

A collegue suggested using "where product <> '" & strAryWordsPrice(k) & "'"

but what this does is step and repeat the same results for as many hits where the codes DON'T match in the rs which is not what I want.

Any help would be greatly appreciated!
Avatar of hb21l6
hb21l6

while not rs.eof
     if strAryWordsPrice(k) <> strAryWords(i) then
     response.Write strAryWordsPrice(k) & "<BR>"
     end if
rs.movenext
wend

Avatar of shadie

ASKER

hb21|6,
Thanks for the quick response, but that didn't do it. What happened was it returned all the results for the codes in the string that didn't match, whereas before, I was at least getting the matches between strings correct. This is just the opposite.
Avatar of raj3060
Need to print both string elements,
while not rs.eof
     if strAryWordsPrice(k) <> strAryWords(i) then
     response.Write strAryWordsPrice(k) & "<BR>"
     response.Write strAryWords(i) & "<BR>"
     end if
rs.movenext
wend
Do you mean, if strings are
123,124
123,124,125,126

then you should get
125,126
Avatar of shadie

ASKER

raj3060,
No, 123 & 124 are where the two strings match so that is the first part of what should be worked with. The rs that I'm trying to use produces 123 ONLY. So I want to display 124.
You are comparing these two
 APCColor = "123,124,125" and APCPrice = "123,124,128,129,130,150"

and getting 123,124.. that's what you are looking in first part.. We are OK here

Then you are comparing 123, 124 with what??
and what do you want??
Did you try to store 123, 124 in some string to compare with the third string;
or after 123 matches with 123 compare it with third string, and so on...
give all three strings and the final result you want to see..
Avatar of shadie

ASKER

>>Then you are comparing 123, 124 with what??
and what do you want??

I'm comparing 123 & 124 with the rs results. In this example, the rs returns 123. As for what I want, I want to display the one not in the rs results and that is 124.

>>Did you try to store 123, 124 in some string to compare with the third string

No, I did not try this. I'm guessing the same logic I used to create the first two strings should be applied here, split and compared and display where <>
I am not sure that I understand completely, but it sounds like you might want to use the InStr() method.

So, break the first string into the array, but don't worry about the second, just do:

if inStr(arrYourArray(i) & ",",objRS("fldYourField")) > 0 then
  'you have a match
end if

FtB
Avatar of shadie

ASKER

FtB,
Would it help if I pasted the whole, narsty code? I didn't do that at first because there are a ton of recordsets, loops, etc. (all without so much as a comment too what I am doing...).

Basically I'm taking two rs results and loading them into a comma-separated string. 123,124,125 and 123,124,128,130. I then split the strings into an array and iterate through the pieces and compare. Does 123 = any of array2 parses, yes, response.write, does 124=any of array2 parses, yes-response.write it. Does 125 = any of array2 parses, NO move next (in this case stop because 125 is the last item.

I am then creating a recordset completely different from the two above. Does the new rs result match anything that was just response.written? The way I have the code now is YES, newRS returns 123 and that's what it is writing. (while not newRS.eof).

What I want to return as the final answer is where my array parses <> newRS. This example is 124. 124 was one of the matches on array1 and array2 comparison but not part of newRS results. This is the one I want to keep. Or for other examples, all of the numbers that were matches but not part of newRS results.
SOLUTION
Avatar of raj3060
raj3060
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I don't think that it is necessary to split anything more than one of the fields into an array. If you split the first and then use the InStr() method , all should be fine. Please see:

http://www.devguru.com/Technologies/vbscript/quickref/instr.html

FtB
Recordset is already a set (array), you do not need to store it in another array as long as all three recordsets have different name.
Not the recordset, the field.

FtB
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of shadie

ASKER

guys,
Thanks for all your help. I was able to figure it out. Thanks to some trial and error but mostly through your suggestions and direction. I am going to split the points if that's ok with you both. Below is my solution. What I found I was doing is trying to step through a recordset (while/wend) when I didn't need to. It was displaying the record when I wanted to display the first array if the rs.eof.

---------------------------------
Dim strAryWords, strAryWordsPrice
Dim strValue, strValuePrice

strValue = APCColor ' set as comma-delimit string from rs1
strAryWords = Split(strValue, ",")

strValuePrice = APCPrice ' set as comma=delimit string from rs2
strAryWordsPrice = Split(strValuePrice, ",")

Dim i, k

For i = 0 to Ubound(strAryWords)

      For k = 0 to Ubound(strAryWordsPrice)

            if strAryWordsPrice(k) = strAryWords(i) then
                  dim cn500, sql500, rs500, rs500Str
                  set cn500 = Server.CreateObject("ADODB.Connection")
                  cn500.Open "Images2"

                  Set rs500 = Server.CreateObject("ADODB.Recordset")
                  sql500 = "select product from tblImageStatus where product = '" & strAryWordsPrice(k) & "'"
                  rs500.CursorType=0
                  rs500.CursorLocation=2
                  rs500.LockType=1
                  rs500.Open sql500, cn500
' this is where I had the while/wend that was messing me up!!
                        if rs500.eof then
                        response.write strAryWordsPrice(k) & "<BR>"
                        end if
            end if
      Next
Next
Avatar of shadie

ASKER

I'm concerned that splitting those points won't be enough so I'm going to up a bit then split (if that's ok...)
Glad to have helped and good luck!

FtB
Thanks Shadie, I am glad you got the solution.
Dr. Fairfield, I didn't mean to offend you. I hope you forgive me. I am working on PhD, but having alot of trouble with my advisor, technicaly I don't have an Advisor now. He dumped me, not my fault, some misunderstanding. Looks now that all my time and effort I put in PhD is gone waste.
Thanks
@raj3060--

No offense at all!

>> Looks now that all my time and effort I put in PhD is gone waste<<

A few off-topic things to say here:

1) I don't think that time you put into learning something and exploring ideas is ever wasted; you grow intellectually, and grappling with difficult concepts changes you in a way that only becomes apparent years later

2) If this is a misunderstanding or a personality conflict, I urge you in the strongest possible terms to see your academic Dean to see if things can't be sorted out.

FtB