"Nicola Cisternino" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I've also noticed that the problem occurs only using money and smallmoney
> SQL types ....
>
> Nicola Cisternino wrote:
>> Hi
>> It seems that String.Format method, when use a ***ZERO VALUE*** object
>> coming from a SqlDataAdapter Fill method works fine, while if the same
>> query is runned using an SqlDataReader ExecuteReader method, the
>> formatted result is wrong ....
>> For my tests i've used the Sql Server 2000 Northwind database and i've
>> updated the Orders.Freight Column to zero (UPDATE Orders SET Freight=0).
>> My general objective is to format all significative decimal values (value
>> > 0)using a mask: #####0.00 and format all zero values using another
>> mask: #####.##.
>> This is obtained with:
>> String.Format("{0:#####0.00;-#####0.00;######.##}", VALUE-TO-FORMAT)
>> The VALUE-TO-FORMAT is obtained either using a server-side cursor
>> (DataReader) and a client-side cursor (DataTable).
>> In the first case it display ",00" (!!! ???) ....
>> ... while using a DataAdapter it (correctly) display "" .....
>>
>> The complete VB.net code is the following:
>> =============================================================================
>> Dim Cn As New SqlClient.SqlConnection("uid=sa; pwd=; database=northwind;
>> server=xdev99")
>> Dim Cmd As New SqlClient.SqlCommand("SELECT Freight FROM orders",
>> Cn)
>> Cn.Open()
>> Dim Dr As SqlClient.SqlDataReader = Cmd.ExecuteReader
>> If Dr.Read Then
>>
>> Console.WriteLine(String.Format("{0:#####0.00;-#####0.00;######.##}",
>> Dr.Item("Freight")))
>> End If
>> Dr.Close()
>>
>> Dim Dt As New DataTable
>> Dim Da As New SqlClient.SqlDataAdapter("SELECT 0 FROM orders",
>> Cn)
>> Da.Fill(Dt)
>> If Dt.Rows.Count > 0 Then
>>
>> Console.WriteLine(String.Format("{0:#####0.00;-#####0.00;######.##}",
>> Dt.Rows(0).Item("Freight")))
>> End If
>>
>> Cn.Close()
>> ==============================================================================
In the DataTable, the value will already have been converted to a .NET type
(System.Decimal probably).
In the DataReader the type is returned as a raw SQL Server type eg
(System.Data.SqlTypes.SqlMoney). String.Format knows things about
System.Decimal, which it doesn't about System.Data.SqlTypes.SqlMoney. In
particular Decimal implements IFormattable, IComparable, and IConvertible,
which help control formatting.
In addition to being another in the long list of why not to use DataReaders,
you can work around this by setting the value to a local variable before
passing it to String.Format.
Dim freight as Decimal = Dr.Item("Freight")
....
David
|
Similar Threads
