[thelist] Testing for the various NULL's (.end thread)

[Ken Schaefer]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Rob Smith" <rob.smith at THERMON.com>
Subject: RE: [thelist] Testing for the various NULL's (.end thread)


: Actually you were right on the money Josh. However the syntax:
:
: >if manager.fields.item("Address2").value is not NULL then
:
: didn't work exactly; something about "Object Required". I think the
keyword
: TO and IS aren't fully supported. I know this is true for select...case
: statements. So I did some reformatting and came up with
:
: if not manager.fields.item("Address2").value = NULL
:
: But dangit! that's where I started from.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


If you are doing this in VBScript, you need to use the IsNull() function

If not isNull(objRS.Fields.Item("myField").Value) then
    ' Field is not NULL
End If

If you are doing this in *SQL* then you use the syntax provided:

SELECT
    myField
FROM
    myTable
WHERE
    myField Is Not NULL


(the Is Not Null syntax is not valid VBScript). The rest of the posts
explaining that NULLs do not equal anything else, including other NULLs is
very correct.

If you have a field where there is a possibility that it is both NULL, or a
zero length string, and you want to cut-down on the amount of VBScript
checking, then use the SQL COALESCE function in your stored procedure
(COALESCE is DB portable, as it is part of the ANSI SQL specification):

SELECT
    COALESCE(myField, '') AS myField
FROM
    myTable

will return myField if it is not NULL (even a ZLS), or a ZLS otherwise. In
your ASP page you can then do:

If objRS.Fields.Item("myField").Value = "" then
    ...
End If

Cheers
Ken