none
Showing decimal as money

    Pergunta

  • 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
    )
    AS
          -- 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
                
          RETURN

    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;
                    
                    #endregion
    
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    int retval = (int)pRet.Value;
                    if (retval == 1)
                    {
                        throw new Utilities.CmsNotFoundDBException("A customer with the account number: " + accnum + " not found.");
                    }
                    else
                    {
                        #region Create the customer class
    
                        Customer customer = new Customer();
                        customer.CustomerID = (int)pCustomerID.Value;
                        customer.CurrentBalance = Convert.ToDecimal(pCurrentBalance.Value);
                        customer.PopulateInfo();
                        return customer;
    
                        #endregion
                    }
                }

    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?

    Thanks!


    Matt

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

Respostas

Todas as Respostas

  • Something like this

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

    jdweng

    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.

    Thanks.


    Matt

    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 http://blogs.msdn.com/b/kathykam/archive/2006/03/29/564426.aspx. 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??

    Matt


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