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

Error inserting a number with comma (decimal sign) using stored procedure

Hi experts,

when i access a stored procede on sql-server and i pass a number with a comma (Belgium uses comma instead of a dot) then my code errors out telling me the number of fields does not match the number of provided variables.
(ofcourse the same error pops up when trying to use a insert-statement by using the connection-object in my code (objConn.execute "insert into...")
The datatype of the field in my table is Real.

Surely there must be a workaround, just can't think of any right now.


1 Solution
Have you tried using double "" ""
PaurthsAuthor Commented:
have tried that,
it then errors out, saying "error converting data type nvarchar to real"

btw, it then ads another quote by itself...
objConn.Execute "execute sp_ZaalToevoegen '" & strRoomName & "', "" & sngVal & """
Hi, Paurths.

Don't know if the syntax below will help you, but the examples below provide the format I use for various Conn.Execute statements.  VB is very finicky about the syntax.  Notice in my first example all instances of a comma require "', '" (e.g. double quote,single quote, comma, space, single quote, double quote).  Your example shows "', "" (e.g. double quote, single quote, comma, space, double quote, double quote).

To write a new record:
InsertString = "INSERT INTO DesignNotes (ID, Description, Supplier, Remarks) VALUES('" & txtId & "', '" & txtDescription & "', '" & txtSupplier & "', '" & txtNewRemarks & "')"
Conn.Execute InsertString

To update a record:
txtSupplierValue = "UPDATE DesignNotes Set Supplier='" & txtSupplier.Text & "'"
Conn.Execute (txtSupplierValue & " WHERE ID=" & txtIdValue)

To return search results records:
txtDescription.Text = "%" & txtDescription.Text & "%"
Set MyRecSet = Conn.Execute("SELECT * FROM DesignNotes WHERE Description LIKE'" & txtDescription.Text & "'")

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Also, should there be the "execute" item in your command?  Can you post what "sp_ZallToevoegen" is set equal to?  That would help evaluate your VB-Sql syntax.
You can try to replace the comma with a decimal dot. However you have prior to that eliminate all thousand dots, maybe something like:

Replace(Replace("1.501.225,15", ".", ""), ",", ".")

Then when you read from the database, use FormatNumber(value, 2) to set it back to tje locale decimal separator.


PaurthsAuthor Commented:
hi edwardiii,

the syntax i provided in my second comment was as reply to egl1044's comment.
I'm familiar with the syntax, as far as it goes to "normal" datatypes. (where VB does not freak out when it encounters a comma and it thinks it is a field-separator)


The replace-function does the job!!!

btw; also found another solution; passing the value as a string to my stored procedure and the using the replace-function there also does the job!


@ZNaam varchar(25),
@ZPrijs varchar(15)


Insert into tbl_Zalen
(ZNaam, ZPrijs)


(@ZNaam, replace(@ZPrijs,',','.'))

Thanx for the input,

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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