insert a number with comma into table

[flaviooooo]

Hi,

I'm trying to insert a new record into a table, using the INSERT INTO... statement.
The problem is that one of the values in the records is a number with a comma, and thus SQl thinks I have 1 column to many in my values-string.

Here is the statement:

insert into tblstock(material,Stock,) values(10000025, 106,4)

As you can see I want to add 10000025 to material, and 106,4 to stock.
How can I tell SQl that 106,4 is a number and not 2 separate values?

Thanks in advance!

 

[PHV]

Have you tried to replace this:
106,4)
By this ?
106.4)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Hi,

Assign to a variable

MyVal = 106,4
MySql = "insert into tblstock(material,Stock,) values(10000025, " &  MyVal & ") "

assuming that your regional settings use COMMA as the Decimal Separator.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[flaviooooo]

Hi, thanks for the quick anwers but it still doesn't work. Skip, I tried your solution, but I still get the same error:

Number of query values and destination fields aren't the same. (Error 3346)


PHV: it works when I change the comma to a point, but all our computers have comma set as decimal sign in the regional options

 

[SkipVought]

And you have Defined MyValue as either Single or Double?

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[PHV]

Have you tried something like this ?
sqlVal = Replace(Format(myVal, "0.00"), ",", ".")
BTW what is the type of the variable you want to insert ?
I guess text instead of numeric.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[flaviooooo]

Hi,

I have solved the issue using PHV's solution. I had hoped for an easier solution, but this will have to do ;-)

Thanks guys!