?
Solved

opposite of while not rs.eof

Posted on 2004-09-30
21
Medium Priority
?
1,242 Views
Last Modified: 2012-06-21
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
Comment
Question by:shadie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 6
  • +1
21 Comments
 
LVL 2

Expert Comment

by:hb21l6
ID: 12191075
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

by:shadie
ID: 12191186
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

by:raj3060
ID: 12191346
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 11

Expert Comment

by:raj3060
ID: 12191385
Do you mean, if strings are
123,124
123,124,125,126

then you should get
125,126
0
 
LVL 1

Author Comment

by:shadie
ID: 12191464
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

by:raj3060
ID: 12191570
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

by:raj3060
ID: 12191573
give all three strings and the final result you want to see..
0
 
LVL 1

Author Comment

by:shadie
ID: 12191734
>>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

by:fritz_the_blank
ID: 12192059
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

by:shadie
ID: 12192422
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

by:raj3060
raj3060 earned 800 total points
ID: 12192509
Why are you loading it to the string, load it to array, and compare.
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

by:fritz_the_blank
ID: 12192558
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

by:raj3060
ID: 12192604
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

by:fritz_the_blank
ID: 12192698
Not the recordset, the field.

FtB
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 800 total points
ID: 12192833
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

by:shadie
ID: 12193466
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
0
 
LVL 1

Author Comment

by:shadie
ID: 12193499
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

by:fritz_the_blank
ID: 12193543
Glad to have helped and good luck!

FtB
0
 
LVL 11

Expert Comment

by:raj3060
ID: 12193694
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
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12199266
@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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question