469,271 Members | 1,014 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

SQL Server and VBscript decimal problem

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>"
Jul 19 '05 #1
6 7863
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.

--
http://www.aspfaq.com/
(Reverse address to reply.)


"Chris Kennedy" <ch**********@cybase.co.uk> wrote in message
news:e4**************@tk2msftngp13.phx.gbl...
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>"

Jul 19 '05 #2
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]" <te*****@dnartreb.noraa> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
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.

--
http://www.aspfaq.com/
(Reverse address to reply.)


"Chris Kennedy" <ch**********@cybase.co.uk> wrote in message
news:e4**************@tk2msftngp13.phx.gbl...
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>"


Jul 19 '05 #3
> Any ideas.

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

--
http://www.aspfaq.com/
(Reverse address to reply.)
Jul 19 '05 #4
Chris Kennedy wrote:
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
--
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.
Jul 19 '05 #5
I'll bear that in mind

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eE**************@TK2MSFTNGP10.phx.gbl...
Chris Kennedy wrote:
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
--
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.

Jul 19 '05 #6
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


"Aaron [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message
news:OJ****************@tk2msftngp13.phx.gbl...
Any ideas.


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

--
http://www.aspfaq.com/
(Reverse address to reply.)


Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Mick Turner | last post: by
6 posts views Thread by Andy Wawa | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.