Prev: ASP Content Display (aspx expert)
Next: IIS7 ASP Response object incompatible with MSXML transformNodeToObject
From: Old Pedant on 29 Jul 2008 01:22


"zz12" wrote:

> It looks like a co-worker had changed the data type that it uses on our SQL
> Server from 'float' to 'decimal'

That's the killer! VBScript can *NOT* handle the DECIMAL data type!!!!

It can't do *ANY* arithmetic on DECIMAL values.

It can *hold* a DECIMAL value, because all variables in VBScript are
actually "variants" (if you don't know what that means, don't worry about it)
and indeed vt_Decimal is a valid variant.

So if all you do is *output* a DECIMAL value, the underlying COM code can
handily convert decimal to text for you and all is well.

But you simply can *NOT* do arithmetic using DECIMAL.

The correct answer is to *ALWAYS ALWAYS ALWAYS* convert *ALL* DECIMAL values
to DOUBLE using CDBL( ).

response.write CDBL(rs("FloatField") + 987.123456

Have you considered shooting the person who made that change in the DB
without testing *ALL* uses of the DB? In our shop, he'd be lucky to still
have a job.

&&&&&&&&&&&&&

There is another solution, by the way. You can convert the data from
DECIMAL to DOUBLE (or even to CURRENCY) in SQL. Whether this is easier or
harder than just finding all the RS("FloatField") occurrences and adding on
the CDBL( ) call is up to you.

&&&&&&&&&&&&&

p.s.: Bob Barrows is right; a NULL used with CDBL( ) would cause the same
error message. But given that the code worked until the datatype was
changed, I'm betting that NULLs are *not* the problem.


From: Old Pedant on 29 Jul 2008 01:35


"Bob Barrows [MVP]" wrote:

> Incidentally, I agree with your co-worker's decision to move away from
> Float.

I have to VERY STRONGLY disagree with this statement!

Oh, it's nice in principal. But changes like this should NEVER be made
without testing ALL uses of the given database table(s) and field(s).

In this case, the change to DECIMAL indeed means that none of the simple
arithmetic that VBScript was doing will work any more. Every single usage of
those fields in VBScript that involve any kind of calculations will now need
to have CDBL( ) calls added to them.

Quite possibly or even probably, using CDBL( ) *is* the right long-term
solution. But to have it foisted on the site like this, because the idiot
changed the DB without thorough testing, is enough reason to put a really
really black mark on the person's record.

Move away from FLOAT? Okay. But *ONLY* after considering *ALL* the
consequences.

If you are curious about the details:

This shows that the OLEDB type dbtype_decimal is converted to vt_decimal by
ADO:
http://msdn.microsoft.com/en-us/library/bb231286(VS.85).aspx

This shows how DECIMAL is represented in a Variant *AND* shows that
vt_decimal has the value 14 (0x0E).
http://msdn.microsoft.com/en-us/library/cc251799.aspx

And this shows that VBScript does *NOT* understand vt_decimal:
http://msdn.microsoft.com/en-us/library/3kfz157h(VS.85).aspx

If you ever get a decimal value from a DB, as in
whatever = RS("someDecimalField")
if you then do
Response.Write VarType(whatever)
you will find that the answer is 14, thus demonstrating that VBScript
variables *CAN* hold values that VBScript doesn't really understand.

When you then do
whatever = CDBL(whatever)
you are calling COM's variantChangeTypeEx( ) function and *IT* is capable of
understanding and converting many more variant types than VBScript is aware
of.


From: Old Pedant on 29 Jul 2008 02:34
If you can't read all of this thread, the reason for this demo won't be
obvious. But it's just too much to copy into this msg.

But maybe the demo itself will be clear, anyway.

http://www.clearviewdesign.com/Newbie/Demos/DataTypes.asp

If you hit that page, you'll see a "dump" of ONE RECORD from a single Access
database table. I purposely put one field of each of the most usual Access
field types in that table, excepting only DateTime and blobs. Then I put
data into only one record, just for demo purposes. Both BIT and YESNO end up
as the same ADO type (and then of course the same VBS type).

So this page just "dumps" that one record from that one table.

But it "dumps" in what I hope is an enlightening way. The name of each
field indicates what type of field it is. I used a CREATETABLE sql statement
to generate the table, so that I could use both CHAR(10) and VARCHAR(10).
And, as you can see, those are seen as separate ADO data types, though by the
time they get to VBS they are both just strings.

Note that for data types where precision and/or numericScale are
meaningless, ADO supplies 255 as a default value. So the only data type that
shows *BOTH* precision and scale is, as expected, the DECIMAL field at the
bottom. (Currency always has a scale of 4, but I guess ADO doesn't care
about that.)

Note that, indeed as I predicted in an earlier post, the decimalField has a
VBS VarType( ) of 14.

But then notice the last cell in the table: AHA! An error!
"Variable uses an Automation type not supported in VBScript"
So even TypeName( ) can't use that variant type!

AND YET look at the VALUE column!!! There the DECIMAL value is in it's full
(18,6) glory! WHY? Simply because Response.Write *always* needs a STRING to
write and so, when asked to write this DECIMAL variant, it calls the COM
variantChangeTypeEx( ) function which happily converts the DECIMAL to STRING!!

And *THAT* is why you *CAN* use DECIMAL database fields with VBScript *IF*
you don't do any calculations on them! But also, as VarType( ) clearly
demonstrates, you can't do much of anything else with them.

And, yes, that clearly includes arithmetic. Of any kind.

-- Bill Wilkinson

From: Old Pedant on 29 Jul 2008 02:39
Incidentally...

If you are curious what the difference between char10Field and
varchar10Field is, aside from the ADO type number, just change the line
<TD><%=val%></TD>
to
<TD><%=Replace(val," ","^")%></TD>

If you do that, you'll see that none of the value displays change *EXCEPT*
for the char10Field, and its value is then displayed as
abc^^^^^^^

In other words, the char(10) field is space filled to the full size of the
field, just as you woud expect!!



From: Anthony Jones on 29 Jul 2008 03:58
"Old Pedant" <OldPedant(a)discussions.microsoft.com> wrote in message
news:D9198E07-0942-4342-B6F9-321BF4AA54F1(a)microsoft.com...
>
>
> "Bob Barrows [MVP]" wrote:
>
> > Incidentally, I agree with your co-worker's decision to move away from
> > Float.
>
> I have to VERY STRONGLY disagree with this statement!
>


I'm rarely bothered by matters of nettiquete and am often irritated when
someone posts a reply merely to correct some matter of netiquette. However
the use of capitals in the above manner is considered to be shouting and you
seem to have a tendancy to use it. I think your opinion is well made and
there is no need to shout. If you would like to emphasise something use
_this point its emphasised_ or *this is also emphasised*.

So as not to break my own rules ;) I have will agree that untested
refactoring is bound to lead to problems. However, I don't believe Bob
implied that it was ok to make the change untested.

--
Anthony Jones - MVP ASP/ASP.NET


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: ASP Content Display (aspx expert)
Next: IIS7 ASP Response object incompatible with MSXML transformNodeToObject