1

I have been researching why my below OleDBCommand ExecuteScalar function is not properly returning the correct data.

query = "SELECT Count(*) FROM NoteTable WHERE SQLMigrationFl <> 'Y'";
command = new OldDbCommand(query, connection);
var remainingNotes = (int)command.ExecuteScalar();

My connection is defined previously, and is successfully used by other queries. My Access database has 99 records in it; however, when I run the above code it only returns 10. When I remove the WHERE statement it returns all 99; however, that will not help when the SQLMigrationFl begins to be populated with a 'Y'. Any thoughts on why the ExecuteScalar function is not returning what I expect it to?

Thanks

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Jason W
  • 682
  • 1
  • 7
  • 18
  • If your `SQLMigrationFl` column can contain NULL values then you'll need to test for that condition, too. `WHERE SQLMigrationFl <> 'Y'` will not match rows where `SQLMigrationFl IS NULL`. – Gord Thompson Oct 06 '17 at 13:32
  • Yup, just changed my code to check for the nulls. If you would like to leave that as the answer, I will accept it. Do you know why SQLMigrationFl <> 'Y' does not pick up nulls? – Jason W Oct 06 '17 at 13:42
  • Any test with a `null` value (except `IS NULL`) will return `false` by design. So even though a null is not a 'Y', it will not match. – Hans Kesting Oct 06 '17 at 13:49

1 Answers1

1

If your SQLMigrationFl column can contain NULL values then you'll need to test for that condition, too. WHERE SQLMigrationFl <> 'Y' will not match rows where SQLMigrationFl IS NULL because NULL values are never equal (or unequal) to any other value. That is,

NULL = 'Y' is false, but
NULL <> 'Y' is also false, and even
NULL = NULL is false, which is why we need to use WHERE ... IS NULL to test for NULL values.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418