Error Inserting Cast data and null fields C#

Question

I'm working on Visual Studio 2012 using an MS Access file as Database and having a bunch of trouble with this insert:

cmd.Parameters.Add(new OleDbParameter("@codigo", cal.CodEtiq)); // Value = int
cmd.Parameters.Add(new OleDbParameter("@data", cal.Data.ToString("yyyy-MM-dd hh:mm"))); //Value = 2013-10-29 00:00
cmd.Parameters.Add(new OleDbParameter("@entidade", cal.EntidadeCal)); // Value = string
cmd.Parameters.Add(new OleDbParameter("@observacao", cal.Observacao)); // Value = string
cmd.Parameters.Add(new OleDbParameter("@certificado", cal.Certificado)); // Value = string
cmd.Parameters.Add(new OleDbParameter("@resultado", cal.Resultado)); // Value = string
cmd.Parameters.Add(new OleDbParameter("@selecionar", cal.Selecionar));// Value = int
cmd.Parameters.Add(new OleDbParameter("@null", DBNull.Value));

cmd.CommandText = "INSERT INTO [Movimento Ferramentas] VALUES (@codigo, CAST(@data AS DATETIME), @entidade, @null, @null, 'Calibração', @null, @observacao, @null, @certificado, @resultado, @selecionar, @null)";

The table is defined like this ( column = fieldtype):

• Codigo = text
• Data saida (aka @data) = date/hour
• Entidade = text
• Data Ent = data/hour
• GT Ent = text
• Estado = text
• GT saida = text
• observacaoes = text
• requisitante = number
• certificado = text
• resultado = text
• selecionar = Yes/No
• tipo int = text

First I was getting an error saying "Data Type Mismatch In Criteria Expression" then I looked up the Cast for the date and put it like that and now it's giving me an error on it saying something like "Syntax error (operator missing) in the expression of the query CAST(@data AS DATETIME)"

Please help.

UPDATE: As suggested by @Ralph, I actually just had to put all nulls as nulls instead of going with the "@null", DBNull.Value. So, Problem Solved Thanks everyone.

Versify

I'd say that the problem is in the 2nd parameter, don't convert it to a string if the database column is defined as a datetime. Also, you can use one of the other constructors of the OleDbParameter so that you can specify the type of the parameter - check msdn.microsoft.com/en-us/library/… – Christinachristine

Without the .ToString("yyyy-MM-dd hh:mm") and the Cast I still get the "Data Type Mismatch In Criteria Expression" error – Aoudad

Have you tried using one of the other OleDbParameter constructor overloads, as I suggested? You can specify the type of the parameter, that should fix the problem. Check the link I added in my previous comment. – Christinachristine

I checked it out. Issue is the other constructors become too complex. This case as you say it I just need the string, the type and the object itself. Closest to that is "OleDbParameter(String, OleDbType, Int32, ParameterDirection, Boolean, Byte, Byte, String, DataRowVersion, Object)". – Aoudad

The Parameter Collection has a simple Add(name, type) overload. But OleDB doesn't know anything about named parameter. You need to ad them in the order they are defined in the sql string. And you can't reuse a parameter either. you have to add @@null as often as its in the sql. Why don't you just write null instead of @@null there? – Piefer

@Piefer Because this column "Data Ent = data/hour" being a datetime type won't accept just null. – Aoudad

@MicaelFlorêncio Then it won't accept DBNull.Value routed through a parameter either. – Piefer

@Piefer Ok forget it, it sorta worked thanks. Now the issue is, there's nothing on the right column... and I checked the "cal." objects, those are right. – Aoudad

Answer 1

see this...

enter code here

if date is null assign null for datetime variable using dbnull.value

 if (@data== null)
 {
    new OleDbParameter("@Dt", SqlDbType.DateTime).Value =DBNull.Value;
 }
else
{
    new OleDbParameter(("@Dt", SqlDbType.DateTime).Value = @data;
}

Beltane