Setting Parameter = IS NULL

[James Hancock]

I've got a Command that has a parameter. Sometimes it will be a
uniqueidentifier (Guid) but there is also the case of looking for the column
being null.

How do I tell the Parameter that I'm looking for NULLs? If I set .Value =
null I get an error, if I set it to DBNull.value I don't get a valid result.
If I set it to SQLType.SQLGuid.Null I don't get a valid result.

Thanks!
James Hancock

 

[Jon Skeet [C# MVP]]

I've got a Command that has a parameter. Sometimes it will be a
uniqueidentifier (Guid) but there is also the case of looking for the column
being null.

How do I tell the Parameter that I'm looking for NULLs? If I set .Value =
null I get an error, if I set it to DBNull.value I don't get a valid result.
If I set it to SQLType.SQLGuid.Null I don't get a valid result.

Setting it to DBNUll.Value should do the right thing. I suggest you do
that and use a profiler on the database to see what's being sent.

 

[James Hancock]

I did that and hte problem is that it's generating a query with = NULL
instead of IS NULL.

IS NULL pulls the values pefectly. = NULL does not (SQL 2000).

Anyone have any suggestion on how to make this work?

Thanks,
James Hancock

 

[Jon Skeet [C# MVP]]

I did that and hte problem is that it's generating a query with = NULL
instead of IS NULL.

IS NULL pulls the values pefectly. = NULL does not (SQL 2000).

Ah yes, the old NULL != NULL chestnut...

Anyone have any suggestion on how to make this work?

Hmm... the only thing I can think of would be some horrible contortion
where you use the parameter twice. Something like:

SELECT * FROM FOO WHERE
(@Foo IS NULL AND X IS NULL) OR (X=@Foo)

I haven't tested the above, but it *might* work!