Insert data in a date field of a access 2013 database from C #

0

I have an Access 2013 database with several tables, and in some of them fields of type "Short Date". The issue is that when trying to enter data using a SQL INSERT statement from C #, I get the following error: "The data types do not match in the criteria expression."

I tried another table that has no date fields and I had no problem doing the INSERT, so I came to the conclusion that the problem is the date field.

The value of the date I'm taking from a DateTimePicker called dtpFechaNac.

How should I format these fields so Access 2013 will take them correctly?

Formats that I tried:

- dtpFechaNac.Value
- dtpFechaNac.Value.ToString("dd/MM/yyyy")
- dtpFechaNac.Value.ToShortDateString();

CODE (most relevant):

string cmd = string.Format("INSERT INTO DatosPersonales " +
                     "([Dni], [NombreApe], [Nacionalidad], [FechaNac], " +
                     "[Edad], [EstadoCiv], [Hijos], [Direccion], " +
                     "[Localidad], [CodigoPos], [Telefono], [Celular], " +
                     "[Educacion], [Email]) " +
                     "VALUES (@Param1, @Param2, @Param3, @Param4, @Param5, @Param6, " +
                     "@Param7, @Param8, @Param9, @Param10, @Param11, @Param12, " +
                     "@Param13, @Param14)");

        List<object> parametros = new List<object>();
        parametros.Add(Convert.ToInt32(txtDni.Text));
        parametros.Add(txtNombreApe.Text);
        parametros.Add(txtNacionalidad.Text);
        parametros.Add(dtpFechaNac.Value.ToString("dd/MM/yyyy")); //Aca esta el problema
        parametros.Add(txtEdad.Text);
        parametros.Add(cboEstadoCiv.SelectedItem.ToString());
        parametros.Add(txtHijos.Text);
        parametros.Add(txtDireccion.Text);
        parametros.Add(txtLocalidad.Text);
        parametros.Add(txtCodigoPos.Text);
        parametros.Add(txtTelefono.Text);
        parametros.Add(txtCelular.Text);
        parametros.Add(txtEducacion.Text);
        parametros.Add(txtEmail.Text);

OleDbCommand command = new OleDbCommand(cmd, CrearConexionDB());

if(parametros != null)
        {
            for (int i = 0; i < parametros.Count(); i++)
            {
                command.Parameters.AddWithValue("Param" + i.ToString(), parametros[i]);
            }
        }

        command.Connection.Open();
        return command.ExecuteNonQuery();

SOLVED:

You do not have to convert to a string as the friend gbianchi said, with taking the value directly from the DateTimePicker (in my case) the conversion is done automatically. The problem I had was that in the database I had another field defined as numeric that I missed (my mistake).

    
asked by Willy616 25.04.2017 в 19:44
source

1 answer

2

The problem you are having is that you are transforming the date to the string before passing it as a parameter, then the query is poorly constructed at the time of passing it to access.

Access works the dates between # , so if you pass a date object instead of string, access would transform it accordingly. As you do not, Access treats it as a string and the conversion fails.

Moreover, I would recommend removing the load of the parameters to a list (which is of objects, but you only pass strings) and pass them directly to the parameters object of the command.

    
answered by 25.04.2017 / 20:12
source