More arrays & math .help

# Thread: More arrays & math .help

1. Senior Member
Join Date
Dec 1969
Posts
339

## More arrays & math .help

I&#039;m trying to add values of a single dimension array.<BR>here&#039;s the code I&#039;m working with<BR><BR>for i=0 to UBound(Quantity)<BR>SQL="SELECT * FROM ProdDescription WHERE ProductNumber="&replace(ProductNumber(i),",","")&" "<BR>set rs=conn.execute(SQL)<BR>zoneshipping=RS(""&zone&"" )<BR>shipping=zoneshipping * Quantity(i)<BR>response.write shipping<BR>next<BR><BR>what this ends up doing is multiplying my shipping charges with the quantity.. that works.. but how do I add the final result together.<BR><BR>for example..<BR>Quantity(1)=1<BR>Quantity(2)=2<BR>zon eShipping(1)=3.00<BR>zoneShipping(2)=2.00<BR><BR>w hat this gives me is<BR>shipping =3.004.00<BR>how do I add those values together in the loop?<BR>Thank you :)

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## And database help

&nbsp; &nbsp; zoneshipping=RS(""&zone&"")<BR>Bogus! It works by accident!<BR><BR>You *should* simply be doing:<BR>&nbsp; &nbsp; zoneshipping=RS(zone)<BR><BR>***************<BR><B R>Other kind of crazy things:<BR>&nbsp; &nbsp; replace(ProductNumber(i),",","")&" "<BR><BR>Are you saying that you have a comma *INSIDE* the productnumber as it is found in your array? I somehow doubt it! I&#039;ll bet you did a split to get the ProductNumber array and are confusing that with what you need to with apostrophes for string fields.<BR><BR>And why do you tack on the " "??? It doesn&#039;t change the query, at all.<BR><BR>********************<BR><BR>The worst thing of all is that you query the database once each time through the loop!<BR><BR>And then you do SELECT * and get *all* the fields from each record, even though you only use one field in the loop!<BR><BR>*************<BR><BR>Having said all that...<BR><BR>Have you ever used a calculator? Especially a printing calculator?<BR><BR>How do you add up a bunch of numbers (you know, like balancing your checkbook)?<BR><BR>You enter the first number. You hit + (or minus, but not in this case). You enter another number. You it +. You ...<BR><BR>So why not do it the same way with the computer?<BR><BR>*******************<BR><BR>I&#039 ;d like to get rid of your one-query-per-loop, but until/unless I knew more about the rest of your data, I can&#039;t. So...<BR><BR>&#060;%<BR>...<BR>&#039; I dunno if you want the shipping array, but I&#039;ll<BR>&#039; create it, just in case:<BR>Dim shipping()<BR>ReDim shipping( UBound(Quantity) )<BR>totalShipping = 0.0<BR><BR>for i=0 to UBound(Quantity)<BR>&nbsp; &nbsp; SQL="SELECT " & zone & " FROM ProdDescription WHERE ProductNumber=" & ProductNumber(i)<BR>&nbsp; &nbsp; set RS = conn.execute(SQL)<BR>&nbsp; &nbsp; zoneshipping = RS(0) &#039; the only thing in the RS!<BR>&nbsp; &nbsp; RS.Close &#039; you really should do this!<BR>&nbsp; &nbsp; shipping(i) = zoneshipping * Quantity(i)<BR>&nbsp; &nbsp; Response.Write "Shipping for item " & i & " is " & quantity(i) _<BR>&nbsp; &nbsp; &nbsp; &nbsp; & " times " & zoneshipping & " = " & shipping(i) & "&#060;br/&#062;"<BR>&nbsp; &nbsp; totalShipping = totalShipping + shipping(i) &#039; push the + key on calculator<BR>Next<BR>Response.Write "total shipping: " & totalShipping & "&#060;P&#062;"<BR>...<BR>%&#062;<BR><BR>********* ***********<BR><BR>I hope you won&#039;t take this wrong, but you *REALLY* need to get a very very beginning book on programming and work through *all* the lessons. This concept (running a total) is usually taught in the first week or at latest the second week of any programming class, for example.<BR><BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
339

## RE: And database help

Thanks bill.. I will SERIOUSLY work through this and I do appreciate your comments.. :) Gotta start somewhere :)<BR>Thanks

4. Senior Member
Join Date
Dec 1969
Posts
339

## few more quetions bill..

