Number are displayed in the wrong format

Hi

I'm not sure if this is a .net or a SQL issue. My development machine is using SQL 2005 while the live server is SQL2000. I have a smallmoney field in the database for holding a house rent. The following is used to display the contents on the page

<asp:Label ID="lblrent" runat="server" Text='<%# Bind("rent", "(0:0.00)") %>'></asp:Label
In development, the number is displayed correctly, with the decimal place, .e.g. 200.50 but on the live server the number is displayed as 20050,00.
What I have noticed in the database is that the number is held differently
SQL 2005 - 200.5000
SQL 2000 - 20050

Is there a difference between SQL 2000 and 2005? How do I get around this problem?

Many thanks

Try using this as your formatting string. I'm not sure what would cause that problem.

{0:c}

I hope that helps,

Brendan

|||

oops sorry do both of them list the same datatype for the column? It should be money or smallmoney for both of them. If it is not it might still have let you insert the value and now would be giving an incorrect result.

|||

My guess would be that one of your servers is set to a culture that you aren't expecting. My guess would be the production webserver is set to a culture that uses "," as the decimal separator, and "." as the thousands separator (Considering that it printed the number 20050 as 20050,00).

|||

The datatype for the columns in both databases is smallmoney, which is why I'm a bit confused as to why they are being displayed differently. Even if I manually change the entry in the SQL2000 db to a number with a decimal place, the decimal place is removed.

I tried using {0:C} as the formatstring and what I get back is 20.050,00 € on the server with SQL2000. With SQL2005 I get ￡200.50.

The server where the website hosted is with 1and1 using a MS Business PRO package, so I don't really have a lot of control over the SQL config. I have e-mailed tech support asking for some more information on the default settings for the SQL setup.

If I am using the same code and the same formatstring, the problem must be with the database setup. Right?

|||

No.

Put this in your web.config somewhere in the <system.web> tags:

<globalizationculture="en-US"uiCulture="en-US"/>

Now the SQL2000 server should show $20,500.00 and the SQL2005 server should show $200.50.

How did you get the data from one server to the other? Because whatever process you used also had culture problems, and the data didn't get moved correctly.

|||

Thanks. That solved the problem. I changed it to en-GB, as this is a UK site and I need the ￡ symbol displayed. The numbers are being displayed correctly.

Only the table structure was imported to the server. No data was imported for the table with the money columns. The users are entering the data themselves.

I exported the table structure using the SQL Database Publishing Wizard. I changed the option 'script for server version' to SQL Server 2000 and left the other options at default. Should I have changed any of the other options?