CODEWITHSUNDEEP
c#sqlms-access
Renzo Gallo
Renzo Gallo

Reputation: 1

"Data type mismatch in criteria expression." ACCESS 2010 and C#

I'm trying to make a query in Access 2010, but it keeps giving me this error "Data type mismatch in criteria expression." over the line "dataCommand.ExecuteNonQuery();"

 String sql1 = "INSERT INTO Formulario (Numero, Localidad, [Parroquia o Institucion], Fecha, [Id Institucion], Comunitaria, Observacion) VALUES (@Numero, @Localidad, [@Parroquia o Institucion], @Fecha, [@Id Institucion], @Comunitaria, @Observacion)";
 mi_conexion.Open();
 //insertar tabla formulario...........
 dataCommand.CommandText = sql1;
 dataCommand.Parameters.Add("@Numero", OleDbType.VarChar).Value = numero;
 dataCommand.Parameters.Add("@Localidad", OleDbType.VarChar).Value = textBox_localidad.Text;
 dataCommand.Parameters.Add("@Parroquia o Institucion", OleDbType.VarChar).Value = comboBox_instituciones.Text;
 dataCommand.Parameters.AddWithValue("@Fecha", "#" + fecha.Value.Date.Day.ToString() + "/" + fecha.Value.Date.Month.ToString() + "/" + fecha.Value.Date.Year.ToString() + "#");
 dataCommand.Parameters.Add("@Id Institucion", OleDbType.VarChar).Value = Int32.Parse(label_id2.Text);
 dataCommand.Parameters.Add("@Observacion", OleDbType.VarChar).Value = obs;
 if(radioButton_comunitaria.Checked)
 {
    dataCommand.Parameters.Add("@Comunitaria", OleDbType.VarChar).Value = true;
 }
 else
 {
    dataCommand.Parameters.Add("@Comunitaria", OleDbType.VarChar).Value = false;
 }
 dataCommand.ExecuteNonQuery();

I dont know what could be the reason.....

I tried this:

String sql1 = "INSERT INTO Formulario (Numero, Localidad, ParroquiaoInstitucion, Fecha, IdInstitucion, Comunitaria, Observacion) VALUES (?,?,?,?,?,?,?)";
mi_conexion.Open();
//insertar tabla formulario...........
dataCommand.CommandText = sql1;
dataCommand.Parameters.AddWithValue("Numero", numero);
dataCommand.Parameters.AddWithValue("Localidad", textBox_localidad.Text);
dataCommand.Parameters.AddWithValue("ParroquiaoInstitucion", comboBox_instituciones.Text);
dataCommand.Parameters.AddWithValue("Fecha", "#" + fecha.Value.Date.Day.ToString() + "/" + fecha.Value.Date.Month.ToString() + "/" + fecha.Value.Date.Year.ToString() + "#");
dataCommand.Parameters.AddWithValue("IdInstitucion", Int32.Parse(label_id2.Text));
if(radioButton_comunitaria.Checked)
{
    dataCommand.Parameters.AddWithValue("Comunitaria", true);
}
else
{
    dataCommand.Parameters.AddWithValue("Comunitaria", false);
}
dataCommand.Parameters.AddWithValue("Observacion", obs);
dataCommand.ExecuteNonQuery();

But it doesn't work yet -_-.....

Upvotes: 0

Views: 946

Answers (3)

Steve
Steve

Reputation: 216343

OleDb wants that you add your parameters in the same order in which their placeholder appears in the command text. OleDb using Ms-Access accepts also parameters with name that starts with @ without problem (probably to ease the upsizing to Sql Server), but you should absolutely respect the order.

In your code you add the parameter for the field Comunitaria after the parameter for the field Observacion This creates the mismatch problem because OleDb passes the value for Comunitaria to the field Observacion and viceversa. The field Comunitaria then receives a value that cannot be converted to a boolean (Yes/No) value.

Try to change your code in this way 

if(radioButton_comunitaria.Checked)
{
    dataCommand.Parameters.Add("@Comunitaria", OleDbType.VarChar).Value = true;
}
else
{
    dataCommand.Parameters.Add("@Comunitaria", OleDbType.VarChar).Value = false;
}
dataCommand.Parameters.Add("@Observacion", OleDbType.VarChar).Value = obs;
dataCommand.ExecuteNonQuery();

A part from this you should remove that spaces inside the parameters names. It could only give you troubles and it is not needed.

I suggest also to use a correct datatype for the Fecha field. If Access expects a Date don't try to build a replacement string like you are doing the insert inside the Access interface.

dataCommand.Parameters.AddWithValue("@Fecha", new DateTime(fecha.Value.Date.Year,
                                                          fecha.Value.Date.Month,
                                                          fecha.Value.Date.Day)

Upvotes: 0

Maciej Los
Maciej Los

Reputation: 8591

Access database (even when OleDb is in action) does not know parameters which names start with @.

To be able to use parameters with MS Access database engine, use:

PARAMETERS [parameter1] type, [parameter2] type, [parameterN] type;
SELECT <FieldList>
FROM <TableName>
WHERE FieldName = [parameter1] AND ...

For further information, please see: PARAMETERS Declaration (Microsoft Access SQL)

Second way is to use non-named parameters:

SELECT <FieldList>
FROM <TableName>
WHERE FieldName = ? AND FieldName2 =?

But i prefer to use named parameters.

Upvotes: 1

mybirthname
mybirthname

Reputation: 18127

Use AddWithValue instead of Add method. Sql Server will decide what type is the data for you ! I'm giving you an example. Also OLE DB does not support named parameters, only unnamed parameter 

dataCommand.Parameters.AddWithValue("?", true);

You should set the parameters in the right order. Here is the source how to add parameters for OleDb in MSDN

Upvotes: 2

Related Questions