Excel中的Jet OLE DB:插入日期时间值 [英] Excel Jet OLE DB: Inserting a DateTime value
问题描述
OLEDB可用于读取和写入Excel表。请看下面的code例如:
OLEDB can be used to read and write Excel sheets. Consider the following code example:
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\my\\excel\\file.xls;Extended Properties='Excel 8.0;HDR=Yes'")) {
conn.Open();
OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1] ([Column1] datetime)", conn);
cmd.ExecuteNonQuery();
cmd = new OleDbCommand("INSERT INTO Sheet1 VALUES (@mydate)", conn);
cmd.Parameters.AddWithValue("@mydate", DateTime.Now.Date);
cmd.ExecuteNonQuery();
}
这工作完全正常。插入数字,文字等,也是行之有效的。然而,在一个的的时间分量插入值的失败:
This works perfectly fine. Inserting numbers, text, etc. also works well. However, inserting a value with a time component fails:
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\my\\excel\\file.xls;Extended Properties='Excel 8.0;HDR=Yes'")) {
conn.Open();
OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1] ([Column1] datetime)", conn);
cmd.ExecuteNonQuery();
cmd = new OleDbCommand("INSERT INTO Sheet1 VALUES (@mydate)", conn);
cmd.Parameters.AddWithValue("@mydate", DateTime.Now); // <-- note the difference here
cmd.ExecuteNonQuery();
}
执行这个INSERT失败与OleDbException:在标准EX pression数据类型不匹配的
这是一个已知的bug?如果是的话,可以做些什么来解决办法呢?我已经找到了一个解决方法,如下:
Is this a known bug? If yes, what can be done to workaround it? I've found one workaround that works:
cmd = new OleDbCommand(String.Format(@"INSERT INTO Sheet1 VALUES (#{0:dd\/MM\/yyyy HH:mm:ss}#)", DateTime.Now), conn);
基本上,它会创建一个SQL语句,看起来像这样: INSERT INTO工作表Sheet1 VALUES(第05 /2011分之2913点一十二分01秒#)
。当然,我没有告诉你有多难看,这是。我宁愿有一个参数化查询的解决方案。
It basically creates an SQL statement that looks like this: INSERT INTO Sheet1 VALUES (#05/29/2011 13:12:01#)
. Of course, I don't have to tell you how ugly this is. I'd much rather have a solution with a parameterized query.
推荐答案
这似乎是一个已知的错误<一href="https://connect.microsoft.com/VisualStudio/feedback/details/94377/oledbparameter-with-dbtype-datetime-throws-data-type-mismatch-in-criteria-ex$p$pssion" rel="nofollow">https://connect.microsoft.com/VisualStudio/feedback/details/94377/oledbparameter-with-dbtype-datetime-throws-data-type-mismatch-in-criteria-ex$p$pssion
It appears to be a known bug https://connect.microsoft.com/VisualStudio/feedback/details/94377/oledbparameter-with-dbtype-datetime-throws-data-type-mismatch-in-criteria-expression
您可能需要截断milisecond这样显得自己工作OleDbParameter:
You might want to truncate the milisecond like this it appear to work for OleDbParameter:
DateTime org = DateTime.UtcNow;
DateTime truncatedDateTime = new DateTime(org.Year, org.Month, org.Day, org.Hour, org.Minute, org.Second);
和添加DateTime.Now这个,而不是到你的参数值。
And add this instead of the DateTime.Now into your parameter value.
这篇关于Excel中的Jet OLE DB:插入日期时间值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!