While Wend statement question

jackiew · January 14th, 2015, 12:00 PM

I have a While Wend statement I use quite often to add up the total of a field on an Admin page. Easy for an Admin to see how many people signed up for an event. Works perfectly.

I now would like to use the same statement (with a few changes) to do the opposite, count down to zero.

Original working statement:

HowMany1Tot = 0
While not rs.eof
HowMany1Tot = (HowMany1Tot + rs("HowMany1"))
Rs.Movenext
Wend
Rs.MoveFirst

What I would like it to do now:

HowMany1Tot = 575
While not rs.eof
HowMany1Tot = (HowMany1Tot - rs("HowMany1"))
Rs.Movenext
Wend
Rs.MoveFirst

But, it does not...it won't even show up on the page with my <% = HowMany1Tot %> (which works in the original example).

What am I doing wrong? Can the While Wend statement add but not subtract? Confused....Help!

Thanks
JackieW

mat41 · January 14th, 2015, 06:02 PM

Hello. Yes you can subtract inside the loop, you can do what ever inside any loop. The type of loop does not decide what can be done inside the loop, it is not connected in any way.

To be honest I can not see any issue with your code, in fact it does work and displays the correct result for me. I would say there is a simple issue somewhere, maybe the placement of <% = HowMany1Tot %>. I suggest you place a simple response.write inside your loop and trace it from there. here is me usin your code whith these response.writes in place. I have added a query of my own but otherwise its all your code:

Code:

dim HowMany1Tot, rs
strsql = "select Top 14 obsDate, currentScore from diseaseScores where diseaseID=1 AND pCode=2640 order by obsDate desc;"
set rs = conn.execute(strsql)
HowMany1Tot = 0
While not rs.eof
   HowMany1Tot = (HowMany1Tot + rs("currentScore"))
   response.write HowMany1Tot & " = my increasing running total<bR>"
   Rs.Movenext
Wend
response.write HowMany1Tot & " = my increasing grandTotal<bR><br>"

Rs.MoveFirst

HowMany1Tot = 129
While not rs.eof
   HowMany1Tot = (HowMany1Tot - rs("currentScore"))
   response.write HowMany1Tot & " = my decreasing running total<bR>"
   Rs.Movenext
Wend
response.write HowMany1Tot & " = my decreasing grandTotal<bR><br>"

I am more a fan of using the getRows method to place a record set into an array. Its a very very efficient way of looping. Also notice the position of set rs = nothing. It means your record set is using local memory and not connected the the database. This makes large record sets load three times faster on the page. here is an example using your code:

Code:

dim HowMany1Tot, rs, myArray, i
strsql = "select Top 14 currentScore from diseaseScores where diseaseID=1 AND pCode=2640 order by obsDate desc;"
set rs = conn.execute(strsql)
HowMany1Tot = 0
myArray = rs.getRows()
se rs = nothing
for i = 0 to uBound(myArray, 2)
   HowMany1Tot = (HowMany1Tot + myArray(0,i))
   response.write HowMany1Tot & " = my increasing total<bR>"
next
response.write HowMany1Tot & " = my increasing GrandTotal<bR><br>"

HowMany1Tot = 129
for i = 0 to uBound(myArray, 2)
   HowMany1Tot = (HowMany1Tot - myArray(0,i))
   response.write HowMany1Tot & " = my decreasing total<bR>"
next
response.write HowMany1Tot & " = my decreasing GrandTotal<bR><br>"

jackiew · January 15th, 2015, 11:30 AM

Thanks! It worked like a charm.

I'm also looking at your getRow method and figuring that one out to replace the While Wend statement. Now that the page is working I have time to learn how to do things better.

Thanks again,
JackieW

mat41 · January 15th, 2015, 11:39 PM

No worries, glad to help.

Personally I hardly ever use while loops. I am more a fan of do until loops. That said, im sure this is a personal preference.

I use getRows 70% of the time. I would not use it for a query that is only run and used once that is not large. I always use it for large record sets in loops and especially record sets which I intend on using more than once. IMO doing the whole moveFirst thing is slow and inefficient.

A great test is to take a page which is heavy on database content that is running slow. Change all the record set to arrays (make sure you SET RS = NOTHING immediately after calling getRows) and watch the page load in under half the time.