Solved

# opposite of while not rs.eof

Posted on 2004-09-30
1,231 Views
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!
0
• 8
• 6
• 6
• +1

LVL 2

Expert Comment

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

0

LVL 1

Author Comment

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.
0

LVL 11

Expert Comment

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
0

LVL 11

Expert Comment

Do you mean, if strings are
123,124
123,124,125,126

then you should get
125,126
0

LVL 1

Author Comment

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.
0

LVL 11

Expert Comment

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...
0

LVL 11

Expert Comment

give all three strings and the final result you want to see..
0

LVL 1

Author Comment

>>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 <>
0

LVL 46

Expert Comment

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
0

LVL 1

Author Comment

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.
0

LVL 11

Assisted Solution

raj3060 earned 200 total points
take first element of the first array and compare with all elements of second array, if there is a match then display(for first requirement) and store in some array(say array2), and then move forward with second and third and so on...
For your second requirement, now do the same thing with your third recordset, store in array and compare with already compared (matched) element array i.e. array2. If there is a match don't display else dispay.

Another approach could be
Store all recordsets in three arrays.
Compare first and second if match then compare with third, if not match with third then display.
Hope this helps
0

LVL 46

Expert Comment

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
0

LVL 11

Expert Comment

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.
0

LVL 46

Expert Comment

Not the recordset, the field.

FtB
0

LVL 11

Expert Comment

0

LVL 46

Accepted Solution

fritz_the_blank earned 200 total points
I don't want to get off topic here, but look--if a recordset were an array, then why would there be the .GetRows() method to convert the recordset into a two-dimensional array?

So, here is what I am proposing:

1) for the first recordset, iterate through and split each field into your array
2) for each element in the array, use the InStr() method to determine if that array value is found anywhere in the field of the other recordsets

so you will have many loops:

1) through the records in your first recordset
2) an inner loop for the array created by splitting the field in the recordset in 1
3) an inner inner loop for each of the records in the other recordset(s)

FtB
0

LVL 1

Author Comment

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
cn500.Open "Images2"

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
0

LVL 1

Author Comment

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...)
0

LVL 46

Expert Comment

Glad to have helped and good luck!

FtB
0

LVL 11

Expert Comment

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
0

LVL 46

Expert Comment

@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
0

## Featured Post

### Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information â€¦
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interactâ€¦
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet sâ€¦
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.