InfoPath Dev

Hi,

Im relativly new to Infopath 2013 but Im very used to coding against databses, I have a wierd issue with my info path form. When it displays a number from the database it offsets the decimal by two, basically the value in the database 17499.00 is shown as 174,99 in the infopath form.

Im using the SQL Database to populate drop-down lists in the form and when a selection has been made the details (in this case price) is shown in a text field.

 I have a Product table in the database that has the following columns:

• ID (bigint)
• Name (Varchar(50))
• Price (numeric(18,2)) - I have also tried the money datatype same result

I have saved the following values to this table

• 1
• Product 1
• 17499.00

Something to mention is that I have SQL_Latin1_General_CP1_CI_AS on the SQL Database and SQL Server (However tried to create a new database with Swedish-Finnish_CI_AS but same result).

My computer has the Swedish region settings chosen and also under Infopath "Options" I have Swedish as default both for keyboard and language.

The field in Infopath is set to Decimal(Double) and it set to "Currency symbol" under "Format" with 2 decimal places and "Use a digit grouping symbol". However I have tried a couple of combinations here even tried setting the field as text it still shows this way.

I just cant for the life in me figure out why it would offset by two, only thing I can think of is that it see's the "." in the database as a thousand separator but then the value should not be displayed as 174,99.

Im completely stuck now as all my decimal values return is this fashion, Im sure its a collation issue but cant figure out why!

I would prefer not to have to re-install my SQL Server just to test if its the SQL Server collation that is messing things up.

One would think decimals would work between collations!

Im very greateful for any help on this issue.

Regards,
Claudio

I hope none of you have spent time on this as I found the issue and it has nothing to do with collation.

one thing I forgot to mention (and at the time did not think it mattered) was that there are two colums more in the product table Admin_Margin and Margin.

Both these have percentage values in decimal like 0.05 and 0.2, and that Infopath field had a calculation to add the margins to the product price, but I forgot to put (1+) in the formula so the margin is added to the price and not that the result shows the actual margin... funny coincident was that it was the same value as the original just two decimal places wrong which got me chasing a shadow.

So just to set things straight regarding collation and decimals, you can have a database with same SQL Server and SQL Database collation or different, in my scenario it produces the same result.

However as a DBA I know mixing CI and CS or AI and AS and things like that will produce issues, also if you use Temp tables in your queries and your collations mix between SQL Server and SQL Database you can also run into data conversion issues.

So best case scenario is always to have the same SQL Server and SQL Database collations.

Regards,
Claudio