T-SQL convert char to money

conrad2010
conrad2010 used Ask the Experts™
on
I have a string value of 100.00 from a web form and I want to enter it into a SQL talbe cell of the MONEY type.

I am getting the error: "cannot convert  a char value to money. The char value has an incorrect syntax"

Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
how do you try to convert?
cast('100.00' as money) ?

Author

Commented:
ALTER PROCEDURE [dbo].[usp_ATFT_FIN_setTransTempData]
      -- Add the parameters for the stored procedure here
      @TransTempID            INT
      ,@companyID                  INT
      ,@ClientID                  INT
      ,@TransactionName      VARCHAR(200)
      ,@AccountParent            INT
      ,@AccountSub            INT
      ,@DocumentID            INT
      ,@DocumentType            VARCHAR(200)
      ,@Debit                  VARCHAR(200)
      ,@Credit                  VARCHAR(200)
AS
BEGIN
      SET NOCOUNT ON;
      
      IF @TransTempID <> 0
            BEGIN
                  UPDATE
                        tbl_FIN_TransTemp
                  SET       
                        TransactionName = @TransactionName
                        ,AccountParent = @AccountParent
                        ,AccountSub = @AccountSub
                        ,DocumentID = @DocumentID
                        ,DocumentType = @DocumentType
                        ,Debit = CAST(@Debit AS MONEY)
                        ,Credit = CAST(@Credit AS MONEY)

                  WHERE
                        TransTempID = @TransTempID

            END

      ELSE

Author

Commented:
and yes, a string value of 100.00 is passed to this sp...
This simply means your text string contains some additional characters. Try to insert the text into some character column and look at it:

                 UPDATE
                        tbl_FIN_TransTemp
                  SET        
                        TransactionName = @TransactionName
                        ,AccountParent = @AccountParent
                        ,AccountSub = @AccountSub
                        ,DocumentID = @DocumentID
                        ,DocumentType = RTRIM(ISNULL(@DocumentType,'')) + '|' + RTRIM(ISNULL(@Debit, '')) + '|' + RTRIM(ISNULL(@Credit, '')) + '|'
                        ,Debit = CAST(100 AS MONEY)
                        ,Credit = CAST(100 AS MONEY)

Also the string could contain CHAR(0) as the last character which is invisible and causes same error as you described.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial