Search code examples
vbscriptasp-classic

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

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?

Solution

  • 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