Converting varchar to Money

I have a a Case statement which sometimes fails when converting a Varchar
column which contains numeric values to Money. I can understand why it fail
s
when "1.05E+07" is passed in, but other values appear to fail also. I have
not located the other offending values yet (500,000 rows to sift through)
but converting them to Float first avoids the errors. Any ideas?
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
"Server: Msg 235, Level 16, State 1, Line 1
Cannot convert a char value to money. The char value has incorrect syntax."
However the following code, which converts to Float, and then to Money, does
not fail.
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
CONVERT(FLOAT,c1_PriorSalePrice) )(a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
the contents can be converted to any numeric type. See
http://www.aspfaq.com/2390 for the long-winded version of this.
(b) if using convert(money, convert(float())) works, then what is wrong with
using that?
(c) STOP STORING NUMERIC VALUES AS STRINGS!
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:47497632-9B73-416F-9BE4-F729B3CEF653@.microsoft.com...
>I have a a Case statement which sometimes fails when converting a Varchar
> column which contains numeric values to Money. I can understand why it
> fails
> when "1.05E+07" is passed in, but other values appear to fail also. I
> have
> not located the other offending values yet (500,000 rows to sift through)
> but converting them to Float first avoids the errors. Any ideas?
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
> "Server: Msg 235, Level 16, State 1, Line 1
> Cannot convert a char value to money. The char value has incorrect
> syntax."
> However the following code, which converts to Float, and then to Money,
> does
> not fail.
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
> CONVERT(FLOAT,c1_PriorSalePrice) )
>
>|||Brother AAron,
The data in question is being provided in bulk by an outside vendor, so I
have no choice in how the data is provided or in what format. I have never
seen this situation before and it may have implications for other procedures
.
I will go read the link you provided.
Thanks
Michael
"Aaron Bertrand [SQL Server MVP]" wrote:

> (a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
> the contents can be converted to any numeric type. See
> http://www.aspfaq.com/2390 for the long-winded version of this.
> (b) if using convert(money, convert(float())) works, then what is wrong wi
th
> using that?
> (c) STOP STORING NUMERIC VALUES AS STRINGS!
>
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:47497632-9B73-416F-9BE4-F729B3CEF653@.microsoft.com...
>
>

Converting SQLMoney to c# float ?

I'm using c# 2.0.

I have a datareader that reads an invoice line item from a table in my SQL Server database. The UnitPrice field is a Money field in SQL server. At the same time I have an invoice class in my application that has a UnitPrice property which is a float.

How do I convert the SQLMoney to a float using my datareader?

Right now I have:

cm.CommandText =

"SELECT ItemID, Quantity, UnitPrice, Discount FROM tblInvoiceLineItems";using (SqlDataReader dr = cm.ExecuteReader())

{

while (dr.Read())

{

LineItem li =newLineItem();

li.UnitPrice = (

float)(double)dr.GetFloat(3); // cast error here.

lineItems.Add(li);

}

}

It will be easier to use DECIMAL in the database so your employer will not loose money and believe me that is a high probability with FLOAT. This makes it easy to use Visual Basic financial class to calculate your price in the application then use standard convert to convert your value to Decimal which is the same as money in SQL Server. Why because you can only set precision and scale with DECIMAL and NUMERIC in SQL Server. Just a thought the link below covers the FCL(framework class library) types, ADO.NET types and SQL Server types. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms131092.aspx