Error inserting DateTime values

[Vincent]

Hi !

I'm trying to insert a datetime into a Access Database and
I've a "Data type mismatch in criteria expression"
Exception.

I need to store the Date AND the Time. ( using
System.Data.DbType.Date works but do not store the Time)

I've written a very cimple program that error :

Any help greathly apreciated, Thanks !

Vincent

// Start Exmaple

DateTime insertedDate = DateTime.Now;
string databaseName = "../../somedb.mdb";

using(IDbConnection connection = new OleDbConnection
(String.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source={0};",databaseName)))
{
try
{
connection.Open();

// Cleanup Table
IDbCommand deleteCmd = connection.CreateCommand();
deleteCmd.CommandText ="DELETE * FROM sometable";
deleteCmd.ExecuteNonQuery();

// Insert date
IDbCommand cmd = connection.CreateCommand();
cmd.CommandText ="INSERT INTO sometable
(somedatefield) VALUES(?)";

IDbDataParameter parameter= cmd.CreateParameter();
parameter.DbType = System.Data.DbType.DateTime;
parameter.Value = insertedDate;

cmd.Parameters.Add(parameter);
cmd.ExecuteNonQuery();
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
}

 

[Prodip Saha]

Need to use OleDbType.DBTimeStamp......

See article below for details:-
http://support.microsoft.com/default.aspx?scid=kb;en-us;320435

Prodip Saha

 

[Vincent]

Hi

Thanks, but it's not enought, Setting
OleDbParameter.DbType to DbType.DateTime set automatically
OleDbParameter.OleDbType to OleDbType.DBTimeStamp.

To be sure, I replace the line :

parameter.DbType = System.Data.DbType.DateTime;

with :

((OleDbParameter)parameter).OleDbType =
System.Data.OleDb.OleDbType.DBTimeStamp;

And I still have the same error...

And the other reason wy I dislike play with the OleDbType
is I don't want to play with OleDbParameter directly,
that's what Interfaces are for, no ?

 

[Ponga Pundit]

The high resolution of DateTime type over that of the OleDb type prevents the conversion perhaps.

The following works, though not elegant:

DateTime.Parse(DateTime.Now.ToString())


From http://developmentnow.com/g/7_2004_3_0_0_129711/Error-inserting-DateTime-values.ht

Posted via DevelopmentNow.com Group
http://www.developmentnow.com

 

[Ponga Pundit]

The following also works:

DateTime dtNow = DateTime.Now;
parameter.Value = new DateTime(dtNow.Year, dtNow.Month, dtNow.Day, dtNow.Hour, dtNow.Minute, dtNow.Second, dtNow.Millisecond);


From http://developmentnow.com/g/7_2004_3_0_0_129711/Error-inserting-DateTime-values.ht

Posted via DevelopmentNow.com Group
http://www.developmentnow.com

 

[Cor Ligthert [MVP]]

Ponga,

DateTime dtNow = DateTime.Now;
parameter.Value = new DateTime(dtNow.Year, dtNow.Month, dtNow.Day,
dtNow.Hour, dtNow.Minute, dtNow.Second, dtNow.Millisecond);

But that is the same as

parameter.Value = DateTime.Now;

All three are in my idea better than your previous reply,

Cor