How to get ASP Classic IF to check for empty or NULL values on multiple columns

Question

In a MS SQL table I have 4 columns that can have 3 different values of (yes, empty or NULL), in these 4 columns I need to check with ASP Classic if they are all either empty or NULL.

I.e I have Col1, Col2, Col3 and Col4, and if they all are either empty or NULL (or different from yes) it should write something to the screen, so I have tried to do this with the following:

<% IF objFlowVAR("Col1") = "" OR IsNull(objFlowVAR("Col1")) AND objFlowVAR("Col2") = "" OR IsNull(objFlowVAR("Col2")) AND objFlowVAR("Col3") = "" OR IsNull(objFlowVAR("Col3")) AND objFlowVAR("Col4") = "" OR IsNull(objFlowVAR("Col4")) THEN %>Some Text<% END IF %>

<% IF objFlowVAR("Col1") <> "yes" AND objFlowVAR("Col2") <> "yes" AND objFlowVAR("Col3") <> "yes" AND objFlowVAR("Col4") <> "yes" THEN %>Some Text<% END IF %>

But this is not given me the result I need.. What am I doing wrong?

Answer 1

Both Null and DbNull are different so don’t expect IsNull() to behave as you expect.

I’ve found the best approach is the sanitise the values first;

Dim col1, col2, col3, col4
col1 = objFlowVAR("Col1") & ""
col2 = objFlowVAR("Col2") & ""
col3 = objFlowVAR("Col3") & ""
col4 = objFlowVAR("Col4") & ""
If Len(col1) = 0 And Len(col2) = 0 And Len(col3) = 0 And Len(col4) = 0 Then
  'All values are empty, so do sonething.
End If

Answer 2

I'm not sure if you need to do one thing if it is null and another if it is not null, but using what user692942's method of instead of doing IsNull() and just checking length to see if its greater than 0 is indeed the best way to do this, especially if you are only dealing with 'yes' as the only string. If you did have other options than just yes later, breaking it out into parts like I show below might be useful to you, but I based it off his example above so you would need to use the dims and same variable settings of course. You can even do nested IF/THEN's if you must, but it is more efficient to use CASE SELECT if you have more elaborate combinations of outcomes. But on to the code:

    IF Len(col1) > 0  Then
      'col1 is NOT NULL
    ELSE
      'col1 is NULL
    END IF

You could get one perfect with the code in place for each if it is null and it isn't null situation, then copy that and past it 3 more times, and just change the numbers on the other 3 to be col2 and col3 and so forth, but would be easy to get the whole thing in place fast if this is what you need instead.

Since someone saw this useful I thought I would cover a CASE SELECT too just in "CASE" there is need for more advanced logic, such as if it might matter if it was NULL versus '' empty or different string types, as well as a default everything else catcher, very handy stuff for this sort of thing.

Select Case col1
   Case col1 = "" : 'code to do when its actually empty
   Case col1 = Len(col1) > 0 AND col1 <> "" : 'code to do when its NULL
   Case col1 = "yes" :  'code to do when its set to 'yes'
   Case col1 = "no" :  'code to do when its set to 'no'
Case Else
   'code to do when everything else not defined happens
End Select 

The 'code to do parts are just comment lines that you would replace everything from the ' after it with the ASP code you want to happen what that case triggers.