How to write a select query using parameters that can be null?

[betawarz]

Hi all,

I'm trying to write a query like the following

Code: Select all

cmd.CommandText = "SELECT * FROM foo WHERE col = :val"

And, I'm inserting that parameter like the following

Code: Select all

cmd.Parameters.Add("val", PgSqlType.Date).Value = DBNull.Value

This example is simplified to show the exact situation, but the value is actually set using the result of a function that either returns a date or the dbnull.

But basically in this query, the syntax "WHERE col = null" is not valid. Does dotConnect convert this to "WHERE col IS null" for me? It doesn't look like it is because I'm not getting any results.

Any ideas?

[Pinturiccio]

NULL cannot be compared to any value using the comparison operators. As a workaround, you may use the following:

Code: Select all

cmd.CommandText = "SELECT * FROM foo WHERE col = :val OR (:val IS NULL AND col IS NULL)";
cmd.Parameters.Add("val", PgSqlType.Date).Value = DBNull.Value;