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.

Money data type

Hi,
I have a Price column which has 'money' as the data type.

Then I populated some data into the table. I enter '1.00' in the Price column, then I used the following code to get the data:

Label2.Text = "$" + dataSet1.Tables["products"].Rows[0]["price"].toString();

However, the price is displayed as "$1.0000". But I believe it should display "$1.00". So how can I get rid off the two zeros at the end.

regardsUse string.format("{0:c}", yourvalue)

This will format it in the currency used by your web server.

If you want more control, use "$" & string.format("{0:#,##0.00}", yourvalue)|||Hi PDraigh

I think u may have goven me VB code, actually I was using C#..

string.format("{0:#,##0.00}", yourvalue)

In the above code, what does 'string' represent|||I assume it would still work. 'string' is just the System.String. It's not a variable or object I declared. "{0:#,##0.00}" is the string formatting instruction and 'yourvalue' is just whatever value you want to format using the instruction. Try it, I think it would work in C#, but I don't use C#, so not sure|||yep

String and Format should be capital, such as String.Format("{0:#,##0.00}", price);

However, I still get 2.0000 istead of 2.00|||try,
Label2.Text = String.Format("{0:$##,##0.00}",dataSet1.Tables["products"].Rows[0]["price"]);

or

Label2.Text = String.Format("{0:c}",dataSet1.Tables["products"].Rows[0]["price"]);

money data type

I choose a price field as a money data type, but I cannot change the
scale of this.
the default scale is 4 (it dimmed).
how can I change to 2? I only need 2 scale such as $23.33 instead of
$23.3344If you want the scale to be 2, instead of using MONEY you might want to
consider using DECIMAL or NUMERIC datatype with the required precision &
scale.

--
Anith|||HandersonVA wrote:
> I choose a price field as a money data type, but I cannot change the
> scale of this.
> the default scale is 4 (it dimmed).
> how can I change to 2? I only need 2 scale such as $23.33 instead of
> $23.3344

Use the DECIMAL(<precision>,<scale>) instead of the MONEY data type. E.g.:

sales_price DECIMAL(9,2)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Also the money data type is not a good candidate since there are some
rounding issues with it. Numeric/decimal is the way to go

Denis the SQL Menace
http://sqlservercode.blogspot.com/

HandersonVA wrote:
> I choose a price field as a money data type, but I cannot change the
> scale of this.
> the default scale is 4 (it dimmed).
> how can I change to 2? I only need 2 scale such as $23.33 instead of
> $23.3344