ADO or String.Format bug ? ......

[Nicola Cisternino]

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()
==============================================================================
What's your idea ?
Thanks.
Nicola.

 

[Marina]

Your queries are different. The datareader gets a 'select Freigh from
orders', your adapter gets a 'select 0 from orders'. This is not a clean
experiment, as it is not clear exactly what the query is retrieving.

 

[Nicola Cisternino]

Sorry, i've posted the wrong example ..... in my real tests the queries
are absolutely identical !!! (SELECT Freight FROM orders)

Thanks.

 

[Nicola Cisternino]

I've also noticed that the problem occurs only using money and
smallmoney SQL types ....

 

[David Browne]

I've also noticed that the problem occurs only using money and smallmoney
SQL types ....

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