• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1827
  • Last Modified:

SQL Insert syntax when float decimal is represented with a comma

Hello all, I need a little help.

I have written a C# application that receives data from a connected device.  The connected device will pass over a floating point number ie 1.8897.

I have no problems storing the data if the locale is US - English, but when I change the locale to something where a decimal is represented by a comma my insert query is failing.  

This query works if locale is US:

float tempfloat = 1.888; // Say this value was received from a device that represents decimals with periods

"INSERT INTO TABLE (PrimaryKey, isaFloatValue) VALUES(1, " + tempfloat + ")"

When the local is non US the query is transformed to look like this:
"INSERT INTO TABLE (PrimaryKey, isaFloatValue) VALUES(1, 1,888)" and generates the error below.

Basically the sql statement is failing with the following error "Number of query values and destination fields are not the same.  Microsoft JET Database Engine"

Any help or suggestions are greatly appreciated!
  • 2
  • 2
1 Solution
Use parameterized SQL

"INSERT INTO TABLE (PrimaryKey, isaFloatValue) VALUES(1,@FloatValue)"

Then add @FloatValue as a parameter to the db command.
shawnpbradyAuthor Commented:

Never had to do that before...works beautifully!

Thanks for you help!

Here is an article with syntax for anyone encountering this problem!

Using parameterized SQL queries

shawnpbradyAuthor Commented:
Thanks again!
You should ALWAYS use parameterized SQL.  It saves you from having to escape quotes and deal with stuff like this.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now