SQL Server and VBscript decimal problem

[Chris Kennedy]

I am pulling a decimal value from SQL server and trying to use the format
currency and format number function. I am using these values in a large
string of HTML to be used in a CDONTS Email. If I use the code fragment
below the price variable, which is 3.92 in the database, shows up as £392.
When the formatcurrency is removed it is displayed as 3.92 but!!!! if I
concatenate a "£" it displays £392. I have read some posts about VBscript
not liking decimals, are there any ways around this. I have tried a
conversion to the money datatype, used a variety of csng and cdbl functions.
The odd thing is other numbers from the same column in the recordset format
correctly. The other thing is that variable 'price' is used elsewhere and
calculates correctly it seems VBscript just doesn't like converting it to a
string. Totally stumped!! Can anyone help.

HTML = HTML & "<td>" & formatcurrency(price) & "(&euro;" &
formatnumber(price*rate,2) & ")" & "</td>"

 

[Aaron [SQL Server MVP]]

Don't use the money datatype, use numeric or decimal. Regional settings can
play a factor if you use money/smallmoney... when you insert one number you
might get something totally different.

 

[Chris Kennedy]

Sorry about the multipost. I have never really thought it as a problem as I
have always done it in the past and often got answers from different people.

In regards to the question the datatype in SQL Server is decimal, I only
converted it to money as workaround that might point me in the right
direction. I would think using the cdbl function would work as it has in
similar situations where ASP sees any calculation with a decimal as a type
mismatch. Any ideas.

 

[Aaron [SQL Server MVP]]

Any ideas.

Only if you show your table structure, sample data, ASP code, and desired
results.

 

[Bob Barrows [MVP]]

Sorry about the multipost. I have never really thought it as a
problem as I have always done it in the past and often got answers
from different people.

Nobody cares if you crosspost to relevant newsgroups (ok, I shouldn't say
"nobody", but ... ). It's multiposting that's the problem.

Crosspost: Single message with multiple newsgroups in its To: field
Multipost: Multiple messages with the identical content posted separately to
multiple newsgroups

Bob Barrows

 

[Chris Kennedy]

I'll bear that in mind

Nobody cares if you crosspost to relevant newsgroups (ok, I shouldn't say
"nobody", but ... ). It's multiposting that's the problem.

Crosspost: Single message with multiple newsgroups in its To: field
Multipost: Multiple messages with the identical content posted separately to
multiple newsgroups

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Chris Kennedy]

Here is the table structure

CREATE TABLE [tblProducts] (
[productid] [int] IDENTITY (3426, 1) NOT NULL ,
[productdescriptionid] [int] NULL ,
[subgroupid] [int] NULL ,
[productcode] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[pcksize] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[distributordist_percent] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
,
[distributor_cost_pounds] [char] (50) COLLATE Latin1_General_CI_AS NULL ,
[description] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[distributorprice] [decimal](18, 2) NULL ,
[unitprice] [decimal](18, 2) NULL ,
[disc4] [decimal](18, 2) NULL ,
[disc10] [decimal](18, 2) NULL ,
[disc20] [decimal](18, 2) NULL ,
[weight] [decimal](18, 2) NULL ,
[specialoffer] [bit] NULL CONSTRAINT [DF_tblProducts_specialoffer] DEFAULT
(0),
[add_description] [varchar] (2000) COLLATE Latin1_General_CI_AS NULL ,
[graphic] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[rank] [int] NULL ,
CONSTRAINT [PK_tblProducts] PRIMARY KEY CLUSTERED
(
[productid]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblProducts_tblProductDescriptions] FOREIGN KEY
(
[productdescriptionid]
) REFERENCES [tblProductDescriptions] (
[productdescriptionid]
) ON DELETE CASCADE
) ON [PRIMARY]
GO

This is an email produced as part of a shopping cart system.
In the ASP the idea is if you are logged in as one type of user you get a
distributor price, alternatively you can a different price depending on the
quantity selected. The price variable works well in the various calculations
its just when I format it or concatenate too much. The outputted string is
wrong not the underlying variable. The problem which I replicate is a the
first attempt to format price pulled from the distributor price value in the
recordset.

There is a lot of code here but.....

for each cookiekey in request.cookies("productid")
Key = "key" & i
productid = request.cookies("productid")(key)
rsRecordset.MoveFirst
rsRecordset.Find "productid =" & productid

qty = request.cookies("qty")(key)
if isnull(rsRecordset("expirydate")) then
expirydate = date
else
expirydate = cdate(rsRecordset("expirydate"))
end if

if rsRecordset("specialoffer") <> 0 and date < expirydate then
prodcode = rsRecordset("offercode")
else
prodcode = rsRecordset("productcode")
end if

if rsRecordset("specialoffer") <> 0 and date < expirydate then
prodcode = rsRecordset("offercode")
else
prodcode = rsRecordset("productcode")
end if

if rsRecordset("specialoffer") <> 0 and date < expirydate then

price = cdbl(rsRecordset("price"))

else

if role = "2" then
'displayprice = "test" & formatcurrency(cdbl(price))
price = rsRecordset("distributorprice")

else
if qty < 5 then
price = csng(rsRecordset("unitprice"))
elseif qty > 4 and qty < 11 then
price = csng(rsRecordset("disc4"))
elseif qty > 10 and qty < 21 then
price = csng(rsRecordset("disc10"))
else
price = csng(rsRecordset("disc20"))
end if

if price = 0 then
price = csng(rsRecordset("disc10"))
end if
'response.write price
if price = 0 then
price = csng(rsRecordset("disc4"))
'response.write price
end if

if price = 0 then
price = csng(rsRecordset("unitprice"))
'response.write "test"
end if

end if
end if
subtotal = cdbl(price) * cdbl(qty)
total = subtotal + total
if isnull(rsRecordset("weight")) then
weight = 0
else
weight = cdbl(rsRecordset("weight"))
weight = weight * cdbl(qty)
end if

totalweight = weight + totalweight

HTML = HTML & "<td>" & prodcode & "</td>"
HTML = HTML & "<td>" & qty & "</td>"

'IT IS WHEN I TRY TO FORMAT THE PRICE VARIABLE IN THIS BIT

HTML = HTML & "<td>" & price & "(&euro;" & formatnumber(price*rate,2) & ")"
& "</td>"
HTML = HTML & "<td>" & weight & " kg</td>"

HTML = HTML & "<td>" & formatcurrency(subtotal) & "(&euro;" &
formatnumber(subtotal*rate,2) & ")" & "</td>"
HTML = HTML & "</tr>"
weight = 0
price = 0.0
i = i + 1
next