Can someone explain this ISNULL sentence for me?...

[Jan Nielsen]

Hi
I am working with rowfilters in dataviews.
I would like to filter for empty fields (= null value in the database)
I found this sentence on msdn:
****************
To return only those columns with null values, use the following expression:
"Isnull(Col1,'Null Column') = 'Null Column'"
*************************'
So I tried like this:
mainDataSource.RowFilter = "Gender = 'male' and " & "Isnull(floor,'Null
Column') = 'Null Column'"
And this actually works returning males that does not have a value in the
floor field.

But I do not understand why.
Isnull returns a boolean and does not take a column and a string. It looks
more like the NZ function from visual basic.

Can someone explain this to me?

Best regards

Jan

 

[Ken Tucker [MVP]]

Hi,

SQL statement that replaces a null with the specified value.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_6mek.asp

Ken
------------------
Hi
I am working with rowfilters in dataviews.
I would like to filter for empty fields (= null value in the database)
I found this sentence on msdn:
****************
To return only those columns with null values, use the following expression:
"Isnull(Col1,'Null Column') = 'Null Column'"
*************************'
So I tried like this:
mainDataSource.RowFilter = "Gender = 'male' and " & "Isnull(floor,'Null
Column') = 'Null Column'"
And this actually works returning males that does not have a value in the
floor field.

But I do not understand why.
Isnull returns a boolean and does not take a column and a string. It looks
more like the NZ function from visual basic.

Can someone explain this to me?

Best regards

Jan

 

[Jan Nielsen]

Hi Ken
Thanks for answering
Well at least it clarified one thing: isnull is a sql function. I looked in
the help for the vb functions.
It works very much like the nz function in vb.

But still...
If a datarow has a dbnull value I would expect the filter to become
something like this then:
mainDataSource.RowFilter = "Gender = 'male' and " 'Null Column' = 'Null
Column'"

Obviously this is incorrect as the column floor is not mentioned at all and
the last part will evaluate to true so gender = 'male' and TRUE.

I still do not get it

Jan

 

[Jan Nielsen]

I'm back.
I kept thinking and now I think I understand it.
The filter will look at a row to see if gender is male. If so it will make
the isnull test for the floor column and if it is null it will return true
for this row (because 'null column' = 'null column' for this specific row)
which will then be included in the filter.

Is this about correct?

Jan