CODEWITHSUNDEEP
c#ms-accessdatetimeoledbcommandoledbparameter
user1181589
user1181589

Reputation: 153

Updating datetime in access table

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);

Upvotes: 0

Views: 1563

Answers (2)

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

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);

Upvotes: 0

amit_g
amit_g

Reputation: 31270

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.

Upvotes: 0

Related Questions