Checking for Null Value Unsuccessful

[Guest]

I retrieve data from sql dbo and place within a dataset datatable using a
simple select statement. One of the fields is nullable and the data returned
may be null. When I use IsDBNull(dr.("fieldname")), the check fails because
the actual contents are the characters "<NULL>". I can check the string value
for "<NULL>" and find it, but I cannot use IsDBNull, dr.IsNull("fieldname"),
or = System.DBNull.Value. Why is the null value being treated as a string and
filled in with the "<NULL>"?

 

[Patrice]

Have you checked the value stored in the DB ? Is it really NULL or already
the "<NULL>" string ?

 

[Guest]

I double-checked, but no, it is a true value in the dbo. When I write the
dataset containing the datatable to an xml document, the null value in the
field element is &ltNull&gt. If I check the string content value for equality
against the string value "<NULL>" then it works. This does not make sense to
me at all. I should be able to check for null.

 

[Patrice]

First once you get the DataTable back, can you check for NULL as expected
before saving to an XML document ?

For now I'm trying to understand at which step you have a problem and
especially if you have this problem once the DataTable is persisted to an
XML Document (and then restored ?)

It would allow to know if the problme is in getting back the data or
saving/restoring to an XML document.

 

[TC]

Why does that not make sense?

"<NULL>" and NULL are two different values. If it truly is "<NULL>" in
the database, then it is not NULL, and testing it for NULL will be
False - as it should be! No mysteries there.

The only real question is, why did the database designers or
programmers choose to use the string value "<NULL>", instead of leaving
the field NULL? You would have to ask them that.

HTH,
TC

 

[Guest]

I loop through the datatable using for each dr in datatable.rows ... . When I
check for the null value in a specific field location (not the string value),
the null value is never flagged. The value in the database is a true null
value (not a <NULL> string value). I used dataset.writexml to look at the
contents of the datatable and that is when I observed &ltNULL&gt. I also
observed in the watch window the value <NULL>. Then when I check for the
string equivalent instead of the is null value check, the value is flagged.
The whole situation does not make a bit of sense to me either. Since the code
is somewhat deep, my belief is that somewhere that field must be getting
translated into a string value equal to <NULL> (as described by TC).

 

[TC]

I still don't understand why you are confused..

Forget how the value gets exported to XML or whatever. Focus on what
the value is in the database.

The value in the database is either NULL, or it is NOT NULL.

If it is NULL, then it can not possibly be "<NULL>".

Conversely, if it is "<NULL>" - or any other string value - then
clearly, it is not NULL.

You test for NULL using your database's "is NULL" operator, whatever
that is, and you test for "<NULL>", if you wish to do that, using a
normal = test.

You do understand that: IF this = that is /always False/, if
either 'this' or 'that' is NULL?

HTH,
TC

 

[Patrice]

What if you try your own bare bone code against the DB ?

If it works correctly then you'll have to dig into the application data
access code where there is likely such a translation.

If it still doesn't work it could be done server side. Make sure you checked
in the DB the exact same object that is used in your code, for example the
server table could have true NULL values but if for example you are using a
strored proc or a view it could be "translated" here.

The last thing I see would be an unausal column (for example I believe
Oracale has huge numbers).



Good luck.

 

[TC]

I suspect he's not sure how to check for NULL.

He said (slightly reworded): "The value in the database is a true null
value, not a <NULL> string value. [But] when I check for the null value
...., not the string value, the null value is never flagged."

My bet: he is saying: IF <field> = NULL ...

TC

 

[TC]

Or - he is checking for IS NULL properly, but the value is an empty
string - so it just "looks" NULL.

TC