Displaying data to 2 decimal points

[Guy Hocking]

Hi there,

I have an ASP/SQL 7 application that rips data from SQL Views onto an ASP
page.
I have a money column in a SQL table that contains money income values.

When data is input to this field it needs to be displayed as money (to 2
decimal places - 10.00 or 7.60)
However, this only seems to be occurring when the user puts in the extra 0,
and if 7.6 is entered, it is still displayed as 7.6

I need all data to be displayed as money values (and it is selected as money
in SQL), so is there any way (either in the table design, View design, or
display on the ASP page) to get the data to 2 decimal places, no matter
what?

Thanks for you help, in advance

--
G

www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

 

[Bob Barrows]

Hi there,

I have an ASP/SQL 7 application that rips data from SQL Views onto an
ASP page.
I have a money column in a SQL table that contains money income
values.

When data is input to this field it needs to be displayed as money
(to 2 decimal places - 10.00 or 7.60)
However, this only seems to be occurring when the user puts in the
extra 0, and if 7.6 is entered, it is still displayed as 7.6

I need all data to be displayed as money values (and it is selected
as money in SQL), so is there any way (either in the table design,
View design, or display on the ASP page) to get the data to 2 decimal
places, no matter what?

Thanks for you help, in advance

Use one of these vbscript functions:
FormatCurrency
FormatNumber

HTH,
Bob Barrows

 

[Guy Hocking]

Hi,

Thanks for the response -

Any examples of the functions in my scenario?

Cheers

--
G

www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

 

[Bob Barrows]

Hi,

Thanks for the response -

Any examples of the functions in my scenario?

Given that you have not provided any details about your scenario (where are
you having a problem? Connecting to the database? Retrieving data in a
recordset? Reading the data from the fields in the recordset?), I can't
offer any examples beyond what is shown in the online documentation. Perhaps
that is what you need ... ? Here is a link:

http://tinyurl.com/7rk6

Just look up the functions in there once you have downloaded and installed
it. A Google search or a search of www.aspfaq.com could also provide you
with some examples of their use.


Bob Barrows

 

[Guy Hocking]

Hi there,

Apologies for that.....

I have downloaded the script docs, and it is very useful, ta.....
But i was wondering how i would incorporate this into my SELECT statement?

See entire code below -
*******
<%
Dim strConnect, DataConnection

Set DataConnection = Server.CreateObject("ADODB.Connection")
strConnect = "DRIVER={SQL
Server};SERVER=server;UID=user;PWD=password;DATABASE=database"
DataConnection.Open strConnect
%>
<%
Dim RS
Set RS = DataConnection.Execute("SELECT col2, currencycol FROM view
WHERE col1='" & Session("listbox") & "'")
do until RS.EOF

<a href= "page.asp?col1=<% =RS("col2")%>"><% =RS("col2")%></a>

<% =RS("currencycol")%>

<% RS.MoveNext
Loop %>
*******

How/where can i format the "currencycol" ???

Thanks again

--
G

www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

 

[Bob Barrows]

Hi there,

Apologies for that.....

I have downloaded the script docs, and it is very useful, ta.....
But i was wondering how i would incorporate this into my SELECT
statement?

Why do you need to put it in your Select statement? Just use the appropriate
function when writing the results to your page. Like this:

<% =FormatCurrency(RS("currencycol"),2)%>

If you really want to do it in your query, look up the CONVERT function in
SQL BOL.

Here is an example for you to run in QA:

select convert(varchar(20),cast(45.1213 as money))

Bob Barrows

 

[Guy Hocking]

Wicked, that works great, 1 thing -

Its coming up in dollars $
How can i get it to do it in pounds £

Cheers

--
G

www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

 

[Bob Barrows]

Wicked, that works great, 1 thing -

Its coming up in dollars $
How can i get it to do it in pounds £

It depends on which one you did. I suspect you're talking about
FormatCurrency rather than the T-SQL CONVERT, so add this line of code to
your page:

session.LCID=2057

The problem is that the Regional Settings for the IUSR account are the
default (US) settings. You can override that with the LCID property.

Bob Barrows
PS. If you were talking about the CONVERT alternative, simply prepend a
pound symbol:

select char(163) + convert(varchar(20),cast(45.1213 as money))
or
select '&pound;' + convert(varchar(20),cast(45.1213 as money))

 

[Bob Barrows]

It depends on which one you did. I suspect you're talking about
FormatCurrency rather than the T-SQL CONVERT, so add this line of
code to your page:

session.LCID=2057

Sorry, I should have said
Response.LCID =2057

The Session setting will affect all pages accessed during the session,
unless overridden with the Response setting. If you use the Session setting,
it should be done in global.asa.

Bob Barrows
PS. I just realized you included the sql server setup group in your
crosspost. That did not seem to be an appropriate group so I've just removed
it from this reply. If any sql server group was relevant, it would have been
..programming.