Updating datetime in access table

Question

I'm trying to insert a datetime value into a datatable and then use the oledbdataadapter's update(datatable) method to load it into my database.. but i keep getting a "Data type mismatch in criteria expression." error. My access Data types in the table are:

ID Number
Nombre_Proyecto Text
Codigo_Ine_Proy Text
Cliente text
Fecha_Creacion Datetime (short date)

according to access short date is mm/dd/yyy, wich fits with my datetime/toshortdatestring method? i think so at least.

Any help would be appreciated. Here's my code:

Insert OledbCommand fot the data adapter:

  sql = "PARAMETERS [@Fecha_Creacion] datetime;INSERT Into [Proyectos] ([ID], [Nombre_Proyecto],[Codigo_Ine_Proy],[Cliente],[Fecha_Creacion]) Values (@ID,@Nombre_Proyecto,@Codigo_Ine_Proy,@Cliente,@Fecha_Creacion)";
  Comando = new OleDbCommand(sql, conn);
  Comando.Parameters.Add("@Nombre_Proyecto", OleDbType.VarWChar, 500, "Nombre_Proyecto");
  Comando.Parameters.Add("@Codigo_Ine_Proy", OleDbType.VarWChar, 500, "Codigo_Ine_Proy");
  Comando.Parameters.Add("@Cliente", OleDbType.VarWChar, 500, "Cliente");
  Comando.Parameters.Add("@Fecha_Creacion", DbType.DateTime);
  Comando.Parameters.Add("@ID", OleDbType.Integer, 10000, "ID");

Part where i create the datarow on my datatable:

  DataRow newRow = Tabla_Proyectos_BD_General.NewRow();
  Max_IDs["Proyectos"] += 1;
  newRow["ID"] = Max_IDs["Proyectos"];
  newRow["Nombre_Proyecto"] = textBox2.Text;
  newRow["Codigo_Ine_Proy"] = textBox1.Text;
  newRow["Cliente"] = textBox3.Text;
  string x = System.DateTime.Now.ToShortDateString();
  newRow["Fecha_Creacion"] = x;
  Tabla_Proyectos_BD_General.Rows.Add(newRow);

Tony Hopkinson · Answer 1 · 2012-02-02T16:03:05.180

0

string x = System.DateTime.Now.ToShortDateString();

It's a string, not a datetime! hence mismatch.

newRow["Fecha_Creacion"] = System.DateTime.Now;

And your parameterised query should just do it for you.

if you want to show the date you put in there in shortdatestring format (whate ever that is on the pc that does the formatting, get it as a datetime and then format as required.

PS if you want to pass a date as a string to a database, use the formats yyyy-MM-dd or yyyyMMdd. Any other than the universal and unambiguous date formats is just a bug waiting to happen, and never do unless you have to.

Tip when outputting dates, converting them into strings in some format is the last operation, when inputing them, converting to a datetime from the string is the first thing you should do.

Edited after comment Simplest solution is

Comando.Parameters.Add("@Fecha_Creacion", DbType.DateTime, System.DateTime.Now);

edited Feb 02 '12 at 16:03

answered Feb 01 '12 at 00:06

Tony Hopkinson

20,172
3
31
39

I get this exception when i do that, but i dont know why!!Wrong data type for parameter '@Fecha_Creacion'. – user1181589 Feb 01 '12 at 00:44
i've tried changing the dbtype.datetime in the parameters but no success – user1181589 Feb 01 '12 at 21:51
Edited after puzzling comment from op – Tony Hopkinson Feb 02 '12 at 16:03

score 0 · Answer 2 · answered Feb 01 '12 at 00:09

0

You should just use

newRow["Fecha_Creacion"] = System.DateTime.Now;

What you see from in the Access is the "formatted date". When interacting thru OleDB you need to use the DateTime and not the formatted string.

answered Feb 01 '12 at 00:09

amit_g

30,880
8
61
118

Updating datetime in access table

2 Answers2