money format ..need some help..please

Hi everybody.
Here's my Product table

ID int,
Title nvarchar(50),
Price money

How can I get value from price field like this
IF PRICE is 12,000.00 it will display 12,000
IF PRICE is 12,234.34 it will display 12,234.34

Thanks very much...I am a beginner. Sorry for foolish question

Formatting is generally applied at the page where the data is going to be displayed as opposed to altering the datasource itself.

If you were going to databind your sql data to something such as a GridView, then you can alter the data's format by specifying a DataFormatString.

for currency you can use: {0:c}
for numeric data with 2 digits after the decimal you can use: {0:n2}

For example:

 
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="Price" DataFormatString="{0:c}" HtmlEncode=False HeaderText="With Currency Symbol" /> <asp:BoundField DataField="Price" DataFormatString="{0:n2}" HtmlEncode=False HeaderText="Without Currency Symbol" /> </Columns></asp:GridView>
  

If you are actually looking to alter the data in sql server, please let us know.

|||

Thanksmbanavige very much for your answer. I use DataList to display my items and I want to display1 pricein my page. I try to use this code

<%If((Eval("Price") *100) mod 100 >0) { %>

<%#Eval("Price","{0:N2}")%>

<% } else {

%> <%#Eval("Price","{0:c}") %>

<% } %> . But it seem wrong. How can I do something like that ?

|||

I'm not sure i follow what you're trying to do with that code. If you want one price, then choose only one of the sample formats i provided

use this: <%#Eval("Price","{0:c}") %>

or this: <%#Eval("Price","{0:N2}")%>

but not both at the same time.

Unless you're dealing with unpredictalbe currencies, i'd probably just go with: <%#Eval("Price","{0:c}") %>

|||

mbanavige:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="Price" DataFormatString="{0:c}"
HtmlEncode=False HeaderText="With Currency Symbol" />
</Columns>
</asp:GridView>

Hi Mike,

What would cause a page with say ten colums, all formated in the same manner, to only have five colums display currency correctly and the remainder to just be numbers?

I have this weird scenario going on and it is not a syntax error, as I have checked in dozens of times.

|||

Did you set HtmlEncode to False for all the columns?

|||

Yes I did. I just did a character by character study of two similar pages. The first page is for one State the next page for a different State. Every single character is identical. Therefore I have no option to conclude that there must be some fundamental change in the SQL database tables themselves. each State has it's own table. I will go in and make sure that each field is set to exactly the same data type.

If there is any other possibility that you can think of, please let me know. I am certain that this is not a code issue, the code is working, the reason I am not getting the formatting to work is probably because what is expected in the DB table, is something else. I'll drop you a line after I have checked the tables.

|||

Oaky, I was correct, the problem was in the database. The fields in question on the working table were 'money' and the same fields in the non-working table were 'float'. The instant I changed over to 'money', the web pages formatted perfectly.

There is an interesting sideline to this. In the beginning all state tables were identical, and the database itself was hosted on remote servers in Dallas, Texas in the USA. The database is now hosted on remote servers in Sydney, Australia and when the databse was transferred it somehow mysteriously changed. The technique of restoring a backup on the new server, then attaching it, was how we did the transfer, but it was not 100% flawless. We could not then nor now figure out why some very minor changes occured, perhaps there may have been some slight version differences on server software.

At any rate it's all fixed now, so the moral to the tale is this, don't always assume your code is incorrect, sometimes, it might be other things, as in this case, an issue with the database.

Best wishes.