Data Type Money C#

Question

Someone please help me I don't know why!

When I insert a value for example 3469,2 into my SQL Server database, I get 34692,0000

The column is of type Money and the value is the type double

// code
public void updateligne_facture(String Ref, int Qte,String Reffacture,float prixvente,int tva)
{
        SqlConnection con = new SqlConnection();
        con.ConnectionString = @"Data Source=AKRAM-PC\SQLEXPRESS;Initial Catalog=MM_DataBase;Integrated Security=True";

        con.Open();

        double prix = Qte * prixvente;

        double prix_ttc = prix * (1 + (tva/ 100D));

        String requete = "update lc SET lc.Quantite='" + Qte + "',lc.Prix_HT='"+prix+"',lc.Prix_TTC='"+prix_ttc+"' FROM LIGNES_FACTURE as lc  JOIN  MM_ARTICLE as art ON lc.ID_Article=art.ID  JOIN MM_Facture as f ON lc.ID_Facture=f.ID   WHERE art.AR_Ref='" + Ref + "' AND f.Ref='" + Reffacture + "'";
        SqlCommand command = new SqlCommand(requete, con);
        command.ExecuteNonQuery();
        con.Close();
    }

You should always use [parameterized queries](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/). This kind of string concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. Also dispose your database connections and objects with `using` statement. — Soner Gönül, Nov 27 '14 at 13:50
@ Soner Gönül yes i will use parameterized queries. but what i have to do for this prob! — Capten, Nov 27 '14 at 13:57
If I remember correctly, `decimal` is the preferred data type over `money` in SQL Server, because it is far more precise and usable: [Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?](http://stackoverflow.com/questions/582797/should-you-choose-the-money-or-decimalx-y-datatypes-in-sql-server) — valverij, Nov 27 '14 at 14:00
@valverij It also is slower and wastes space. Money is perfect when money is usable. I use it in a lot of places and outside of certain things the money type really is wonderfull... especially when you deal with multiple of them in a row and more than 10 billion rows. — TomTom, Nov 27 '14 at 14:06

score 3 · Accepted Answer · edited Nov 27 '14 at 14:05

According to Microsoft type mapping guide, SQL Server's Money type is mapped to decimal in C#.

Note: a proper way to make your query is to use parameters, like this:

decimal prix = (decimal)(Qte * prixvente);
decimal prix_ttc = prix * (1 + (tva/ 100M));

String requete = @"UPDATE lc 
                   SET lc.Quantite = @qte, 
                       lc.Prix_HT = @prix,
                       lc.Prix_TTC = @prix_ttc
                   FROM LIGNES_FACTURE as lc
                   JOIN  MM_ARTICLE as art ON lc.ID_Article=art.ID
                   JOIN MM_Facture as f ON lc.ID_Facture=f.ID
                   WHERE art.AR_Ref = @ref 
                     AND f.Ref = @reffacture";
SqlCommand command = new SqlCommand(requete, con);
command.Parameters.Add("@qte", qte);
command.Parameters.Add("@prix", prix);
... // Set other parameters here
command.ExecuteNonQuery();

score 1 · Answer 2 · answered Nov 27 '14 at 14:08

You probably have a problem with decimal separator. From your naming of the variables you have a french(?) system locale. So converting your variable to string inserts a comma for the decimal separator.
Your SQL Server wants a dot as a decimal separator if you use a SQL Statement. so your 3469,2 gets a 34692.

to work around it either
- use a parameterized query which will take care of it (strongly recommended) or
- format the string conversion of your variable to use a dot as a decimal separator. But this will have several downsides as: You are dependent on locales of SQL Server and are prone to injection attacks if you ever use user entries as variable input.

Thank you @Uwe i insert this instruction String p = prix_ttc.ToString().Replace(',', '.'); it works now — Capten, Nov 27 '14 at 14:13
@user3475928 - But that is still the *wrong* way to do it! If you would use parameterized queries, you wouldn't even *have* the problem you had. — Corak, Nov 27 '14 at 14:31

Data Type Money C#

2 Answers2