insert double value in mssql

[tchiloh]

i try to insert data in database, but i have a problem.

example:

double price = 12,55;

mySelectQuery = 
                "INSERT INTO order (total)" +
                "VALUES (" + price+ ");";

now price are 2 values because the ",". now C# see 12 and 55.
so a get a error that a have to many values.

what is the solution?

[finito]

double price = 12.55;

mySelectQuery = 
                "INSERT INTO order (total)" +
                "VALUES (" + price+ ");";

[kvprajapati]

order is reserved word (say order by clause).

double price = 12,55;

mySelectQuery = "INSERT INTO `order` (`total`)" +
                "VALUES (" + price+ ");";

[finito]

Didn't see that nice catch :)

[tchiloh]

double price = 12.55;

mySelectQuery = 
                "INSERT INTO order (total)" +
                "VALUES (" + price+ ");";

the price value comes from the database. the database give 12,55 and not 12.55

[Ketsuekiame]

i try to insert data in database, but i have a problem.

example:

double price = 12,55;

mySelectQuery = 
                "INSERT INTO order (total)" +
                "VALUES (" + price+ ");";

now price are 2 values because the ",". now C# see 12 and 55.
so a get a error that a have to many values.

what is the solution?

That code shouldn't even compile. 12,55 is invalid for a Double. The database, also cannot put that value into a double. (It would need to be converted to double first, the parse would either output 1255 or 12.55 depending on the localisation used)

Double myDouble = Double.Parse("12,55", new CultureInfo("de-DE"));
Console.WriteLine(myDouble);
Console.ReadLine();

Output: 12.55

Double myDouble = Double.Parse("12,55", new CultureInfo("en-US"));
Console.WriteLine(myDouble);
Console.ReadLine();

Output: 1255

Double myDouble = 12,55;
Console.WriteLine(myDouble);
Console.ReadLine();

Output: Build error - Identifier expected So, you cannot have "12,55" in a double. It will be parsed as "12.55" or "1255" depending on your Culture.

Also, unless "price" is an array it cannot hold more than a single value.

Please post the actual code, so we can see what is happening. Also, the SQL Exception would be handy.

[nick.crane]

This is probably an issue with the international rules for decimal place being different.
Extract from Standard Numeric Format Strings Help

The settings in the Regional and Language Options item in Control Panel influence the result string produced by a formatting operation. Those settings are used to initialize the NumberFormatInfo object associated with the current thread culture, and the current thread culture provides values used to govern formatting. Computers using different settings will generate different result strings.

Try this:

string priceString = 
   String.string.Format("{0:N0}.{1:000}", price,(price - Math.Round(price))*1000);
mySelectQuery = 
                "INSERT INTO 'order' ('total')" +
                "VALUES (" + priceString + ");";

Or This

CultureInfo CI = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
mySelectQuery = 
                "INSERT INTO 'order' ('total')" +
                "VALUES (" + price + ");";
Thread.CurrentThread.CurrentCulture = CI;

Or (if it is possible) get the SQL to recognise international number format.
But I don't know how to do that.

[Ketsuekiame]

The way to do this is through using parameters.

You would need to change the SQL query to

Double price = 0;
Double.TryParse("12,55", NumberStyles.Any, new CultureInfo("de-DE"), out price); // Parses 12,55 into 12.55 to be stored in the variable
myQuery = "INSERT INTO 'order' ('total') VALUES(@price)"; // Creates the query string
SqlCommand myCommand = new SqlCommand(myQuery, dbConnection); // Creates the query command and specifies the Database Connection
myCommand.Parameters.Add("@price", SqlDbType.Double); // Adds the @price parameter to the command and specifies the type
myCommand.Parameters["@price"].Value = price; // Sets the parameter value
int rowsAffected = myCommand.ExecuteNonQuery(); // Executes the query
...

That should fix your problem

[nick.crane]

The way to do this is through using parameters.

You would need to change the SQL query to

Double price = 0;
Double.TryParse("12,55", NumberStyles.Any, new CultureInfo("de-DE"), out price); // Parses 12,55 into 12.55 to be stored in the variable
myQuery = "INSERT INTO 'order' ('total') VALUES(@price)"; // Creates the query string
SqlCommand myCommand = new SqlCommand(myQuery, dbConnection); // Creates the query command and specifies the Database Connection
myCommand.Parameters.Add("@price", SqlDbType.Double); // Adds the @price parameter to the command and specifies the type
myCommand.Parameters["@price"].Value = price; // Sets the parameter value
int rowsAffected = myCommand.ExecuteNonQuery(); // Executes the query
...

That should fix your problem

Of course. I generally use the dataset designer which hides the sql commands for me, so I sometimes forget how to format sql command strings with parameters.

[Ketsuekiame]

Don't worry, I tend not to use them either ;) (Although I really should...)