yes, I was using the split function, it was the only way I knew how to get the array. I was getting the values from single field that was looped through. like so:<BR><BR>&#060;%SQL="SELECT * FROM ProdDescription"<BR> set rs=conn.execute(SQL)<BR> while not rs.eof<BR> %&#062;<BR> &#060;TR&#062;<BR> &#060;TD&#062;&#060;INPUT TYPE="text" size="5" NAME="Quantity"&#062;<BR> &#060;TD&#062;&#060;INPUT TYPE="hidden" name="productnumber" value="&#060;%=RS("ProductNumber")%&#062;"&#062;&# 060;%=RS("ProductNumber")%&#062;&#060;/TD&#062;<BR> &#060;TD&#062;&#060;INPUT TYPE="hidden" name="pDescription" value="&#060;%=RS("pDescription")%&#062;"&#062;&#0 60;%=RS("pDescription")%&#062;&#060;/TD&#062;<BR> &#060;TD&#062;&#060;INPUT TYPE="hidden" value="&#060;%=RS("Price")%&#062;" name="unitprice"&#062;&#060;%=formatcurrency(RS("P rice"))%&#062;&#060;/TD&#062;<BR> &#060;TD&#062;&nbsp;&#060;/TD&#062;<BR> &#060;/TR&#062;<BR> &#060;%<BR> rs.movenext<BR> wend<BR> rs.close<BR> %&#062;<BR><BR>so when the data went tothe next page.. I had to split it to make the array? How would you have set up the array (I fully admit that I SUCK at arrays, but I&#039;m trying)?<BR><BR>question two:<BR>In the code you so generously wrote for me you have zoneshipping=RS(0)<BR><BR>for i=0 to UBound(Quantity) <BR> SQL="SELECT " & zone & " FROM ProdDescription WHERE ProductNumber= "&replace(ProductNumber(i),",","")<BR> set RS = conn.execute(SQL) <BR> zoneshipping = RS(0) &#039; the only thing in the RS! .....<BR><BR>what exactly does this do, I have seen it numerous times.. but hey I&#039;m still a beginner.. can you explain this to me or point me to something to read on it? <BR>Thanks<BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## And good questions!

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## With all that in mind...

&#060;% <BR>... <BR>strProdNums = Request("ProductNumber")<BR>ProductNumbers = Split( strProdNums, ", ")<BR><BR>&#039; move this out of loop (and choose your own ORDER BY)<BR>SQL="SELECT " & zone & ", ProductNumber FROM ProdDescription WHERE ProductNumber IN (" & strProdNums & ") ORDER BY ProductNumber"<BR>set RS = conn.execute(SQL) <BR><BR>&#039; I dunno if you want the shipping array, but I&#039;ll <BR>&#039; create it, just in case: <BR>Dim shipping() <BR>ReDim shipping( UBound(Quantity) ) <BR>totalShipping = 0.0 <BR><BR>for i=0 to UBound(Quantity) <BR>&nbsp; &nbsp; If RS.EOF Then<BR>&nbsp; &nbsp; &nbsp; &nbsp; Response.Write "Unexpected EOF!"<BR>&nbsp; &nbsp; &nbsp; &nbsp; Response.End<BR>&nbsp; &nbsp; End If<BR>&nbsp; &nbsp; If RS("ProductNumber") &#060;&#062; ProductNumbers(i) Then<BR>&nbsp; &nbsp; &nbsp; &nbsp; Response.Write "Mismatch on product ordering!"<BR>&nbsp; &nbsp; &nbsp; &nbsp; Response.End<BR>&nbsp; &nbsp; End If<BR>&nbsp; &nbsp; zoneshipping = RS("zone")<BR>&nbsp; &nbsp; shipping(i) = zoneshipping * Quantity(i) <BR>&nbsp; &nbsp; Response.Write "Shipping for item " & i & " is " & quantity(i) _ <BR>&nbsp; &nbsp; &nbsp; &nbsp; & " times " & zoneshipping & " = " & shipping(i) & "&#060;br/&#062;" <BR>&nbsp; &nbsp; totalShipping = totalShipping + shipping(i) &#039; push the + key on calculator <BR>&nbsp; &nbsp; &#039; move to next record as we move through the loop!<BR>&nbsp; &nbsp; RS.MoveNext<BR>Next <BR>If Not RS.EOF Then<BR>&nbsp; &nbsp; Response.Write "too many records? mismatch on length?"<BR>&nbsp; &nbsp; Response.End<BR>End If<BR><BR>Response.Write "total shipping: " & totalShipping & "&#060;P&#062;" <BR>... <BR>%&#062; <BR><BR>Do you understand all that? *NOW* I am getting *all* the records, in an order that matches the arrays from the prior page, so that I can easily loop through all the data at the same time.<BR><BR>*****************<BR><BR>One last thing: Do you know the zone by the time you get to the *first* page you showed????<BR><BR>If you do, we could eliminate the recordset on the second page entirely, by simply putting the zone-based info into yet another hidden field!<BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
339

## um.. wow.. ok.

That there was a tad over my head.. lol. Alas, It is good for me to see complexities and to mull through them. Here&#039;s how it goes. <BR>I have them pop up the first page and then enter their Zip code.. I grab the first 3 numbers of that and put that into a sessionvariable this way if they want to update the form the Zip doesn&#039;t have to be held in a form field or querystring. <BR> then they enter their order quantities etc.. and hit update and it goes to page 2 where the calculations take place that we are working on. The first section of code that is used on "order2.asp" is this<BR><BR>&#060;%<BR>Dim intQuantity,intUnitprice<BR>Quantity=request.form( "Quantity")<BR>Unitprice=request.form("unitprice") <BR>Productnumber=request.form("productnumber")<BR >pDescription=request.form("pDescription")<BR><BR> <BR><BR><BR>Quantity=split(Quantity)<BR>Unitprice= split(unitprice)<BR>Productnumber=split(productnum ber)<BR>pDescription=split(pDescription)<BR><BR>zi p=session("zip")<BR>zip=left(zip,3)<BR>SQL="SELECT * FROM ZIPCODES where Zip=&#039;"&Zip&"&#039; "<BR>set rs=conn.execute(SQL)<BR>zone="Zone"&RS("zone")<BR> rs.close<BR><BR>%&#062;<BR>--followed by some HTML crap-- <BR>and the calculation code.. so yes, we do know the zone before we get to the calculation code. :)<BR>This is great stuff.. and I do appreciate you taking the time to walk me through it :). I hope you see this. <BR>Thanks!

