validate existence before inserting

Question

validate existence before inserting

Navigation

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

1

Hello good afternoon everyone, I previously asked this same question but I was recommended to open a new thread, I have to validate that before making an insert check if that record exists. If it exists, show me an alert message and ask me if I want to call the edition of it, if it does not exist, that allows me to move to the insertion function.

For this, I have a procedure stored in MYSQL that through a COUNT tells me if X id exists X times in the database.

Now, in c #, I have a save button with the following code:

    private void btnGuardar_Click(object sender, EventArgs e)
    {


        DateTime fd = Convert.ToDateTime(dtpFechaNac.Value);
        String fnacimiento = fd.ToString("yyyy/MM/dd");

        //label15.Text = fnacimiento;

        //VALIDAR EXISTENCIA DE RUT


        try
        {
            string respuesta = "";

            //validamos los campos obligatorios
            if (this.txtDocumento.Text == string.Empty || this.txtNombres.Text == string.Empty ||
                this.txtApePat.Text == string.Empty || this.txtDireccion.Text == string.Empty ||
                this.txtFono.Text == string.Empty)
            {
                mensajeError("Faltan datos por ingresar, favor verificar");
                error.SetError(txtDocumento, "Debe Ingresar Numero de Documento");
                error.SetError(txtApePat, "Debe Ingresar Numero de Documento");
                error.SetError(txtDireccion, "Debe Ingresar Numero de Documento");
                error.SetError(txtFono, "Debe Ingresar Numero de Documento");
                error.SetError(txtNombres, "Debe Ingresar Numero de Documento");
            }
            else
            {
                if (this.isNuevo)
                {
                   respuesta = negociosFuncionario.insertar(
                   this.txtDocumento.Text,
                   this.txtNombres.Text,
                   this.txtApePat.Text,
                   this.txtApeMat.Text,
                   this.dtpFechaNac.Value.ToString("yyyy-MM-dd H:mm:ss"),
                   this.cmbSexo.Text,
                   this.txtDireccion.Text,
                   Convert.ToInt32(this.txtFono.Text),
                   this.txtCorreo.Text,
                   this.cmbPrevSalud.Text,
                   this.cmbPrevSocial.Text,
                   this.txtComentarios.Text
                  );
                }
                else
                {
                   respuesta = negociosFuncionario.editar(
                   Convert.ToInt32(this.txtCodFuncionario.Text),
                   this.txtDocumento.Text,
                   this.txtNombres.Text,
                   this.txtApePat.Text,
                   this.txtApeMat.Text,
                   this.dtpFechaNac.Value.ToString("yyyy-MM-dd H:mm:ss"),
                   this.cmbSexo.Text,
                   this.txtDireccion.Text,
                   Convert.ToInt32(this.txtFono.Text),
                   this.txtCorreo.Text,
                   this.cmbPrevSalud.Text,
                   this.cmbPrevSocial.Text,
                   this.txtComentarios.Text
                  );

                }

                if (respuesta.Equals("OK"))
                {
                    if (this.isNuevo)
                    {
                        this.mensajeOK("REGISTRO INSERTADO CON EXITO EN LA BASE DE DATOS");
                    }
                    else
                    {
                        this.mensajeOK("REGISTRO ACTUALIZADO CON EXITO");
                    }
                }
                else
                {
                    this.mensajeError(respuesta);
                }

                this.isNuevo = false;
                this.isEditar = false;
                this.botones();
                this.limpiarControles();
                this.cargarGrilla();
            }

        }
        catch (Exception ex)
        {

            MessageBox.Show(ex.Message + ex.StackTrace);
        }

    }

and in my data layer, I have the method to validate in the following way:

        public int validarExistencia(string rut)
    {
        MySqlConnection con = new MySqlConnection();
        MySqlCommand cmd = new MySqlCommand();
        con.ConnectionString = conexion.cadenaConexion;
        MySqlDataAdapter adaptador = new MySqlDataAdapter(cmd);
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "spValidaExistFuncionario";

        //ID AUTOINCREMENTAL
        MySqlParameter parIdFunc = new MySqlParameter();
        parIdFunc.ParameterName = "@rut1";
        parIdFunc.MySqlDbType = MySqlDbType.VarChar;
        parIdFunc.Size = 25;
        parIdFunc.Value = rut;
        cmd.Parameters.Add(parIdFunc);

        //EJECUCION DEL COMANDO
        int respuesta = Convert.ToInt32(cmd.ExecuteScalar());

        return respuesta ;
    }

What I do not understand well, is how and where I should call the validation function.

Greetings to all, thank you already

mysql c#

asked by Nicolas Ezequiel Almonacid 03.08.2018 в 19:21

source

2 answers

1

Only change if (this.isNuevo) by if (clase.validarExistencia(id_validar) == 0)

answered by 03.08.2018 в 19:31

Allow special characters in .htaccess How to make the text appear in a new line in a table

score 0 · Accepted Answer

In other technologies you can create a unique constraint, you can define one or several columns in it and when trying to insert an existing record you will mark the error.

I think this link can help you. link

What is a unique constraint (Unique constraint) in MySQL? A Unique restriction is a single field or combination of fields that defines a record in a unique way. Some of the fields may contain null values as long as the combination of values is unique.

Create Unique Restriction - Use a CREATE TABLE statement

The syntax for creating a unique restriction using a CREATE TABLE command in MySQL is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);