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. This kind of string concatenations are open for SQL Injection attacks. Also dispose your database connections and objects with using statement. — Soner Gönül
– Soner Gönül, Commented Nov 27, 2014 at 13:50
@ Soner Gönül yes i will use parameterized queries. but what i have to do for this prob! — Capten
– Capten, Commented Nov 27, 2014 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? — valverij
– valverij, Commented Nov 27, 2014 at 14:00

marc_s · Accepted Answer · 2014-11-27 14:05:18Z

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();

Uwe Hafner · Accepted Answer · 2014-11-27 14:08:01Z

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
@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.

Collectives™ on Stack Overflow

Data Type Money C#

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related