Money in SQL and ASP.NET

Hi,
I'm having some trouble with my asp.net page and my sql database. What I'm trying to do is allow the user to upload an number to the database, the number is a money amount like 2.00 (￡2.00) or 20.00(￡20.00). I've tried using money and smallmoney datatypes but the numbers usually end up looking like this in the database...
I enter 2.00 and in the database it looks like 2.00000, and even if I enter the information directly into the database I get the same results. I'm not going to be using big numbers with lots of decimal places like this 1000,000,0000. Can anyone help me? All I want is to know what to set the value to on my aspx page and what setting to set the field to in my database, I'd just like two pound to appear as 2.00. any help would be great.

I'm using Microsoft Visual Web Developer 2005 Express Edition and Microsoft SQL Server 2005 if that helps.

Thanks.

Hi,

The money or smallmoney datatypes are the right ones to use in your case. By design, they have four decimal at the end and you can find relevent infromation related to these two from Books Online. As to your question, you use either of these types in your database to hold your data and when it is time to show your data on your asp.net page, you can format them into what you need. For example, {0,c2} will give you two decimals as you want. In GridView:

<asp:BoundField HeaderText="Price/Unit" DataField="UnitPrice" DataFormatString="{0:c2}"HtmlEncode="false"> </asp:BoundField>

Or

<asp:TemplateFieldHeaderText="UnitPrice"><ItemTemplate><asp:LabelID="Label2"runat="server"Text='<%# Eval("UnitPrice","{0:c2}") %>'></asp:Label></ItemTemplate></asp:TemplateField>|||

Hi,

You don't say what type of controls you're trying to display this in. However, one simple way (that I think you can apply to any text box or label control) is this:

TextBox1.Text = (512.23).ToString("c")

If you need to set the page to a different currency than your own, one way is to set the UICulture of the page directive:

<%@. Page Language="VB" AutoEventWireup="false" UICulture="en-GB" CodeFile="Default4.aspx.vb" Inherits="Default4" %>

You can also set this in the web.config file (under globalization)

How the numbers are stored in SQL aren't relevant to how they'll be displayed - as you've discovered!

Hope this helps.

Paul