Click here to Skip to main content
15,036,823 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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;
    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.");

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)
        using (NpgsqlConnection npgsqlConn = new NpgsqlConnection(connString))
            // Open the PgSQL Connection.

            insertCommand = String.Format( "INSERT INTO main.transaction  (documentdate,registrationdate,documentnumber," +                    

"documenttype,description,partnercodename,journaltype,paymentin,paymentout)" + 


            using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(insertCommand, npgsqlConn))
    catch (NpgsqlException ex)
        throw ex;
    catch (Exception ex)
        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
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?
Updated 17-Dec-12 14:11pm
CHill60 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

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.
Zsombi55 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 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 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 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)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900