I am new to SQL Server 7, and I was wondering what datatype everyone uses for currency (US currency) fields?

I have a field where the user inputs a dollar amount (for example, $25.00), but the database will not retain the formatting or sybmols. When I retrieve that field on an ASP page, the data appears like 25.

I've tried these datatypes, money, numeric, and varchar, but none of these types will retian the currency format and symbols.

What am I doing wrong? Any suggestions?

Thank you in advance.

Andrea

I tend to use a integer field for any numeric value then format how it is displayed on the page.

WHen you drop the field onto the page and then in the bindings tab change the format to 2 decimal places. Hard code the $ symbol on the page.

Regards
Vince

Response.write("The best line of code you can ever use"

VBScript | ASP | HTML | SQL | Oracle | Hosting

Use a datatype of money for the field in the db.

$25.00 will show as 25 in the field.

When you display the field on the page:
<%= FormatCurency(rsName.Fields.Item("YourMoneyField".Value %>

This will display as $25.00

The Interger datatype can ony store whole numbers so you can't store any decimal places or cent values in the field.

Also, a note about the numeric or decimal datatype in SQL Server.

If you want to use these with ASP you have to collect the value as varchar and convert it to decimal in the database. e.g.

@SomeIntegerValue int,
@MyDecimalValue varchar(10),
@SomeTextValue varchhar(30),


INSERT INTO TableName(IntegerField, DecimalField, TextField) VALUES (@SomeIntegerValue, CONVERT(decimal(18,4),@MyDecimalValue), @SomeTextValue)

Where 18 is the precision and 4 is the scale (Number of decimal places)


Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Edited by - oeastwick on 13 Dec 2002 16:41:03