Click here to Skip to main content
14,734,026 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)

I stumble on this SQL statement whose code is this:

var query = "SELECT SUM(replace(montant_total_cmd, ',', '')) FROM Tble_Commande WHERE Annee = '" + Annee_en_Cour + "'";
         using (var cmd = new SQLiteCommand(query, Program.Connex_Bdd))
           int sum = Convert.ToInt32(cmd.ExecuteScalar());
             Program.Montant_ttle = cmd.ExecuteScalar().ToString();
             MessageBox.Show(string.Format("{0:0.00}", sum));

In this example I want to find the sum of (145.15 + 191.86 + 117.60 + 218.80) which normally makes 673.41

Who can help me find the true value with two digits after the decimal point?
thank you

What I have tried:

But with the value below I get 67341

<pre lang="c#">

and with the value below I get 67341.00

string.Format("{0:0.00}", sum)

Despite my research, I can't get 673.41

In my Sqlite database I have commas and not REAL type points.

I had thought of this solution below but the result gives this 67341

double sum = Convert.ToInt32(cmd.ExecuteScalar());
                 MessageBox.Show("" + Math.Round(sum, 2));

Updated 15-Feb-20 23:46pm
Richard Deeming 18-Feb-20 15:53pm
var query = "SELECT SUM(replace(montant_total_cmd, ',', '')) FROM Tble_Commande WHERE Annee = '" + Annee_en_Cour + "'";

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

Why are you converting the returned value to an integer? That just destroys the fractional part. Try this:
double sum = (double)cmd.ExecuteScalar();
MessageBox.Show(string.Format("{0:0.00}", sum));

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