Update and insert in one button with Command Parametrs C#

Question

I want to do update and insert in one button.

i want it to check if there's data then it will just update it but if not it will add it.

I don't know what i'm missing in this code

cnx.Open();
        SqlCommand cmd = cnx.CreateCommand();
        SqlDataReader dr;
        cmd.CommandText = "Select * from TPayement where ID='" + txt_ID.Text + "'";
        dr = cmd.ExecuteReader();
        if (dr.HasRows)
        {
            dr.Read();
            cmd.CommandText = "update TPayement (ID, Nom, QUA, Salaire, NombreJour, HeurSupplimentaire, SalaireHeur, Somme) values (@ID, @Nom, @QUA, @Salaire, @NombreJour, @HeurSupplimentaire, @SalaireHeur, @Somme) where ID='" + txt_ID.Text + "'";

            cmd.Parameters.Add("@ID", SqlDbType.Int);
            cmd.Parameters.Add("@Nom", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@QUA", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@Salaire", SqlDbType.Float);
            cmd.Parameters.Add("@NombreJour", SqlDbType.Float);
            cmd.Parameters.Add("@HeurSupplimentaire", SqlDbType.Float);
            cmd.Parameters.Add("@SalaireHeur", SqlDbType.Float);
            cmd.Parameters.Add("@Somme", SqlDbType.Float);

            cmd.Parameters["@ID"].Value = txt_ID.Text;
            cmd.Parameters["@Nom"].Value = txt_Nom.Text;
            cmd.Parameters["@QUA"].Value = txt_QUA.Text;
            cmd.Parameters["@Salaire"].Value = txt_Salaire.Text;
            cmd.Parameters["@NombreJour"].Value = txt_NBRJ.Text;
            cmd.Parameters["@HeurSupplimentaire"].Value = txt_HSUP.Text;
            cmd.Parameters["@SalaireHeur"].Value = txt_SalireHeur.Text;
            cmd.Parameters["@Somme"].Value = txt_Somme.Text;
            cmd.ExecuteNonQuery();
            MessageBox.Show("Se payement est enregistrer");
        }
        else
        {
            cmd.CommandText = "insert into TPayement (ID, Nom, QUA, Salaire, NombreJour, HeurSupplimentaire, SalaireHeur, Somme) values (@ID, @Nom, @QUA, @Salaire, @NombreJour, @HeurSupplimentaire, @SalaireHeur, @Somme)";

            cmd.Parameters.Add("@ID", SqlDbType.Int);
            cmd.Parameters.Add("@Nom", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@QUA", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@Salaire", SqlDbType.Float);
            cmd.Parameters.Add("@NombreJour", SqlDbType.Float);
            cmd.Parameters.Add("@HeurSupplimentaire", SqlDbType.Float);
            cmd.Parameters.Add("@SalaireHeur", SqlDbType.Float);
            cmd.Parameters.Add("@Somme", SqlDbType.Float);

            cmd.Parameters["@ID"].Value = txt_ID.Text;
            cmd.Parameters["@Nom"].Value = txt_Nom.Text;
            cmd.Parameters["@QUA"].Value = txt_QUA.Text;
            cmd.Parameters["@Salaire"].Value = txt_Salaire.Text;
            cmd.Parameters["@NombreJour"].Value = txt_NBRJ.Text;
            cmd.Parameters["@HeurSupplimentaire"].Value = txt_HSUP.Text;
            cmd.Parameters["@SalaireHeur"].Value = txt_SalireHeur.Text;
            cmd.Parameters["@Somme"].Value = txt_Somme.Text;
            dr.Close();
            cmd.ExecuteNonQuery();
            MessageBox.Show("Se payement est enregistrer");

        }
        dr.Close();
        cnx.Close();

This is a perfect place for a stored procedure. Just pass the data to the server and let a stored procedure do an `if exists` for you. — Reinstate Monica Cellio, Jul 17 '19 at 13:42
Why are you not using a parameter in your first query? You demonstrated you know how to use them after that. But your first query renders this wide open to sql injection. And your second is a mismatch of sql injection and parameters. — Sean Lange, Jul 17 '19 at 13:46
Use a merge sql statement - does exactly this with a single statement and a single block of app code. In addition, your update statement is syntactically incorrect - you don't supply a column list using the same format as the insert statement does. Copy/paste is not always your friend! — SMor, Jul 17 '19 at 13:46
@Tab Alleman the error is : Un DataReader associé à cette Command est déjà ouvert. Il doit d'abord être fermé. he say that there's an open datareader need to be closed — jino, Jul 17 '19 at 13:49
your answer is here :https://stackoverflow.com/questions/39249746/update-if-the-name-exists-else-insert-in-sql-server — Pooria, Jul 17 '19 at 13:51
Possible duplicate of [Update if the name exists else insert - in SQL Server](https://stackoverflow.com/questions/39249746/update-if-the-name-exists-else-insert-in-sql-server) — Pooria, Jul 17 '19 at 13:52
And the select query. When you munge strings together and execute that string you are opening the door for [bobby tables](http://bobby-tables.com/) to come visit. — Sean Lange, Jul 17 '19 at 13:56

score 1 · Answer 1 · answered Jul 17 '19 at 13:53

The problem is that you open a DataReader with the Command object cmd.

Then, before you close the DataReader, you try to re-purpose the same cmd object and Execute it.

You have to close the DataReader before you can re-use the cmd object for an insert or update.

Update and insert in one button with Command Parametrs C#

1 Answers1