|
Nulls in your database can be very annoying to deal with if you have never experienced them before. I will walk you through dealing with them in this article. We will discover how to know if a value is null, what code does and doesn't work with nulls, and how to detect and deal with them in your code.
To detecting a null value use the IsNull() function.
Response.Write(IsNull(variable))
If this prints out true then your variable has a null value. Usually what you would do is stick this in your database code where you are looping through the records.
The following code and results arise when dealing with nulls:
Testing the length of a variable like this: Len < 1 will not work.
Testing the variable for a value of "" will not work.
And you will not get an error when trying to Trim() a variable with a null value. Which can cause confusion on the programmer's part.
If you try to convert a null valued variable to a string with CStr() you will get the following error:
Microsoft VBScript runtime error '800a005e'
Invalid use of Null: 'CStr'
/path/file.asp, line 55
What to do that works: Test for nulls with IsNull(), if true then set them equal to "". Test for "", then set all those variables equal to a value that will print out nicely in your table like " ".
Here is a snippet of code that I used once to get the city and state from a database, test for nulls, and print out the correct code to maintain the background color of my table cell. I also was looking to avoid seeing "," printed out in my table when the city and state were nulls.
acity = RS(2)
astate = RS(3)
If IsNull(acity) Then
acity = ""
Else
acity = Trim(acity)
If Len(acity) < 1 Then
acity = ""
End If
End If
If IsNull(astate) Then
astate = ""
Else
astate = Trim(astate)
If Len(astate) < 1 Then
astate = ""
End If
End If
If acity = "" Then
acitystate = "<font size=2 face=arial color=black><b>" & astate
Else
acitystate = "<font size=2 face=arial color=black><b>" & acity & ", " & astate
End If
|