473,224 Members | 1,913 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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 8199
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: jane | last post by:
I use following code to create a Crystal Report by connecting to an access db in ASP. It works fine. Now I switch to SQL Server and it doesn't work. It seems to me there is nowhere I can add login...
9
by: Kathryn | last post by:
Hiya I have a problem with using some client side and server side scripting together in an ASP. I'm using VBScript. What I'm trying to achieve is this - - Page loads up and some server side...
3
by: Mick Turner | last post by:
I have connected to SQL Server database (server-side vbscript) and read some data from the tables. This works correctly. I now have the data in an array (server-side). I have to draw a...
6
by: Andy Wawa | last post by:
Hi, on a simple HTML (not an ASP!)-Site I try to connect to a sql server (MS): <html> <title>Test</title> <head> <script language="javascript"> <!-- Function showForm(){
7
by: skeddy | last post by:
In a nutshell, I'm trying to dynamically create a select box with ResultSet code in vbscript and then need to be able to access the value of that select box later with a Save button. I've got...
1
by: Chris | last post by:
Hi, I have jsut started to learn ASP development and have read many articles regarding which is the best to use regarding JavaScript or VBScript. All of the learning that I have done so far has...
12
by: Light | last post by:
Hi all, I posted this question in the sqlserver.newusers group but I am not getting any response there so I am going to try it on the fine folks here:). I inherited some legacy ASP codes in my...
6
by: avcitamer | last post by:
We upgraded our system and problem below occured, pleas help me... Windows 2003 server SP1 When I set a decimal DB field value to "123,32" (using ADODB.recordset ) updated value was ok (123.32)...
2
by: Rosco | last post by:
Problem: Using the sub routing below, when adding a value to another value the results eventually change from 2-decimal places to multiple decimal places. Basically, the amount stored should...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.