I have a float field in a MS 2000 SQL Server database that I want to
display in a '00000.000' format in a DataGrid in Visual Basic.NET. 
Either the SQL statement has to return the correct format or there
needs to be some statement that converts the entire column in the
DataGrid.

Any ideas?

Hi, softcom-ga:

Thanks for posting this interesting development question.  I assume
the difficulty here has to do mainly with preserving/guaranteeing the
leading zeroes in the display.  I'll first explain how to accomplish
this on the SQL Server query side of things, then (since your target
application is populating a VB.Net DataGrid) how to accomplish this by
setting the properties of a bound column in your grid control.

You can acheive the desired affect by having the SQL statement return
a string in the given format through a combination of the builtin STR(
) function and some string manipulation.

The simple use of the STR( ) function, say to convert a float to a
string of total length 9 with 3 decimal places, e.g.

SELECT STR(Price,9,3) from tblSales

would produce "trailing zeroes" for you as appropriate (to fill three
places to the right of the decimal), but instead of padding with
"leading zeroes" the STR function pads with leading spaces (blanks). 
Microsoft documents the syntax and functionality of this builtin here:

[Transact SQL Reference STR]
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_412q.asp

Since the format you envision allows for at most 6 leading zeroes,
here's an adapted approach I often use, e.g. to produce fixed length
record export files which specify leading zeroes in numeric fields:

SELECT RIGHT('000000' + LTRIM(STR(Price,9,3)),9) from tblSales

The additional string manipulation strips off the leading blanks (if
any), concatenates more than enough leading zeroes, and then extracts
the rightmost 9 characters of that intermediate result to form the
final expression.  For details of the syntax of RIGHT( ) and LTRIM( )
in Transact SQL, follow the "String Functions" link at the bottom of
the above Web page.

Because you intend to populate a VB.Net DataGrid control with the
data, it is likely to be preferable to accomplish the formatting on
the client side by using the DataFormatString property of the
corresponding "BoundColumn" in your control.  This property can be set
from the Columns tab of the control's Property "builder" in design
mode only after the column is bound to a data source.  An appropriate
formatting expression would be this simple custom numeric formatting
string:

{0:000000.000}

Note that the first character within the brackets is a zero and not a
letter o.

[.Net Framework Class Library BoundColumn.DataFormatString Property]
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemWebUIWebControlsBoundColumnClassDataFormatStringTopic.asp

[.Net Framework Developer's Guide Custom Numeric Format Strings]
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp

An important advantage of implementing the formatting on the control
side rather than in the SQL Server query would be for the case when
the bound data is to be editable.  Locating the format string on the
"client" allows for a consistent approach to validation of the data by
keeping the numeric nature of the values instead of reducing it to a
"lowest common denominator" of string.

If I have not yet completely addressed your question, please post a
request for clarification to this answer.

regards, mathtalk-ga


Search Strategy:

[STR( ) Syntax]
Google Keywords: SQL STR convert format float
Microsoft's SQL Server Books Online: format string convert float

[DataGrid VB.Net Formatting String]
Google Keywords: BoundColumn DataFormatString
Microsoft's VS.Net Integrated Help: DataGrid column formatting
expression

---

Clarification of Answer by mathtalk-ga on 01 Dec 2002 21:17 PST

Oops!  I put one too many leading zeroes in the custom numeric
formatting expression for the VB.Net side of things.  Your question
poses a sample value:

00000.000

with five leading zeroes.  The custom formatting string should
naturally be:

{0:00000.000}

and not {0:000000.000} as indicated in my original answer.  Sorry for
the slip up!

-- mathtalk-ga

Thanks again for the prompt response

Note that the SQL portion of the answer does not round the float field
as I would expect:

print RIGHT('000000' + LTRIM(STR(0.2345,9,3)),9)
spits out 00000.234 rather than the (probably) desired 00000.235

For some funny reason, 
print RIGHT('000000' + LTRIM(STR(0.2346,9,3)),9)
spits out the (expected) value of 00000.235

To avoid problems, you should probably do something like
print RIGHT('000000' + LTRIM(STR(Round(0.2345, 3),9,3)),9)

Hi, davedave:

Thanks for the additional SQL-side suggestion.  One might want 0.2345
to round up to 0.235 rather than 0.234.  It is equally close to both,
but sometimes the business rules will specify one direction over the
other for rounding.

The decimal number 0.2345 is not exactly representable in binary form,
so there is a slight rounding error on conversion to the internal
float format.  As these examples from SQL Server 2000 show, the
results with and without using ROUND may or may not agree:

select STR(0.2345,9,3)
    0.234

select STR(ROUND(0.2345,3),9,3)
    0.235

select STR(0.2335,9,3)
    0.234

select STR(ROUND(0.2335, 3),9,3)
    0.234

Of course 0.2346 is definitely closer to 0.235 than to 0.234, so it
should and does round up.  If one wants the midpoint to round up or
simply wants a more consistent rounding behaviour, your suggestion
appears to be a good one.

While we're on the subject, two more comments about doing the
formatting on the SQL side:

- If negative numbers were allowed, prepending the zeroes doesn't
handle their sign correctly, and the technique would need to be
modified.

- The prepended string of six zeroes is a bit longer than necessary
(four would suffice).

regards, mathtalk