floats from SQL not recognized as numbers (scientific notation parsing issue?)

paolo — Thu, 17 Jan 2013 08:30:16 GMT

Hallo,

I am reporting a weird issue I had.

I was reading a floating point number directly from an SQL table (in this case with value=0.08) into an infopath text box.

In SQL the column had the proper type of an 8 byte float (i.e. a double).

When read into an infopath text box connected to a *text (string)* field it would show in scientific notation as 8.0000000000000002E-2

When read into an infopath text box connected to a *decimal (double)* field it would show as a proper number 0.080000000.

However, it turns out there is a problem with the decimal field, in that the number (although it shows correctly) cannot properly

be parsed as double (for instance all arithmetic operations with it would return NaN, and also the built-in function

number(fieldname) would return NaN).

I did found a workaround - to use VB conversion functions (such as CDbl() ) to convert the string 8.0000000000000002E-2 into

an usable float instead of the built-in number() function.

My speculation on what could possibly be happening is that the float is converted into a string by infopath on reading

the data, and that the built-in function number() is unable to convert this back to a float/double due to the

scientific notation being used in the string - however that does not explain why the number is properly displayed

(maybe yet another parsing routine is used for the display).

Not sure if feature or bug...

Has anyone else encountered this problem? Are you aware of simpler workarounds that do not require custom code?

Paolo Grigis

ABB Switzerland

SQL Database Integration