?
Solved

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

Posted on 2005-03-23
6
Medium Priority
?
225 Views
Last Modified: 2010-05-02
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.

G's
Ricky


0
Comment
Question by:Paurths
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 29

Expert Comment

by:nffvrxqgrcfqvvc
ID: 13616795
Have you tried using double "" ""
0
 
LVL 12

Author Comment

by:Paurths
ID: 13616900
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 & """
0
 
LVL 10

Expert Comment

by:edwardiii
ID: 13617328
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 & "'")

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 10

Expert Comment

by:edwardiii
ID: 13617358
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.
0
 
LVL 19

Accepted Solution

by:
Shauli earned 1000 total points
ID: 13617705
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.

S

0
 
LVL 12

Author Comment

by:Paurths
ID: 13619618
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)

@Shauli

The replace-function does the job!!!
Thanx


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!

*******************
CREATE PROCEDURE
[dbo].[sp_ZaalToevoegen]

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

AS

Insert into tbl_Zalen
(ZNaam, ZPrijs)

VALUES

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


Thanx for the input,


G's
Ricky
0

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question