Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# .NET PostgreSQL
Connection is with npgsql, to PostgreSQL 9.2.
 
Currency value is taken in as string from a TextBox, which is then converted to Decimal, with formatting to have/allow decimal delimiter and group pointer for thousands.
The SQL table variable is of type numeric.
 
This is where I get the string and put it into the decimal variable:
decimal payIn;
if(!decimal.TryParse(textBoxPaymentIn.Text.Trim(), 
    NumberStyles.AllowThousands | NumberStyles.AllowDecimalPoint, CultureInfo.CurrentCulture, out payIn))
{
    //code to display message that txt_price doesn't have a valid value.
    MessageBox.Show("This is not a valid Incasari value! The format has to be" + "#,##0.00 or #.##0,00 according to global regional settings.");
    return;
}
This is where I would put the decimal variable's value as is into the database table's numeric type variable:
//Insert records
public void InsertRecord(NpgsqlTimeStamp docDate, NpgsqlTimeStamp regDate, string docNumber, string docType, string regDesc, string partnerCodeName, string journalType, decimal payIn, decimal payOut)
{
    try
    {
        using (NpgsqlConnection npgsqlConn = new NpgsqlConnection(connString))
        {
            // Open the PgSQL Connection.
            npgsqlConn.Open();
 
            insertCommand = String.Format( "INSERT INTO main.transaction  (documentdate,registrationdate,documentnumber," +                    
 
"documenttype,description,partnercodename,journaltype,paymentin,paymentout)" + 
 
"VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}');",
 
docDate,regDate,docNumber,docType,regDesc,partnerCodeName,journalType,payIn,payOut
            );
    
            using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(insertCommand, npgsqlConn))
            {
                npgsqlCommand.ExecuteNonQuery();
            }                    
        }
    }
    catch (NpgsqlException ex)
    {
        throw ex;
    }
    catch (Exception ex)
    {
        //MessageBox.Show(ex.ToString());
        throw ex;
    }
}
The SQL line for the table column is:
paymentin numeric NOT NULL DEFAULT 0
But whatever I have tried, with comma as group pointer and dot as decimal delimiter, in the database for example 11,111,111.11 was always registered as 11111111.11 .
 
If I understand right, because of the
CultureInfo.CurrentCulture
the user is supposed to be able to use the dot and comma when entering the number according to what is set in the operating system's regional settings' format options for digit group pointer and decimal delimiter. So depending on what is set, either 1,111.11 or 1.111,11 could be valid.
 
But the database accepts only this #,###.## format.
 
How could the database numeric column get what is given from the C# formatted decimal? Or more precisely if the user enters a number with this #.###,## format then how can I make the database accept that?
 
Or should I store them as just varchar? But then how to perform monetary calculations without using a dataTable?
Posted 17-Dec-12 14:34pm
Edited 17-Dec-12 15:11pm
v2
Comments
CHill60 at 17-Dec-12 19:40pm
   
The database is showing the actual number ... you will never get any formatting on the way it is stored. It is only when you want to use the content that you need to format it

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

The database stores the number if you use a numeric type. Thus, it has a value and no rendering details. The format you see, will always be the default or the one you ask for. The value stored does not change.
  Permalink  
Comments
Zsombi55 at 17-Dec-12 20:16pm
   
Then how should I -or can I?- add in a value that is in #.###,## number format? The database numeric gives an exception.
Christian Graus at 17-Dec-12 20:17pm
   
You either specify the format when you RENDER the value, or you store it as a string ( which is stupid ), otherwise it HAS NO FORMAT
Zsombi55 at 17-Dec-12 20:26pm
   
So the formatting which I was asking about, is only 'visual' which is used only to present/render it to the user? it does not matter at calculations?
Christian Graus at 17-Dec-12 20:27pm
   
The raw value will be used for calculations. How it's formatted, unless your format strips some values, will not change the calculations you do with it

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.1411022.1 | Last Updated 17 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100