9

I'm getting an ADODB Recordset and I need to check the value of a nullable column in ASP Classic. How can I tell whether it's null? AFAIK IsDBNull doesn't exist in ASP Classic, and all the Null testers ask whether the object is null, not its value.

E.g., I have Recordset RS that contains a column RS("myCol"). In the database, myCol is a nullable bit, so values can be {0, 1, NULL}.

How can I test RS("myCol") = NULL? It appears that if I literally run that comparison it will only tell me whether the object RS("myCol") is Null, not whether the value of the field requested is "database" null.

feetwet
  • 3,248
  • 7
  • 46
  • 84
  • 3
    `IsNull()` will not work because ADO Nulls are treated differently to Null values in VBScript. Just use `Len(RS("myCol") & "") > 0` to check for Null columns. – user692942 Aug 13 '16 at 06:51
  • `IsNull` is the correct way for checking for the special value of `Variant/Null` which the database nulls are translated to. Concatenating nulls with a `""` in order to coerce them to an empty string, on the other hand, is a hack. – GSerg Jan 18 '23 at 10:32

4 Answers4

7

Try this, it should work if you are using MS SQL

IF IsNull(RS("myCol")) = False THEN
  IF RS("myCol") THEN
    Response.Write "myCol is TRUE"
  ELSE
    Response.Write "myCol is False"
  END IF
ELSE
  Response.Write "myCol is NULL"
END IF

or Try Using 

IF RS("myCol")<>"" THEN
  IF RS("myCol") THEN
    Response.Write "myCol is TRUE"
  ELSE
    Response.Write "myCol is False"
  END IF
ELSE
  Response.Write "myCol is NULL"
END IF
Karthikeyan Vedi
  • 1,360
  • 1
  • 11
  • 17
0

For a database column myCol of type nullable bit, the following will be true of a Recordset RS containing that column:

  • If the database value is 0, then RS("myCol") = Empty = False = 0
  • If the database value is 1, then RS("myCol") = True = 1
  • If the database value is Null, then IsNull(RS("myCol")) = True = 1.

Another solution:

  • If the database value is 0 then Len(RS("myCol")&"") = 5
  • If the database value is 1 then Len(RS("myCol")&"") = 4
  • If the database value is Null then Len(RS("myCol")&"") = 0
feetwet
  • 3,248
  • 7
  • 46
  • 84
  • 1
    Probably useful: http://stackoverflow.com/questions/14507526/error-checking-for-null-in-vbscript – John Aug 13 '16 at 06:24
  • 2
    `IsNull()` will not work because ADO Nulls are treated differently to Null values in VBScript. Just use `Len(RS("myCol") & "") > 0` to check for Null columns. – user692942 Aug 13 '16 at 06:49
  • 1
    @Lankymart: Your solution wouldn't work because both `0` and `Null` would produce `Len = 0`. I verified my answer by running in a real ASP classic website pulling from a real MS SQL database. So it works in my environment. Are you saying that it *shouldn't* work? Or that it is *not guaranteed* to work in such an environment? – feetwet Aug 13 '16 at 14:24
  • What are you on about? I was showing how to check for database nulls without receiving an error. Ofc it would be 0 that's the whole point of `> 0` check. – user692942 Aug 13 '16 at 14:49
  • @Lankymart: The question is *how to detect and distinguish between the three values of a nullable bit*. I.e., it's not enough to avoid an error when the value is Null. I need to be able to distinguish in the ASP between a value of Null and a value of 0. – feetwet Aug 13 '16 at 14:51
  • It can `0`, `1` or `Null` - `Len(RS("myCol") & "") > 0` deals with the null condition. So what's your point again? – user692942 Aug 13 '16 at 18:06
  • @Lankymart - If the value is 0/False then `Len(RS("myCol") & "") = 0` also. So `Len(RS("myCol") & "")` cannot distinguish between a value of Null and a value of zero for a database bit field. – feetwet Aug 13 '16 at 18:24
  • What are you going on about? I'm so confused now your talking absolute rubbish `Len(0 & "")` is `1`, `Len(DBNullValue & "")` is `0`. It you have a value *(any value!!)* it will have a length greater then zero. I've used this approach for years and years so don't even try and justify your comments because you are plain wrong matey. – user692942 Aug 13 '16 at 19:39
  • @Lankymart - I just tested your solution and found it works, so I added it to the answer. – feetwet Aug 14 '16 at 00:18
  • I know it does. But why would a value of `1` have a length of `4`?? Whatever your testing your doing it wrong should be length of `1`. The **whole** answer is just wrong. – user692942 Aug 14 '16 at 06:14
  • @Lankymart - Obviously because the server is choosing to interpret a bit value of 1 as "True" and 0 as "False." Don't shoot the messenger. These are the actual results on an actual system: IIS 6 or 7, MS SQL 2008 or 2012. – feetwet Aug 14 '16 at 09:29
  • First right thing you've said, forgot we are talking about bits so they will be interpreted as a `Boolean`. But like I said earlier the approach works for **any** value *(except object references)*. – user692942 Aug 14 '16 at 10:29
  • The third value on the second solution is for `Null` not `0`. – user692942 Aug 14 '16 at 13:06
0

This works for me:

Function A9000_Coalesce(SqlValue, ValueIfNull)

    If VarType(SqlValue) = vbNull Then 
        A9000_Coalesce = ValueIfNull
    Else
        A9000_Coalesce = SqlValue
    End If

End Function
סטנלי גרונן
  • 2,917
  • 23
  • 46
  • 68
Chris Johnson
  • 1
-2

In server side Javascript you have to test

if (RS("myCol").Value))

instead of

if (RS("myCol")))

so probably in VBScript you need to use 

IF IsNull(RS("myCol").Value) THEN

to actually test for the value and not the object.

Jeff
  • 736
  • 5
  • 14
  • 1
    I don't know about JavaScript but the `ADODB.Recordset` object has a default property of `Value` on the `Fields` collection so `RS("myCol")` is equivalent to `RS("myCol").Value`. – user692942 Aug 15 '16 at 07:13