How to make an UPDATE on a BD SQL Server from C Sharp

Question

How to make an UPDATE on a BD SQL Server from C Sharp

Navigation

#1 by (4 votes)
#2 by (0 votes)

0

I have the following code which makes the query.

  private void EditarUsuario()
    {

        miconexion.Open();
        string sql = @"UPDATE USUARIOS SET
                            [USUARIO] = @USUARIO, [CLAVE] = @CLAVE";

        SqlCommand command = new SqlCommand(sql, miconexion);



        command.Parameters.AddWithValue("USUARIO", txtusuario.Text);
        command.Parameters.AddWithValue("CLAVE", txtContra.Text);
        command.ExecuteNonQuery();
        MessageBox.Show("Datos Actualizados Satisfactoriamente", "Sistema", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        miconexion.Close();
    }

And with this other I show the user's data.

 //BUSQUEDA POR DNI
            if (cbobusqueda.Text == "USUARIO")
            {
                SqlConnection miconexion = new SqlConnection(Conexion.conexion);
                miconexion.Open();
                SqlCommand cmd = new SqlCommand("select * from USUARIOS where USUARIO= @Clave", miconexion);
                cmd.Parameters.AddWithValue("@Clave", txtbusqueda1.Text);
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                //Representa un set de comandos que es utilizado para llenar un DataSet
                SqlDataAdapter dp = new SqlDataAdapter(cmd);
                //Representa un caché (un espacio) en memoria de los datos.
                DataSet ds = new DataSet("USUARIOS");

                //Llenamosel DataSet con la tabla. USUARIOS es nombre de la tabla
                dp.Fill(ds, "USUARIOS");

                //Si dni existe ejecutara la consulta
                if (ds.Tables["USUARIOS"].Rows.Count > 0)
                {
                    //Inicializamos una fila de datos en la cual se almacenaran todos los datos de la fila seleccionada
                    DataRow myRow = ds.Tables["USUARIOS"].Rows[0];

                    txtusuario.Text = myRow["USUARIO"].ToString();
                    txtContra.Text = myRow["CLAVE"].ToString();



                    txtbusqueda1.Enabled = false;
                    cbobusqueda.Enabled = false;
                    txtusuario.Enabled = true;
                    txtusuario.Focus();
                    txtContra.Enabled = true;

                    btnVerDatos.Enabled = true;

                }
                //Si dni no existe mandara mensajillo
                else
                {
                    MessageBox.Show("El usuario ingresado NO EXISTE - Digite un usuario Valido", "Sistema", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    txtbusqueda1.Enabled = true;
                    cbobusqueda.Enabled = true;
                    txtusuario.Enabled = false;
                    txtbusqueda1.Focus();
                    txtbusqueda1.Clear();
                    txtContra.Enabled = false; 
                    btnVerDatos.Enabled = false;

                }

            }

The problem is that, when I give it to modify data, if it modifies it but it modifies all the users. Ejeplo:
I have 2 users, user1 and user2, when I modify user1 when wanting to change my name or password, the result is like this, user1, user1.
I do not understand why I modified all the records instead of just modifying the selection record.
I hope you can help me, thanks in advance

sql c# sql-server

asked by Ryuzaki Lpz 08.03.2017 в 06:05

source

2 answers

0

I can recommend that you not add the query directly to your code for 2 reasons:

Security : If someone comes to see or get your code and want to hurt your system, you will have access to information database as the name of the tables and fields

.

Maintenance : If in the future you have to make changes only to SQL queries, you will have to edit, recompile and release your code again.

It is recommended to use stored procedures and leave the security to the database and if you have to make a change to the consultation, will need only enter the database modify the query in your stored procedure without the need to recompile.

answered by 08.03.2017 в 07:29

Table nested in html Using List as data source Datagridview ordered

score 4 · Accepted Answer

Your SQL query needs the WHERE clause to limit it like this:

string sql = @"UPDATE USUARIOS SET " +
             @"[USUARIO] = @USUARIO, [CLAVE] = @CLAVE " + 
             @" WHERE [USUARIO] = @USUARIO";

because the way you have it about you write the values of User and Password.