Error updating records of type Varchar to INT using form with comboBox

Question

Error updating records of type Varchar to INT using form with comboBox

Navigation

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

0

I have a problem updating the fields in my table. Within the form I have a comboBox where I show the fields of my table of type Varchar but when selecting one of them I save the id of that department. My problem is generated when updating, showing the following error:

"ErroSystem.SqlClient.SqlException (0x80131904): Conversion failed when converting the varchar value 'Purchases' to data type int".

To modify my record, first search using a SEARCH button where you read the person's control number. With the following method.

public void buscarNumeroControl()
    {
        // string cod = textBox1.Text;
        try
        {
            string cadena = "SELECT nombre, apellidoP, depto FROM Personal INNER JOIN  Departamento ON Personal.id_Depto=Departamento.id_Depto WHERE id_numControl='" + textBox1.Text + "' ";
            SqlCommand cmd = new SqlCommand(cadena, con);
            con.Open();
            SqlDataReader registro = cmd.ExecuteReader();
            if (registro.Read() == true)
            {
                textBox2.Text = registro["nombre"].ToString();
                textBox3.Text = registro["apellidoP"].ToString();
                comboDepto.Text = registro["depto"].ToString();
                actualizar.Enabled = true;
            }
            else
            {
                MessageBox.Show("No existe un artículo con el código ingresado");

            }
            con.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error" + ex.ToString());
        }

    }

The comboBox filled it in the following way.

public void llenarcomboboxDepto()
    {
        try{
        con.Open();
        DataSet dsd = new DataSet();

        SqlDataAdapter cmd = new SqlDataAdapter("SELECT id_Depto, depto FROM Departamento", con);
        //se indica con que tabla se llena

        cmd.Fill(dsd, "Departamento");

        comboDepto.DataSource = dsd.Tables[0].DefaultView;

        //indicamos el valor de los miembros

        comboDepto.ValueMember = "id_Depto";

        //se indica el valor a desplegar en el combobox

        comboDepto.DisplayMember = "depto";
        con.Close();
         }
        catch (Exception ex)
        {
            MessageBox.Show("Error" + ex.ToString());
        } 
    }

and to modify I have the UPDATE button invoking the following method.

public void modificarRegistro()
    {
        try
        {
            con.Open();
            string cadena = "UPDATE Personal set nombre='" + textBox2.Text + "', apellidoP='" + textBox3.Text + "' , id_Depto='" + comboDepto.Text + "'  WHERE id_numControl='" + textBox1.Text + "'";
            SqlCommand comando = new SqlCommand(cadena, con);
            int cant;
            cant = comando.ExecuteNonQuery();
            if (cant == 1)
            {
                MessageBox.Show("Se modificaron los datos correctamente");
                textBox1.Text = "";
                textBox2.Text = "";
                textBox3.Text = "";
                comboDepto.Text = "";
                con.Close();
            }
            else
            {
                MessageBox.Show("No existe la persona con ese numero de control");

                actualizar.Enabled = false;
            }
            // }

        }
        catch (Exception ex)
        {
            MessageBox.Show("Error" + ex.ToString());
        }
    }

I do not know where this is wrong, I feel like I'm not recovering the id of the department I'm selected for. Could someone please help me?

c# sql-server-2008-r2

asked by Daniel M. 16.05.2018 в 21:27

source

2 answers

Line break in excel query about the movement constructor

score 0 · Answer 1

The Det_id is integer and you are storing a string that retrieves the SelectedValue property and not the Text.

string cadena = "UPDATE Personal set nombre='" + textBox2.Text + "', apellidoP='" + textBox3.Text + "' , id_Depto='" + comboDepto.SelectedValue + "'  WHERE id_numControl='" + textBox1.Text + "'";

score 0 · Answer 2

I think the correct sentence should be:

string cadena = "UPDATE Personal set nombre='" + textBox2.Text + "', apellidoP='" + textBox3.Text + "' , id_Depto=" + comboDepto.SelectedValue.ToString() + " WHERE id_numControl=" + textBox1.Text;

Special care should be taken with single quotes.

EDIT : Would remain this way using parameters ...

public void modificarRegistro()
{
    int cant=0;
    string cadena = "UPDATE Personal SET nombre=@nombre, apellidoP=@apellido , id_Depto=@iddepto WHERE id_numControl=@id";

    using (var comando = new SqlCommand(cadena, con))
    {
        //Recomendado para tipos de datos especializados como latitud, longitud, decimales, money, etc.
        command.Parameters.Add("@nombre", SqlDbType.NVarChar); //Debe ser el mismo tipo de datos definido en tu SQL SERVER
        command.Parameters["@nombre"].Value = textBox2.Text;

        //Genial para tipos de datos comunes
        command.Parameters.AddWithValue("@apellido", textBox3.Text);
        command.Parameters.AddWithValue("@iddepto", comboDepto.SelectedValue);
        command.Parameters.AddWithValue("@id", textBox1.Text);

        try
        {
            con.Open();
            cant = comando.ExecuteNonQuery();
            if (cant == 1)
            {
                textBox1.Text = string.Empty;
                textBox2.Text = string.Empty;
                textBox3.Text = string.Empty;
                comboDepto.SelectedIndex = -1; //O puedes colocar 0 (cero)
                MessageBox.Show("Se modificaron los datos correctamente");
            }
            else
            {
                actualizar.Enabled = false;

                MessageBox.Show("No existe la persona con ese numero de control");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error" + ex.ToString());
        }
        finally
        {
            //Se cierra la conexión haya o no error.
            con.Close();
        }
    }
}