Problem with Len() Function & Null Values

[scott]

i've come across a real head-hurter. I'm looping through a recordset and
response.writing it's rows out with no problem except 1 field. The field
type is varchar and contains words like meeting, holiday, etc.

Problem is, I'm trying to render a "n/a" when the field is null as in
LISTING 1 below. My code isn't catching the null values. How can I test for
null values? I could swear I've successfully used the Len() test like below
successfully on similiar null varchar fields, but perhaps not.

Any ideas?

LISTING 1:

If Len(objRS(7)) < 1 Then
xTeamName= "n/a" ' this is problem line
Else
xTeamName = objRS(7)
End If

 

[Aaron Bertrand [SQL Server MVP]]

Why not do this in the query? You can use COALESCE(col, 'n/a') in SQL
Server, or NULLIF or IIF in Access.

Or, instead of the way you're doing it,

rs7 = trim(objRS(7))
if len(rs7) = 0 then rs7 = "n/a"
response.write rs7

I'm guessing there is a blank space, not a NULL value, and hence
len(objRS(7)) = 1, and falls into the else.

 

[scott]

first, what is COALESCE?

I found the culprit, I inserted zeros for null values, staying up too late
again.

 

[Ray Costanzo [MVP]]

Do you have SQL Server installed? If so, look in BOL (Books Online).

Start--Run---%windir%\hh.exe "C:\Program Files\Microsoft SQL
Server\80\Tools\Books\SQL80.col"
(Or whatever directory SQL Server is installed in)

http://search.microsoft.com/search/results.aspx?qu=coalesce

Ray at home

 

[Aaron Bertrand [SQL Server MVP]]

first, what is COALESCE?

It is a built-in SQL Server function that takes 2 or more parameters, and
returns the first non-NULL value.