标准表达式中的数据类型不匹配 从 Excel 文件到 OLEDB
我正在通过 ASP.NET 页面处理上传的文件。我使用以下连接字符串:
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
excelFile + @";Extended Properties=""Excel 8.0;HDR=YES;""";
这是 SQL 语句:
string sql = "SELECT * FROM [Sheet1$] WHERE [req_tf_order_no] <> ''";
这是循环遍历 DataReader 的代码
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
using (OleDbCommand command = connection.CreateCommand())
{
command.CommandText = sql;
connection.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
hasMoreData = reader.Read();
while(hasMoreData)
{
...
hasMoreData = reader.Read();
if (hasMoreData == false)
{
break;
}
}
}
}
这是大部分堆栈跟踪:
(Error Description: Data type mismatch in criteria expression.)
(Stack Trace: at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteReader() at ...
如果我不包含 WHERE 子句(WHERE [req_tf_order_no] < > ''
),我没有收到任何错误。 WHERE 子句的唯一原因是过滤掉输入文件中已清除但未删除的行。
我怀疑输入 Excel 文件的格式或数据中存在某些问题导致此问题。我有一些输入文件没有抛出异常,但大多数都抛出异常。是什么导致了这个错误以及如何修复它?有没有更好的方法来完成我想做的事情?我可以让用户上传不同的文件格式吗?
编辑 发现在 SQL 语句中检查 null 而不是空字符串可以正常工作,并且不会引发异常。
string sql = "SELECT * FROM [Sheet1$] WHERE [req_tf_order_no] IS NOT NULL";
I am processing an uploaded file through a ASP.NET page. I am using the following connection string:
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
excelFile + @";Extended Properties=""Excel 8.0;HDR=YES;""";
Here is the SQL statement:
string sql = "SELECT * FROM [Sheet1$] WHERE [req_tf_order_no] <> ''";
Here is the code that loops through the DataReader
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
using (OleDbCommand command = connection.CreateCommand())
{
command.CommandText = sql;
connection.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
hasMoreData = reader.Read();
while(hasMoreData)
{
...
hasMoreData = reader.Read();
if (hasMoreData == false)
{
break;
}
}
}
}
Here is most of the stack trace:
(Error Description: Data type mismatch in criteria expression.)
(Stack Trace: at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteReader() at ...
If I don't include the WHERE clause(WHERE [req_tf_order_no] <> ''
), I don't get any error. The only reason for the WHERE clause is to filter out cleared but not deleted rows in the input file.
I suspect there is something in the formatting or data of the input Excel file that is causing this. I have had some input files that have not thrown an exception but most do. What is causing this error and how can it be fixed? Is there a better way to accomplish what I am trying to do? Can I perhaps have the user upload a different file format?
Edit
Found that checking for null rather than an empty string in the SQL statement works without an exception being thrown.
string sql = "SELECT * FROM [Sheet1$] WHERE [req_tf_order_no] IS NOT NULL";
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
发现在 SQL 语句中检查 null 而不是空字符串可以正常工作,并且不会引发异常。
string sql = "SELECT * FROM [Sheet1$] WHERE [req_tf_order_no] IS NOT NULL";
Found that checking for null rather than an empty string in the SQL statement works without an exception being thrown.
string sql = "SELECT * FROM [Sheet1$] WHERE [req_tf_order_no] IS NOT NULL";