Code not running when Field is NULL - SSRS 2005

Question

I have a textbox in my SSRS 2005 report. The expresssion for this textbox is:

=IIF(IsDBNull(Fields!fOrgID), Code.SetMyVar("null"), Code.SetMyVar(Fields!fOrgID.Value))

I have also tried IsNothing(Fields!fOrgID) and a few other variations of checking for nulls.

I have modified the SetMyVar function for testing and it now looks like this:

Public Function SetMyVar (var as String)

   MsgBox(var, VbOKCancel, "Test1")

   If var Is Nothing Then
        Return "NOTHING"
    Else
         MyVar = var
         Return var
    End If

End Function

I also have the public variable MyVar:

Public Shared Dim MyVar as String

When my database query returns data, this correctly evaluates, a messagebox is displayed with the value, the textbox gets set with the value, and the world is generally a happier place.

When my database query does not return a value though, I get the error: "The query returned no rows for the data set. The expression therefore evaluates to null." and the SetMyVar function never appears to be ran (you never get the messagebox popup). As expected, my emotions range from anger, sadness, and bitter hatred of SSRS.

I read something about SSRS evaluating both sides of an IF statement, so perhaps that is why I get the error (likely then on "Code.SetMyVar(Fields!fOrgID.Value)")... not sure how I get around that though.

Thoughts? Suggestions? Words of comfort?

Answer 1

I believe you're right about about Iif always evaluating both of its value arguments (at least, it does in Visual Basic). I'm not sure why you're getting this precise error (unless strings can't be assigned a value of DBNull?), but you almost certainly want to attack this problem with a different method.

The reason for this is that your current code will likely always call both set methods regardless of the conditional value.

Answer 2

I tried this too (also tried a version with IsNothing)...

=Code.SetField(IsDBNull(Fields!fOrgID))

And changed the fuction to be one that accepts a boolean. I figure this above function would always return a true or false, but in the event of a NULL, I again get "The query returned no rows for the data set. The expression therefore evaluates to null.".

I need to pass back to my code if the field is null or not (as this will let me know if the datasource is null or not).

Let me know if you can think of a better way because I cannot.

Answer 3

From the sound of things, it seems likely that the issue is that SSRS is having a problem displaying zero records. I'd recommend one of the following:

1) Use a control that handles zero records appropriately (Tables do. I think Lists do as well).

2) Modify your query to return a single record with blank values if it would otherwise return zero records.