Setting a DateTime Parameter to DBNULL.Value

[Guest]

I have a table with a DateTime column. I have a SQLDataAdapter which queries
the table and then fills a TypedDataSet. The DataAdapter has a parameter
value in the DateTime column.

I want to find all the rows with nulls in this DataTime column.

So, I set the ...SelectCommand.Parameters("Name of Parameter").Value =
DBNULL.Value

Even though I am certain there are rows with nulls in these columns, the
query returns no rows.

Is there a special way to look for nulls in DateTime columns?

Michael

 

[Rami Saad]

Hello,

In your select command, you should add the command "SET ANSI_NULLS OFF", to
be able to use null values.
This is usually used in stored procedures. But it can be inserted in the
beginning of a text command as well.
Try it out, and let me know of the results. And don't hesitate to
post/reply back if you have more problems, or if I misunderstood yours.

Regards,
Rami Saad
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC

 

[Guest]

I was trying to let the SQLDataAdapter write all of the SQL text and then
just assign values to the parameters. In this case I wanted to assign the
DBNULL.Value to one of the parameters. During compilation and runtime, the
assignment occurs but the rows with nulls are not returned correctly.

Possibly, what is happening is that in the DataAdapter wizard, when you
place a parameter value in the criteria column, the SQL text generated has
an "=" preceding the parameter ( where TheValue=TheParameter). When what is
really needed is an "IS" (where TheValue IS DBNULL.Value).

I can't find a way within the SQLDataAdapter to "SET ANSI_NULLS OFF". I
would need to manipulate the SelectCommand text.

There must be some simple thing I'm missing. I should be able to use
DBNULL.Value in a parameter assignment.

Michael

 

[Rami Saad]

Hello,

for "SET ANSI_NULLS OFF" you need to manipulate your command text. As I
recall, it's your Update command.
As for the parameter, it's (where the_value LIKE dbnull.Value).
Please try this out and let me know of the results.

Regards,
Rami Saad
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC