Trouble inserting number with commas into database

Question

I have a problem of inserting numbers with comma into database. It only accepts dot but i have function that only works with commas so is there any idea to solve this like converting decimal seperation from dot to comma

if (radioButton1.Checked)
        {
            Avance = 200;
        }
        else if (radioButton2.Checked)
        {
            Avance = 0;
        }
        cnx.Open();
        SqlCommand cmd = cnx.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "insert into Employeur values('" + this.txt_ID.Text + "','" + this.txt_Nom.Text + "','" + this.txt_QUA.Text + "','" + this.txt_Salaire.Text + "','" + this.txt_NBRJ.Text + "','" + this.txt_HSUP.Text + "','" + this.txt_SalireHeur.Text + "','" + this.txt_Somme.Text + "','" + this.txt_Dette.Text + "','" + this.Avance + "','" + this.txt_Credit.Text + "','" + this.txt_Montant.Text + "','" + this.txt_Paye.Text + "','" + this.txt_Reste.Text + "')";
        cmd.ExecuteNonQuery();
        cnx.Close();
        MessageBox.Show("Se payement est enregistrer");

Lucky for you your syntax is sloppy because the MUCH bigger issue is that this is wide open to sql injection. You need to parameterize your queries before something bad happens. When you create an insert you need to get in the habit of stating the columns you want to insert to. Otherwise when the table changes your code is broken. Don't write brittle code, code defensively. You should also look into the USING statement for things like your command and connection objects. — Sean Lange, Jul 16 '19 at 13:54
I think you should try prepared statements. It will be also safe against sql injection: https://learn.microsoft.com/tr-tr/dotnet/api/system.data.sqlclient.sqlcommand.prepare?view=netframework-4.8 — yavuzkavus, Jul 16 '19 at 13:59
@Thameem yes. https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/security-considerations — Sean Lange, Jul 16 '19 at 14:01
Your [deleted question](https://stackoverflow.com/questions/57054047/error-error-converting-data-type-varchar-to-float) had the answer to this. This is still a duplicate of the one I marked your other question as (and your deleted question). Parametrise your queries and this issue doesn't exist. — Thom A, Jul 16 '19 at 14:05
Duplicate of [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) — Thom A, Jul 16 '19 at 14:08
@Larnu no in fact this is the problem in my deleted question i found the problem now i'm workin on finding the solution — jino, Jul 16 '19 at 14:08
But your code is still injecting values, @jino . This is exactly why you were having a problem before, and is why you're having a problem now. — Thom A, Jul 16 '19 at 14:09
Youu should be using `Parameters.Add`, as I suggested before. — Thom A, Jul 16 '19 at 14:16

score 2 · Accepted Answer · answered Jul 16 '19 at 14:19

You desperately need to learn how to parameterize your queries. You have several other issues going on here to. Here is a shortened version of how this query should look. Of course I would prefer to get the query out of my code entirely with a stored procedure.

cmd.CommandText = "insert into Employeur (ID, Nom) values(@txt_ID, @txt_Nom)";
cmd.Parameters.Add("@txt_ID", SqlDbType.VarChar, 30).Value = this.txt_ID.Text;
cmd.Parameters.Add("@txt_Nom", SqlDbType.VarChar, 30).value = this.txt_Nom.Text;

You would need to set the appropriate datatypes and sizes to your tables.

Also, look into the USING statement. And never just reuse a connection.

score 1 · Answer 2 · answered Jul 16 '19 at 14:18

To expand on Sean's comment, the least you want is something like this:

    cnx.Open();
    using(SqlCommand cmd = cnx.CreateCommand()) {
        cmd.CommandType = CommandType.Text;
        // I've cut this down a bit to save my typing fingers - you need all your cols and values
        cmd.CommandText = "insert into Employeur (Salaire) values(@Salaire)";
        cmd.Parameters.Add(new SqlParameter("@Salaire", decimal.Parse(txtSalaire.Text));
        cmd.ExecuteNonQuery();
    }
    cnx.Close();

You should also have a using around you cnx creation, but you haven't shown it above.

Trouble inserting number with commas into database

2 Answers2