Issue reading strings of "TRUE" and "FALSE"

[GoogleCodeExporter]

Okay, I am definitely willing to entertain that I am doing this wrong, but 
there seems to be an issue reading strings that are labeled as "true" and 
"false".

Here is the data set (in a tab named "Test2")
--------------------- 
Serial_Number   SomeVal      SomeOtherVal
ABCD1232    TRUE         FALSE
ABCD1233    FALSE        FALSE
ABCD1234    TRUE         TRUE
ABCD1235    FALSE        TRUE

Here's the class
---------------------
public class TestSetting
{
        public string Serial_Number {get; set;}
        public string SomeVal {get; set;}
        public string SomeOtherVal {get; set;}
}

And here are the queries
--------------------------
string excelFileAt = @"C:\TestDataSet.xlsx";
var excel = new ExcelQueryFactory(excelFileAt);

var testQuery1 = from c in excel.Worksheet<TestSetting>("Test2")
             //  where (c.SomeOtherVal.ToUpper() == "TRUE")
                 select c;

var testQuery2 = from c in excel.Worksheet<TestSetting>("Test2")
                 where (c.SomeOtherVal.ToUpper() == "TRUE")
                 select c;

What is the expected output? What do you see instead?

     testQuery1 returns a count of 4 rows, which is expected.
     testQuery2 returns "Sequence contains no elements", which is not expected. Count should be 2. 

What version of the product are you using? On what operating system?
     ASP.NET targeted for .NET 4.0 framework
     MS Visual Web Developer 2010

Please provide any additional information below.
     As an aside, the same query works as expected when the dataset is changed so that TRUE is "AA", and FALSE is "BB", and c.SomeOtherVal == "AA", so I do believe it is directly related to the fact that the strings happen to be boolean. 

Original issue reported on code.google.com by steven.v...@gmail.com on 30 Mar 2011 at 1:07

[GoogleCodeExporter]

I also tried (for the sake of completeness) 

where (c.SomeOtherVal == "TRUE")
where (c.SomeOtherVal == "True")
where (c.SomeOtherVal.ToUpper().Equals("TRUE") == true)

Which all result in a "Sequence contains no elements"

When I tried 
where (c.SomeOtherVal.Equals("TRUE") == true)

It gives a different problem, which I really don't understand. 
'TRUE' is not a valid column name. Valid column names are: 'Serial_Number', 
'SomeVal', 'SomeOtherVal'

Original comment by steven.v...@gmail.com on 30 Mar 2011 at 1:13

[GoogleCodeExporter]

Steve, can you attach an example spreadsheet that is experiencing this issue. 
It will help me research the issue.

Original comment by paulyo...@gmail.com on 1 Apr 2011 at 9:48

[GoogleCodeExporter]

Paul,

File attached. Please let me know if there's anything else you need from me.

~Steve

Original comment by steven.v...@gmail.com on 4 Apr 2011 at 1:31

Attachments:

• TestDataSet.xlsx

[GoogleCodeExporter]

Turns out that the Jet Database engine automatically converts "TRUE" and 
"FALSE" strings to boolean.

All you should have to do is update the SomeVal and SomeOtherValue properties 
to be bool rather than strings. And here is how your new query would look: 

var testQuery2 = from c in excel.Worksheet<TestSetting>("Test2")
                 where (c.SomeOtherVal == true)
                 select c;

Original comment by paulyo...@gmail.com on 23 May 2011 at 3:51

• Changed state: Fixed