Curious SQL to ASP/HTML Conversion

[KirbyWallace]

This isn't critical, I'm just wondering if anyone can explain this.

I have an ASP (classic) page that is displaying info from a SQL Server 2008 table. The table column in question is a varchar(16) and contains data that is a text representation of a dollar amount - including the dollar sign. (eg, "$7,123.09"). It is definately text. I can open the table directly and see the numbers just like I just showed you. I can delete the dollar sign, the comma and the decimal, and it will save it just fine with my edits intact (ie, no dollar sign, comma or period).

Here's the curious bit.

When I display this data in an asp page, it strips out the dollar sign and comma (decimal point remains). $7,123.09" displays as "7123.09".

The data is not formatted when displayed. (ie, <TD><%=rs("order_amount")%></TD> ). It is not formatted when selected (ie, it's a "SELECT * FROM tbl_name).

Why does it change format when it displays a varchar representation of a dollar amount? And where does this "silent" conversion take place?


ASP/SQL sleuth's have at it....

Kirby

 

[gmmastros]

What does this return?

Select Data_Type, Character_Maximum_Length 
From   Information_Schema.Columns 
Where  Table_Name = 'Player' 
       And Column_Name = 'AverageRating'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[KirbyWallace]

It returns absolutely nothing.

;-)

However, this:

Select Data_Type, Character_Maximum_Length
From Information_Schema.Columns
Where Table_Name = 'tbl_Workflow_Documents'
And Column_Name = 'Order_Amount'

Returns one row:

Data_Type Character_Maximum_Length
---------- ------------------------
VARCHAR 16


Thanks...

 

[gmmastros]

What about this:

Select ColumnProperty(Object_Id('tbl_Workflow_Documents'),'Order_Amount', 'IsComputed')

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[KirbyWallace]

Returns 0 (zero)

 

[gmmastros]

In ASP..... What does this return?

Response.Write TypeName(RS("Order_Amount"))

And this...

Response.Write TypeName(RS.Fields.Item("Order_Amount").Value)



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[KirbyWallace]

Response.Write "<br>TypeName1=" & TypeName(RS("Order_Amount"))
Response.Write "<br>TypeName2=" & TypeName(RS.Fields.Item("Order_Amount").Value)
Response.Write "<br>"

TypeName1=Field
TypeName2=String

 

[gmmastros]

It's a string in the database. It's a string in the recordset. It doesn't make sense that it would strip out anything from it. I'm out of suggestions, sorry.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[KirbyWallace]

Yeah... it's wierd. I'll let you know if I find out anything.

 

[KirbyWallace]

SOLVED!

Sorry to waste your time, George. Here's the deal.

There are a little over one million rows in the table.

When I originally wrote the application in 2006, I stored the order amount fully formatted. After I turned the application over to the client, they changed it to remove this formatting.

You can see the rest..... I have both formatted and unformatted in the table, and I'm looking at the recent stuff... The old stuff still has the formatting. The new values do not.

All's good. Nothing to see here. Move on. ;-)

Thanks, George.....

 

[gmmastros]

Good to know.

As far as the database is concerned, I don't like storing formatted money values. If this were my application, I would have stored the data in a Numeric(20,2) column which has a large enough range and allows for 2 digits after the decimal.

Then, I would probably have created a computed column in the database to show the formatted value. Something like this:

Create Table #TestMoneyFormat(MoneyAmount Numeric(20,2))

Insert Into #TestMoneyFormat(MoneyAmount) Values(9000000000000.01)
Insert Into #TestMoneyFormat(MoneyAmount) Values(219.99)

Alter Table #TestMoneyFormat Add FormattedMoneyAmount As '$' + Convert(VarChar(20), Convert(Money, MoneyAmount), 1)

Select * From #TestMoneyFormat

Drop Table #TestMoneyFormat

The benefit here is that you can use the column as though it's a number (because it is), and you also have a computed column that looks nice for display purposes.

Food for future thought.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[KirbyWallace]

It will be ok, since FormatCurrency() will work fine with both.

Woot!