$Money datatypes in Stored Procedures

I am not sure what to do as I thought removing the $ sign was enough.

Dim var1 = Replace(myResult.resultList(i).listPrice,"$","")' money

And even VS2005 in locales mode shows me "44.00" as the string for var1.

My parameter setup:myCommand.Parameters.AddWithValue("@.listPrice", var1)

In the actual stored procedure I have:

@.listPrice money,

Do I need to Cast this or Convert it anyway?

How would I do that?

I am getting the following error when running my web-app:

"implicit conversion from data type nvarchar to money is not allowed. Use the CONVERT function to run this query."

I have come up with the following solution of passing it as a varchar first, but then casting it to money on the INSERT.

What do you think?

Alter PROCEDURE ap_Insert_Pricing_ListPrice @.partNumbervarchar(50),@.listPricevarchar, @.partDescriptionvarchar,@.prodClassCodevarchar,@.prodClassDescriptionvarchar,@.prodFamilyCodesmallint,@.prodFamilyDescriptionvarchar,@.prodLineCodevarchar,@.prodLineDescriptionvarcharAS--Set @.partNumber = '10-AF40-N04B-JZ'IfNot Exists (SELECT PartNumberFROM PricingWHERE (PartNumber = @.partNumber))INSERT INTO Pricing (PartNumber, ListPrice, PartDescription, ProductClassCode, ProductClassDescription, ProductFamilyCode, ProductFamilyDescription, ProductLineCode, ProductLineDescription)VALUES(@.partNumber,CAST(@.listPriceAs money), @.partDescription, @.prodClassCode, @.prodClassDescription, @.prodFamilyCode, @.prodFamilyDescription, @.prodLineCode, @.prodLineDescription)GO

|||

Set the size for the parameter. >>> partDescriptionvarchar

There could be comma's that you need to get rid of: example $456,677.67

|||

var1=double.parse(myresult.resultlist(i).listprice,system.globalization.numberinfo.any)

that will remove leading/trailing spaces, currency symbols, thousands separators, and then convert the result to a double in the var1 variable.

|||

Hmm my money value is still only inserting the first digit into the database. The rest is getting submitted to the database good.

Alter PROCEDURE ap_Insert_Pricing_ListPrice @.partNumbervarchar(50),@.listPricevarchar, @.partDescriptionvarchar(255),@.prodClassCodevarchar(50),@.prodClassDescriptionvarchar(255),@.prodFamilyCodesmallint,@.prodFamilyDescriptionvarchar(50),@.prodLineCodevarchar(10),@.prodLineDescriptionvarchar(50)AS--Set @.partNumber = '10-AF40-N04B-JZ'IfNot Exists (SELECT PartNumberFROM PricingWHERE (PartNumber = @.partNumber))INSERT INTO Pricing (PartNumber, ListPrice, PartDescription, ProductClassCode, ProductClassDescription, ProductFamilyCode, ProductFamilyDescription, ProductLineCode, ProductLineDescription)VALUES(@.partNumber,CAST(@.listPriceAs money(8)), @.partDescription, @.prodClassCode, @.prodClassDescription, @.prodFamilyCode, @.prodFamilyDescription, @.prodLineCode, @.prodLineDescription)GO

|||

@.listPricevarchar,

You've defined this variable as being able to only hold 1 character. Change it to "@.listPrice varchar(50),"

|||

Ok.

I did (8) since it is money it will max at 8 chars.

Interesting the smallint further down does not suffer the same fate, and 3 digits are entered into the database field as required.