Error converting datatype varchar to numeric in update method

Question

I'm trying to create an update method. The code is fine but it gives me an error when I run the project:

Erreur de conversion du type de données varchar en numeric. (Error converting data type varchar to numeric)

My code is :

public ActionResult Edit(string numQuittance)
    {
        var connetionString = ConfigurationManager.ConnectionStrings["connections"].ToString();
        QUITTANCIER quittancier = new QUITTANCIER();
        using (SqlConnection connection = new SqlConnection(connetionString))
        {
            string sql = $"Select numero_quittance,numero_police,date_mvt_du,date_mvt_au,prime_gbase,prime_gannexe,frais,taxes,commission_paye From quittancier Where numero_quittance ='{numQuittance}'";
            SqlCommand command = new SqlCommand(sql, connection);
            connection.Open(); using (SqlDataReader dataReader = command.ExecuteReader())
            {
                while (dataReader.Read())
                {
                    quittancier.NUMERO_QUITTANCE = Convert.ToString(dataReader["NUMERO_QUITTANCE"]);
                    quittancier.NUMERO_POLICE = Convert.ToString(dataReader["NUMERO_POLICE"]);
                    quittancier.DATE_MVT_DU = Convert.ToDateTime(dataReader["DATE_MVT_DU"]);
                    quittancier.DATE_MVT_AU = Convert.ToDateTime(dataReader["DATE_MVT_AU"]);
                    quittancier.PRIME_GBASE = Convert.ToInt32(dataReader["PRIME_GBASE"]);
                    quittancier.PRIME_GANNEXE = Convert.ToInt32(dataReader["PRIME_GANNEXE"]);
                    quittancier.FRAIS = Convert.ToInt32(dataReader["FRAIS"]);
                    quittancier.TAXES = Convert.ToInt32(dataReader["TAXES"]);
                    quittancier.COMMISSION_PAYE = Convert.ToInt32(dataReader["COMMISSION_PAYE"]);

                }
            }
            connection.Close();
        }
        return View(quittancier);


    }
    [HttpPost]
    [ActionName("Edit")]
    public ActionResult Edit(QUITTANCIER quittancier)
    {
        var connetionString = ConfigurationManager.ConnectionStrings["connections"].ToString();
        using (SqlConnection connection = new SqlConnection(connetionString))
        {
            string sql = "Update quittancier set numero_quittance='" + quittancier.NUMERO_QUITTANCE + "',numero_police='" + quittancier.NUMERO_POLICE + "',date_mvt_du='" + quittancier.DATE_MVT_DU + "',date_mvt_au='" + quittancier.DATE_MVT_AU + "',prime_gbase='" + quittancier.PRIME_GBASE + "',prime_gannexe='" + quittancier.PRIME_GANNEXE + "',frais='" + quittancier.FRAIS + "',commission_paye='" + quittancier.COMMISSION_PAYE + "'";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
        }
        return RedirectToAction("detail");
    }
}

Where in the code is this error located? I suspect it will be on one of your quittancier. parameters that should be a different datatype. — emmademontford, Nov 18 '19 at 09:35
Can you clarify: Are you getting a compile-time error or a run-time error? (That error message seems to be a run-time error.) — Matthew Watson, Nov 18 '19 at 09:36
it's a run time error . the error in this ligne : command.ExecuteNonQuery(); — Oussama Hsini, Nov 18 '19 at 09:36
I'd say your error is here: `Where numero_quittance ='{numQuittance}'` You put your numer_quittance value in quotes, telling your db to treat it as a varchar. Leaving away the single quotes alone should do it. BUT: your queries are wide open for SQL injection. You should use parametrized queries instead. — LocEngineer, Nov 18 '19 at 09:50
Did you check what values you have for properties of `quittancier` object? — Chetan, Nov 18 '19 at 09:52
all is fine i didn't understand the error came from ? and i deleted the quotes from the parametrs and the error persists — Oussama Hsini, Nov 18 '19 at 09:57
When the exception is thrown, go to the `Immediate Window` and type in `?sql`. Please share the **exact** value. — mjwills, Nov 18 '19 at 10:26

Lajos Arpad · Answer 1 · 2019-11-18T10:26:11.733

0

The error message tells you that a numeric value cannot be converted into varchar. Since you have no explicit conversion being executed, your problem is with an implicit conversion. The database attempts to store a numeric field, but you pass a textual value to it. Note, that

'123'

is textual, while

123

is numeric. If you attempt to store a numeric value, you don't need quotes around it and you need to check whether it's numeric indeed.

P.S. Watch out for SQL injection, your project can easily be hacked by people with bad intention.

EDIT

Even though your code is extremely vulnerable, I will describe here how it will "work":

Your select might "work" if you omit the apostrophes around numQuittance

string sql = $"Select numero_quittance,numero_police,date_mvt_du,date_mvt_au,prime_gbase,prime_gannexe,frais,taxes,commission_paye From quittancier Where numero_quittance ={numQuittance}";
Your update might "work" if you omit the apostrophes around numeric fields:

string sql = "Update quittancier set numero_quittance='" + quittancier.NUMERO_QUITTANCE + "',numero_police='" + quittancier.NUMERO_POLICE + "',date_mvt_du='" + quittancier.DATE_MVT_DU + "',date_mvt_au='" + quittancier.DATE_MVT_AU + "',prime_gbase=" + quittancier.PRIME_GBASE + ",prime_gannexe=" + quittancier.PRIME_GANNEXE + ",frais=" + quittancier.FRAIS + ",commission_paye='" + quittancier.COMMISSION_PAYE + "'";

edited Nov 18 '19 at 10:26

answered Nov 18 '19 at 09:59

Lajos Arpad

64,414
37
100
175

prime_gbase , prime_gannexe,frais,taxes are numeric . how to write the sql query now – Oussama Hsini Nov 18 '19 at 10:07
Thank you Lajos Arpad . i did what you told me . But there is another error ( incorrect syntax to '00' ) and in the same line command.ExecuteNonQuery(); i know that my code is vulnerable i just in a learning period in a new company as a junior c# developper .. it's an excercice – Oussama Hsini Nov 18 '19 at 11:53
1

@OussamaHsini If you do work the wrong way, you'll continue to experience this error. Do it the right way - https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection . – mjwills Nov 18 '19 at 20:14
@OussamaHsini the mistake you consistently make is that you mix up textual data with numeric. Your database enforces the types and you need to be careful about them. I know that you are stressed, maybe even in a panic, but you will need to watch some tutorials. Without some minimal level of solid knowledge you will have no fun while working, hence you need to acquire some knowledge. You might want to learn about methodologies of learning, like this one: https://www.endpoint.com/blog/2018/10/30/how-i-learn-new-technologies – Lajos Arpad Nov 19 '19 at 10:04

Error converting datatype varchar to numeric in update method

1 Answers1