Problem saving DATE TIME into Database

[RMirenzi]

Hi,

I'm having problems inserting datetime into and access database as shown below

string insert = "insert into " + TableName + " (Name,Location,Date) ";
insert += " values('" + row["Name"] + "','" + row["Location"];
insert += "','#2/2/2004#')";
System.Data.OleDb.OleDbCommand cmd = new
System.Data.OleDb.OleDbCommand(insert,this.Connection);
cmd.ExecuteNonQuery();

or even with this

System.Data.OleDb.OleDbCommand cmd = database.CreateCommand();
cmd.CommandText = "insert into "+ TableName +" (Name,Location,Date)"+" values(?,?,?)";
cmd.Parameters.Add("Name",System.Data.OleDb.OleDbType.VarChar,40,"Name");
cmd.Parameters.Add("Location",System.Data.OleDb.OleDbType.VarChar,40,"Location");
cmd.Parameters.Add("Date",System.Data.OleDb.OleDbType.Date,0,"Date");

in both cases I get "syntax error in insert into statement"
The Date field is defined as "General Date", but I get the same thing with "Short Date"

If I take the parts to do with the date it works

Help Please

[Andy Tacker]

I usually stick to String version of datetime when i insert data and SQL manipulates the string data to date format...

[riscoh]

cmd.CommandText = "insert into "+ TableName +" (Name,Location,Date)"+" values(?,?,?)";
cmd.Parameters.Add("Name",System.Data.OleDb.OleDbType.VarChar,40,"Name");
cmd.Parameters.Add("Location",System.Data.OleDb.OleDbType.VarChar,40,"Location");
cmd.Parameters.Add("Date",System.Data.OleDb.OleDbType.Date,0,"Date");

parameters are wrong........ should be
cmd.Parameters.Add("?", OleDbType.Char, 25, "Name")
cmd.Parameters.Add("?", OleDbType.Char, 25, "Location")
cmd.Parameters.Add("?", OleDbType.Date, 7, "thedate")
Each parameter has to be exactly the same order as it is in your "insert into statement and one more thing.............. though not sure.... dont use date as a column name in your database I think it is an SQL keyword......

use "?" when u are using Oledb..... you can specify column names, only when you are using SqlClient namespace