Problem with Len() Function & Null Values

[scott]

i've come across a real head-hurter. I'm looping through a recordset and
response.writin g 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" <sb*****@milesl umber.com> wrote in message
news:ek******** ******@TK2MSFTN GP14.phx.gbl...

i've come across a real head-hurter. I'm looping through a recordset and
response.writin g 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

[scott]

first, what is COALESCE?

I found the culprit, I inserted zeros for null values, staying up too late
again.
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartr eb.noraa> wrote in message
news:eb******** ******@TK2MSFTN GP10.phx.gbl...

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" <sb*****@milesl umber.com> wrote in message
news:ek******** ******@TK2MSFTN GP14.phx.gbl...

i've come across a real head-hurter. I'm looping through a recordset and
response.writin g 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

[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.co l"
(Or whatever directory SQL Server is installed in)

http://search.microsoft.com/search/r...px?qu=coalesce

Ray at home
"scott" <sb*****@milesl umber.com> wrote in message
news:OP******** ******@TK2MSFTN GP10.phx.gbl...

first, what is COALESCE?

I found the culprit, I inserted zeros for null values, staying up too late
again.
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartr eb.noraa> wrote in
message news:eb******** ******@TK2MSFTN GP10.phx.gbl...

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" <sb*****@milesl umber.com> wrote in message
news:ek******** ******@TK2MSFTN GP14.phx.gbl...

i've come across a real head-hurter. I'm looping through a recordset and
response.writin g 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]]

> 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.

[Jth]

Oh, well, in your case I think that might be something like:
If IsNull(objRS(7) ) Then
xTeamName= "n/a" ' this is problem line
Else
xTeamName = objRS(7)
End If

Jth wrote:

*Hi.

Did you try with the IsNull function in ASP?

This code worked for me:

if (IsNull (PokerConn.fiel ds("StandBy")) ) then Response.Write( "n/a")
else Response.Write( PokerConn.field s("StandBy")) *

--
Jth
------------------------------------------------------------------------
Posted via http://www.codecomments.com
------------------------------------------------------------------------

[Jth]

Hi.

Did you try with the IsNull function in ASP?

This code worked for me:

if (IsNull (PokerConn.fiel ds("StandBy")) ) then Response.Write( "n/a")
else Response.Write( PokerConn.field s("StandBy"))


scott wrote:

*i've come across a real head-hurter. I'm looping through a recordset
and
response.writin g 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 *

--
Jth
------------------------------------------------------------------------
Posted via http://www.codecomments.com
------------------------------------------------------------------------