Simple Math Problem

1. Senior Member
Join Date
Dec 1969
Posts
228

## Simple Math Problem

I am updating a table with info form a form. I need the total to reflect the combined values of subtotal + freight both in the table after the update query and then on the form.<BR><BR>I am experimenting with SQL statemens like:<BR>POtotal = (&#039"&request.form("subtotal")&"&#039) + (&#039"&request.form("freight")&"&#039)<BR><BR>whi ch are IGNORED! <BR><BR>Is there an SQL statment for this.<BR><BR>Here is the who SQL statment:<BR><BR>mysql="Update PurchaseOrder Set "_<BR> & " issuername = &#039"&request.form("issuername")&"&#039, "_<BR> & " dateawarded = &#039"&request.form("dateawarded")&"&#039, "_<BR> & " paymentterms = &#039"&request.form("paymentterms")&"&#039, "_<BR> & " destination = &#039"&request.form("destination")&"&#039, "_<BR> & " shipvia = &#039"&request.form("shipvia")&"&#039, "_<BR> & " FOBTerms = &#039"&request.form("FOBTerms")&"&#039, "_<BR> & " CIF = &#039"&request.form("CIF")&"&#039, "_<BR> & " budgetcode = &#039"&request.form("budgetcode")&"&#039, "_<BR> & " subtotal = &#039"&request.form("subtotal")&"&#039, "_<BR> & " tax = &#039"&request.form("tax")&"&#039, "_<BR> & " freight = &#039"&request.form("freight")&"&#039, "_<BR> & " POtotal = this needs to be subtotal PLUS Freight<BR> & " where rfqid=&#039 909&#039"<BR><BR><BR>(I can add it together on the form, but since the two values making up the total are coming from the table after this is submitted, the total obviously is not entered into the table yet-- that is only happening if I submit twice!<BR>

2. J Guest

## RE: Simple Math Problem

Have you tried using variables?<BR>Dim dblTotal<BR>dblTotal = CDbl(Request.Form("subtotal")) + CDbl(Request.Form("freight"))<BR><BR>Then use the dblTotal variable in your query and to display on your page. Hope this helps.<BR><BR>J

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

## RE: Simple Math Problem

(1) WHY would you have a numerical value enclosed in quotes???<BR><BR>Surely you will store things such as subtotal, tax, freight, POtotal, etc. as NUMBERS. If you don&#039t, you will *never* be able to make reasonable database queries against the values.<BR><BR>And if they are numbers in the DB, then they *can not* be assigned &#039...&#039 values in the SQL.<BR><BR>(2) So you are asking SQL to apply the + operator to a pair of quoted values: Something like<BR> &nbsp; POtotal = &#039 7.95&#039 + &#039 1.25&#039<BR>*IF* it worked, it would have to CONCATENATE the two strings...because you are working VERY hard to tell it that those values are *NOT* numbers.<BR><BR>(3) Ever heard of "DEBUG"? Did you try to look at what your resultant SQL is? Via something like:<BR> &nbsp; Response.Write "&#060;HR&#062;" & mysql & "&#060;HR&#062;"<BR><BR>(4) VBScript is a wonderful little language. Why not let *IT* do the addition instead of making SQL do useless extra work?<BR> &nbsp; thePOtotal = = CDbl(request.form("subtotal")) + CDbl(request.form("freight"))<BR>and *then* do<BR> &nbsp; ... & "POtotal = " & thePOtotal & "," _ <BR><BR>(Note the lack of quote marks.)<BR><BR>************<BR><BR>But I think you *really* need to go through and fix that DB so the numeric fields are declared to be numeric! And then get rid of &#039...&#039 around numeric values.<BR><BR><BR><BR>

4. jd
Senior Member
Join Date
Dec 1969
Posts
850

## RE: Simple Math Problem

Bill&#039s right. And it gets worse than that. I am going to bet that this poster comes back and states that his/her database fields ARE numeric, and that his queries still work. And guess what? That would be true, because SQL Server will automatically attempt to perform type conversion. BUT, this has two very negative effects.<BR><BR>First, it slows down all of your queries and your entire database engine. It shouldn&#039t have to do that work.<BR><BR>Second (and this is the ghastly one), SQL Server at some point will begin to report database corruption, invalid indexes, and much worse. This does NOT happen at some logical point (like, say, when your integer values exceed 32767 or 65535, or anything you would expect), but rather at some point when the database hits a certain size. It&#039s enough to make you mess your pants when you see those messages start appearing.<BR><BR>Trust me, I know (I inherited a system like this!)

5. Senior Member
Join Date
Dec 1969
Posts
228

## RE: Simple Math Problem

I had them as money values in the SQL, but table wouldn&#039t allow the money fields to be updated with the query unless I took out all the money items here, and so I made everything nvarchar.<BR><BR>mysql="Update PurchaseOrder Set "_<BR> & " issuername = &#039"&request.form("issuername")&"&#039, "_<BR> & " dateawarded = &#039"&request.form("dateawarded")&"&#039, "_<BR> & " paymentterms = &#039"&request.form("paymentterms")&"&#039, "_<BR> & " destination = &#039"&request.form("destination")&"&#039, "_<BR> & " shipvia = &#039"&request.form("shipvia")&"&#039, "_<BR> & " FOBTerms = &#039"&request.form("FOBTerms")&"&#039, "_<BR> & " CIF = &#039"&request.form("CIF")&"&#039, "_<BR> & " budgetcode = &#039"&request.form("budgetcode")&"&#039, "_<BR> & " subtotal = &#039"&request.form("subtotal")&"&#039, "_<BR> & " tax = &#039"&request.form("tax")&"&#039, "_<BR> & " freight = &#039"&request.form("freight")&"&#039, "_<BR> & " POtotal = POstr<BR> & " where rfqid=&#039 909&#039"<BR> conn.execute(mysql)

6. Yuck. Guest

## RE: Simple Math Problem

SO I&#039m not clairvoyant, but still... double yuck.

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

## Get rid of Currency fields...

...unless you will use reports directly generated by the DB, there is no point in using them. Double (real) fields work just fine.<BR><BR>BUT...If you *must* use them, you *might* be able to get them to work by using VBScript CCur function. (I&#039ve never tried it, fair warning.)<BR><BR>Thus:<BR><BR>... & " subtotal = " & CCur( Request.Form("subtotal") ) & "," _ <BR>...<BR><BR>I *do* know that if you made the fields numeric and simply wanted to strip out dollar signs and commas from form input, then the following *should* work (still no guarantees):<BR><BR>... & " subtotal = " & CDbl( CCur( Request.Form("subtotal") ) ) & "," _ <BR>...<BR><BR>It&#039s a cute trick: CCur strips out the dross and *then* you convert the VBS currency to a Double numeric value for storage in the DB.<BR><BR>YOU WILL BE SORRY if you leave those fields as VarChar, I double guarantee it, along with "Yuck" (grin).<BR><BR>

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

## DEMO of CCur and CDbl et al.

Take the code below; save it as an ASP file in an appropriate spot.<BR><BR>Run the page in your browser. Type a dollar value such as \$3,456.78 into the text box. Push the SUBMIT button. Look at what is reported.<BR><BR>See? CCur is a fun and powerful little function that can be useful in situations like this.<BR><BR>********* FILE: anyNameYouWant.asp **********<BR>&#060;HTML&#062;&#060;BODY&#062;<BR> <BR>&#060;%<BR>raw = Request("fld")<BR>cur = CCur(raw)<BR>val = CDbl(cur)<BR>%&#062;<BR><BR>Raw is &#039&#060;% = raw %&#062;&#039 and is type &#060;% = TypeName(raw) %&#062;<BR><BR>Cur is &#039&#060;% = cur %&#062;&#039 and is type &#060;% = TypeName(cur) %&#062;<BR><BR>Raw is &#039&#060;% = val %&#062;&#039 and is type &#060;% = TypeName(val) %&#062;<BR><BR><BR>&#060;HR&#062;<BR><BR>&#060;FOR M&#062;<BR>&#060;INPUT Name="fld" Size=40&#062;<BR><BR><BR>&#060;INPUT Type=Submit&#062;<BR>&#060;/FORM&#062;<BR>&#060;/BODY&#062;&#060;/HTML&#062;<BR>************************************ *****

9. Senior Member
Join Date
Dec 1969
Posts
228

## RE: DEMO of CCur and CDbl et al.

Thanks for all the commentary. <BR><BR>I am trying to absorb all this -- on a deadline!

#### Posting Permissions

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