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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
betawarz
Posts: 10
Joined: Mon 06 Dec 2010 18:15

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

Post by betawarz » Tue 23 Jul 2013 18:30

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

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

Post by Pinturiccio » Wed 24 Jul 2013 13:17

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;

Post Reply