Showing decimal as money


  • Hi,

    I have a very frustrating issue -

    I have a SQL 2008 Express database that holds money in a Decimal(19, 2) field. EG: 126.20

    I have a stored procedure which pulls this out into an Output parameter -

    ALTER PROCEDURE [dbo].[CustomerFromAccountNumber]
          @AccountNumber nvarchar(10),
          @CustomerID int output,
          @FullName nvarchar(100) output,
          @DefaultInvAddress int output,
          @DefaultDelAddress int output,      
          @TelNumber nchar(12) output,
          @MobNumber nchar(12) output,
          @EmailAddress nvarchar(150) output,
          @CustomerType int output,
          @ParentAccount nchar(9) output,
          @StatusID int output,
          @RepID int output,
          @CurrencyCode varchar(4) output,
          @CurrentBalance decimal output,
          @VatRate int output,
          @VatNumber varchar(21) output,
          @Fax nchar(12) output,
          @Terms varchar(40) output,
          @IsHeadOffice bit output,     
          @LastUpdated datetime output,
          @LastUpdateBy nvarchar(15) output
          -- returns 1 if the User is not found
          declare @err as int
          select      @CustomerID = CustomerID,
                      @FullName = FullName,
                      @DefaultInvAddress = DefaultInvAddress,
                      @DefaultDelAddress = DefaultDelAddress,
                      @TelNumber = TelNumber,
                      @MobNumber = MobNumber,
                      @EmailAddress = EmailAddress,
                      @CustomerType = CustomerType,
                      @ParentAccount = ParentAccount,
                      @StatusID = StatusID,
                      @RepID = RepID,
                      @CurrencyCode = CurrencyCode,
                      @CurrentBalance = CurrentBalance,
                      @VatRate = VATRate,
                      @Fax = Fax,
                      @Terms = Terms,
                      @IsHeadOffice = IsHeadOffice,             
                      @LastUpdated = LastUpdated,
                      @LastUpdateBy = LastUpdateBy
          from Customers
          where AccountNumber = @AccountNumber
          set @err = @@ERROR
          if (@err <> 0)
                return @err
          if (@CustomerID is null)
                return 1

    The column/parameter in question is @CurrentBalance.

    This is pulled out from within Winforms using -

    string SprocName = "CustomerFromAccountNumber";
                using (SqlConnection conn = DatabaseUtilities.GetConnection())
                    SqlCommand cmd = new SqlCommand(SprocName, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    #region Parameters
                    //Return Values
                    SqlParameter pRet = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
                    pRet.Direction = ParameterDirection.ReturnValue;
                    //Input Paramenters
                    SqlParameter pAccountNumber = cmd.Parameters.Add("@AccountNumber", SqlDbType.NVarChar, 10);
                    pAccountNumber.Direction = ParameterDirection.Input;
                    pAccountNumber.Value = accnum;
                    //Output Parameters
                    SqlParameter pCustomerID = cmd.Parameters.Add("@CustomerID", SqlDbType.Int);
                    pCustomerID.Direction = ParameterDirection.Output;
                    SqlParameter pCurrentBalance = cmd.Parameters.Add("@CurrentBalance", SqlDbType.Decimal);
                    pCurrentBalance.Direction = ParameterDirection.Output;
                    int retval = (int)pRet.Value;
                    if (retval == 1)
                        throw new Utilities.CmsNotFoundDBException("A customer with the account number: " + accnum + " not found.");
                        #region Create the customer class
                        Customer customer = new Customer();
                        customer.CustomerID = (int)pCustomerID.Value;
                        customer.CurrentBalance = Convert.ToDecimal(pCurrentBalance.Value);
                        return customer;

    When we set customer.CurrentBalance (which is a decimal) -

    public class Customer
            public decimal CurrentBalance { get; set; }

    It is showing as 126 (no 20p). Any ideas how I can get it to show 126.20?



    terça-feira, 17 de abril de 2012 20:28


Todas as Respostas

  • Something like this

    float inp = 123;
    string outp = String.Format("0.00",inp);


    terça-feira, 17 de abril de 2012 21:59
  • I have changed customer.CurrentBalance to a string and done this in the Data retrieval....

    customer.CurrentBalance = String.Format("0:00", pCurrentBalance.Value);

    But the value goes to "0.00". Ideally I want to keep this value as a decimal as I have additions and subtractions to do.



    terça-feira, 17 de abril de 2012 22:19
  • I think you're going to want to use C (for currency) in your String.Format() call . More about string formatting at Something like "{0:C2}";
    • Editado Lie YouModerator segunda-feira, 23 de abril de 2012 06:43 Edit the link
    • Marcado como Resposta Lie YouModerator segunda-feira, 23 de abril de 2012 06:43
    terça-feira, 17 de abril de 2012 23:15
  • change your stored procedure as like this way just change this : @CurrentBalance decimal(18,2) output instead of @CurrentBalance decimal output , see bold line

    ALTER PROCEDURE [dbo].[CustomerFromAccountNumber]
          @CurrentBalance decimal(18,2) output,

    Please vote if you find this posting was helpful or Mark it as answered.

    • Sugerido como Resposta Kelmen quarta-feira, 18 de abril de 2012 07:33
    • Não Sugerido como Resposta MattyB1989 quarta-feira, 18 de abril de 2012 08:45
    quarta-feira, 18 de abril de 2012 05:58
  • Thanks for the help. Unfortunatly by doing both the previous posts it is coming back as 126.00, not 126.20. How could this be when in the database it IS 126.20??


    • Editado MattyB1989 quarta-feira, 18 de abril de 2012 08:56
    quarta-feira, 18 de abril de 2012 08:47