We help IT Professionals succeed at work.

IsNull function error

agzis
agzis asked
on
When I call function IsNull against ADO recordset values I'll receive an error that function IsNull should support array datatype. Why this happened? Thanks.
Comment
Watch Question

Commented:
Can you show the code snippit that is giving you this error as well as the data type of the column that it is blowing up on?

Author

Commented:
      Dim i, j As Integer
       'i-col;j-row
       j = 1: i = 0
       
       If Not Reds.EOF Then Reds.MoveFirst
                     
       While Not Reds.EOF
           If SSheet.Rows <= j Then
              SSheet.Rows = SSheet.Rows + 1
           End If
       
           SSheet.Row = j
           For i = 0 To (Reds.Fields.Count - 1)
               SSheet.Col = i
               If IsNull(Reds(i)) Then    
                  SSheet.Text = ""
               Else
                  SSheet.Text = Trim(Reds(i))
               End If
           Next i
           Reds.MoveNext
           j = j + 1
       Wend
Commented:
Well, the dangerous thing you are doing is assuming that the fields collection of the Reds recordset is the default property, and you're assuming that the Value property of the field item is the default property of the field object.  Now, that may all be true, but you're also assuming that the IsNull function is smart enough to resolve all of that.

Whenever you reference any object, you should fully qualify it.  I think you will succeed if you change your code to this:

 
      Dim i, j As Integer
      'i-col;j-row
      j = 1: i = 0
     
      If Not Reds.EOF Then Reds.MoveFirst
                     
      While Not Reds.EOF
          If SSheet.Rows <= j Then
             SSheet.Rows = SSheet.Rows + 1
          End If
     
          SSheet.Row = j
          For i = 0 To (Reds.Fields.Count - 1)
              SSheet.Col = i
              If IsNull(Reds.Fields(i).Value) Then    
                 SSheet.Text = ""
              Else
                 SSheet.Text = Trim(Reds.Fields(i).Value)
              End If
          Next i
          Reds.MoveNext
          j = j + 1
      Wend  

There is another simpler shortcut to this, you don't even have to check for nulls if you just append an empty string during the assignment:

      Dim i, j As Integer
      'i-col;j-row
      j = 1: i = 0
     
      If Not Reds.EOF Then Reds.MoveFirst
                     
      While Not Reds.EOF
          If SSheet.Rows <= j Then
             SSheet.Rows = SSheet.Rows + 1
          End If
     
          SSheet.Row = j
          For i = 0 To (Reds.Fields.Count - 1)
              SSheet.Col = i
              SSheet.Text = Trim(Reds.Fields(i).Value & "")
          Next i
          Reds.MoveNext
          j = j + 1
      Wend  

Commented:
Function NullText(Text As Variant) As String
    If IsNull(Text) Then
        NullText = vbNullString
    Else
        NullText = Text
    End If
End Function



SSheet.Text = NullText(trim(Reds(i))

Commented:
Another option is not to use isnull, a faster way that I've found is as follows
variable = rscordset.fields("fieldname") & ""
This will never pop up the Invalid use of null error.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.