Decimal comma / point problem in sql

[Tommy DN]

I think I've a simple question.

I'm using the datatype "decimal". I need to build a sql - insert
statement to save something in the database. But the problem is that the
decimal - datatypes are written with a comma " , " and not " . ".

So when I make the string:
--
dim decimalvariable as decimal
sql = "insert into table(decimalthing) values(" & decimalvariable & ")"
--
while decimalvariable is 10,12
my sql string will be:

"insert into table(decimalthing) values(10,12)"

So the sql statement is wrong because it thinks you want to insert 2
variables 10 and 12.

What can I do to make it "insert into table(decimalthing) values(10.12)" ?

PS: I'm living in Europe, we usually write decimals with a comma.

 

[Chris]

I think I've a simple question.

I'm using the datatype "decimal". I need to build a sql - insert
statement to save something in the database. But the problem is that the
decimal - datatypes are written with a comma " , " and not " . ".

So when I make the string:
--
dim decimalvariable as decimal
sql = "insert into table(decimalthing) values(" & decimalvariable & ")"
--
while decimalvariable is 10,12
my sql string will be:

"insert into table(decimalthing) values(10,12)"

So the sql statement is wrong because it thinks you want to insert 2
variables 10 and 12.

What can I do to make it "insert into table(decimalthing) values(10.12)" ?

PS: I'm living in Europe, we usually write decimals with a comma.

There may be a way to do this using culture that I'm not aware of.
You'll need a smarter man like Herfried to answer that. I would do this
like by just converting it to a string and replacing the "," with "."

Chris

 

[Greg Burns]

use a parameter

 

[Cor Ligthert [MVP]]

Tommy,

If you can see the Christmas wishes that you will see how international this
newsgroup is. The English language uses a dot as decimal seperator, all
other European languages a comma even in America and Africa. While in
Europe we use the dot as seperator as well. British and Irish people live as
well in Europe do you know.

However for SQL we use in Net parameters.

In this sample it is showed with a datetime because that is mostly the most
difficult to understand in these question, however it is the same for a
decimal.

http://www.vb-tips.com/default.aspx?ID=886bba68-8a2f-4b99-8f66-7139b8970071

In your case of course instead of CDate, CDec

There are more parameter samples on our website.

I hope this helps,

Cor

 

[Branco Medeiros]

I think I've a simple question.

albeit a religious one, as it seems... :-D

I'm using the datatype "decimal". I need to build a sql - insert
statement to save something in the database. But the problem is that the
decimal - datatypes are written with a comma " , " and not " . ".

So when I make the string:
--
dim decimalvariable as decimal
sql = "insert into table(decimalthing) values(" & decimalvariable & ")"
--
while decimalvariable is 10,12
my sql string will be:

"insert into table(decimalthing) values(10,12)"

So the sql statement is wrong because it thinks you want to insert 2
variables 10 and 12.

What can I do to make it "insert into table(decimalthing) values(10.12)" ?

PS: I'm living in Europe, we usually write decimals with a comma.

You may as well use the InvariantCulture property of the CultureInfo
class:

Const SQL_INSERT As String = "insert into " _
& "table(decimalthing) values({0})"

Dim D As Decimal = 10.12D
Dim SQL As String
'...
SQL = String.Format(SQL_INSERT, _
D.ToString(System.Globalization.CultureInfo.InvariantCulture) )

HTH.

Regards,

Branco.

 

[Claes Bergefall]

International stuff can be a pain to work with

As Greg said, a parameter is the way to go. Don't use concatenation to build
sql strings like you do below since they are vulnerable to sql injection
attacks (I'm assuming that the actual decimal value will come from a textbox
or similar). Check the docs for SqlParameter.

/claes

 

[Tommy DN]

albeit a religious one, as it seems... :-D




You may as well use the InvariantCulture property of the CultureInfo
class:

Const SQL_INSERT As String = "insert into " _
& "table(decimalthing) values({0})"

Dim D As Decimal = 10.12D
Dim SQL As String
'...
SQL = String.Format(SQL_INSERT, _
D.ToString(System.Globalization.CultureInfo.InvariantCulture) )

HTH.

Regards,

Branco.

OK, I've converted the decimal variables with
System.Globalization.CultureInfo.InvariantCulture and it works.

Thanks,
Tommy DN.