Data type mismatch in criteria expression From Excel file through OLEDB

Question

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

Answer 1

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