NULL fields in VB RecordSets 2

[bitwise]

How are you supposed to handle NULL values when retrieving data from a database. I have a table with three attributes namely, (ID: integer, Name: varchar(32), Type: integer). However, the Type attribute can be NULL. Well, when you try to get the data back from the database via a VB recordset by going

Dim Type As Long
...
Type = MyRecSet(2)

You get a runtime error: "Invalid use of Null". My question is how do I deal with this scenario, namely check if a field value is Null and take appropriate action?

Thanks,
-bitwise

 

[madlarry]

Test the field with the IsNull() function...

If Not IsNull(FieldName) Then
' Do something interesting
End If


It'll add alot more code, but worth it in the long run.

 

[rgh]

Dunno if this will help you out, got it from Visual Basic Programmers Journal 101 Tech Tips.

intNumber = catchnull(rst.field("Input&quot,0)

Function CatchNull(vOldValue As _
Variant, vNewValue As Variant) As Variant
' Check for Null
If IsNull(vOldValue) Then
' If Null, use the new value
CatchNull = vNewValue
Else
' Otherwise, use the existing value
CatchNull = vOldValue
End If
End Function

 

[daimaou]

Go with Madlarry sugestion, it's the easier way. This is the way I do it when I need to check if a recordset field is null.

 

[bitwise]

Thanks for the input guys. That was the ticket.

bitwise

 

[Guest_imported]

rgh way will handle any field form any database whereas Madlarry suggestion could leave you with lots of if statements if your database gets bigger