8. Senior Member
Join Date
Dec 1969
Posts
339

## SUCCESS!!

My god thank YOU SO MUCH!! In this case it actually didn&#039;t matter what order the shipping was because it was just adding up the totals anyway, and that of course doesn&#039;t not count on order. So here&#039;s what I have:<BR><BR> strProdNums = Request.form("ProductNumber") <BR> ProductNumbers = Split( strProdNums, ", ") <BR><BR> <BR>SQL="SELECT " & zone & ", ProductNumber FROM ProdDescription WHERE ProductNumber IN (" & strProdNums & ") ORDER BY ProductNumber" <BR>set RS = conn.execute(SQL) <BR><BR> <BR><BR>Dim shipping() <BR>ReDim shipping( UBound(Quantity) ) <BR>totalShipping = 0.0 <BR><BR>for i=0 to UBound(Quantity) <BR> If RS.EOF Then <BR> Response.Write "Unexpected EOF!" <BR> Response.End <BR> End If <BR> zoneshipping = RS(0) <BR> shipping(i) = zoneshipping * Quantity(i) <BR> totalShipping = totalShipping + shipping(i) &#039; push the + key on calculator <BR> RS.MoveNext <BR>Next <BR>If Not RS.EOF Then <BR> Response.Write "too many records? mismatch on length?" <BR> Response.End <BR>End If <BR><BR> response.write formatcurrency(totalShipping)<BR><BR><BR>and it works like a charm. I&#039;m going to read that FAQ on using IN. that will save a crap load of time and coding. :)<BR>Thanks again! :)

9. Senior Member
Join Date
Dec 1969
Posts
96,118

## you are making a **MISTAKE**

You *need* to be sure that the data is IN THE SAME order or you *will* get wrong results!<BR><BR>So long as the quantity of items being shipped is the same, it doesn&#039;t matter.<BR><BR>But let&#039;s say somebody buys <BR>10 One-ton Air Conditioners (shipping \$1000 each)<BR>1 Wing Nut (shipping 34 cents)<BR><BR>But because you don&#039;t process them in the same order as they come from the form, you end up charging:<BR><BR>10 * 0.34 = \$3.40<BR>1 * \$1000 = \$1000<BR>Total: \$1003.40<BR><BR>*INSTEAD* of the \$10,000.34 that you *should* have charged!!!<BR><BR>YOU *NEED* that test that I had in there to ensure things were in the same order! Put it back in. I saw you sneakily leave it out to "make things work". But they are *NOT* working if that test fails!<BR><BR>

10. Senior Member
Join Date
Dec 1969
Posts
339

## RE: you are making a **MISTAKE**

Ok. I put it back in and the calculation always fails. <BR>I have the same select order for both queries.. <BR>Here is the query from the first page with the form.<BR>---------<BR>&#060;%SQL="SELECT * FROM ProdDescription ORDER BY ProductNumber"<BR> set rs=conn.execute(SQL)<BR> while not rs.eof<BR> %&#062;<BR> &#060;TR&#062;<BR> &#060;TD&#062;&#060;INPUT TYPE="text" size="5" NAME="Quantity"&#062;<BR> &#060;TD&#062;&#060;INPUT TYPE="hidden" name="productnumber" value="&#060;%=RS("ProductNumber")%&#062;"&#062;&# 060;%=RS("ProductNumber")%&#062;&#060;/TD&#062;<BR> &#060;TD&#062;&#060;INPUT TYPE="hidden" name="pDescription" value="&#060;%=RS("pDescription")%&#062;"&#062;&#0 60;%=RS("pDescription")%&#062;&#060;/TD&#062;<BR> &#060;TD&#062;&#060;INPUT TYPE="hidden" value="&#060;%=RS("Price")%&#062;" name="unitprice"&#062;&#060;%=formatcurrency(RS("P rice"))%&#062;&#060;/TD&#062;<BR> &#060;TD&#062;&nbsp;&#060;/TD&#062;<BR> &#060;/TR&#062;<BR> &#060;%<BR> rs.movenext<BR> wend<BR> rs.close<BR> %&#062;<BR>-------------------------<BR><BR>however when I removed the check and I repdroduced your example given, I did get the correct shipping. <BR>Just for kicks I put in more products with various costs etc.. and the result was accurate everytime.. <BR><BR>if you&#039;re curious I can send you the files etc. I&#039;m sure you don&#039;t have the time... but if you want them, you are more than welcome to look.

#### Posting Permissions

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