1

MS SQL Server's Money Data Type seems to accept a well formatted currency value with no problem (example: $52,334.50) From my research MS SQL Sever just ignores the $ and , characters. ASP .NET has a parameter object that has a Type/DbType property and Currency is an available option to set as a value. However, when I set the parameter Type or DbType to currency it will not accept a value like $52,334.50. I receive an error

Input string was not in a correct format.

When I try to Update/Insert. If I don't include the $ or , characters it seems to work fine. Also, if I don't specify the Type or DbType for the parameter it seems to work fine also. Is this just standard behavior that the parameter object with its Type set to currency will still reject $ and , characters in ASP .NET?

Here's an example of the parameter declaration (in the .aspx page):

<asp:Parameter Name="ImplementCost" DbType="Currency" />

So here is a little more information: The code snippet that I provided above is part of the Update parameters to an Update command declared for an SqlDatasSource Control. That parameter will update a column (of money type) of a table in my database. The column in my database is bound to a control in a DetailsView control.

Kristijan Iliev
  • 4,901
  • 10
  • 28
  • 47
Rudi Ramey
  • 21
  • 1
  • 4

2 Answers2

0

Look at the overrides on Parse - one of them accepts a format type parameter, one of the valid values being currency.

Tom Clarkson
  • 16,074
  • 2
  • 43
  • 51
  • Not sure if this is going to work in my particular situation. Application crashes before the OnInserting event occurrs... – Rudi Ramey Apr 21 '10 at 19:15
0

Two issues. (1: .NET ASP layer) and (2:SQL Server at the server)

On the ASP side you are trying to put a string in a money/decimal type.

Try converting the string $123.45 to a decimal type and then to your declaration. Or leave it a string (with checking), or a string 123.45M (M literal) because ...

On the SQL Side the dollar sign is a literal that implies money currency data type with a fixed 4 decimal places. So it is ok to leave it but SQL is not ignoring it. It actually is using it to cast the value.

Here is why I never do what you are doing. You have just discovered what is really going on. The IIS profile and defaults affect why dolars with commas and German marks with decimals are all interpreted correctly. But 123.45 and 123,45 do not mean the same thing under both language profiles. Also on the SQL side similar things happen such as when a literal dollar sign is passed in to sql. So you may want to clean up the value and get it right on the screen for the user and then pass the decimal value up to SQL with no formatting at the SQL layer so that this is not a hidden issue. I personally usually pass a string and fix it and validate it at the server after it is reasonably validated at the client.

Sql Surfer
  • 1,344
  • 1
  • 10
  • 25