Home > Archive > ASP > November 2005 > Problem with Len() Function & Null Values
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Problem with Len() Function & Null Values
|
|
|
| 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] 2005-07-24, 8:19 pm |
| 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" <sbailey@mileslumber.com> wrote in message
news:ekJuHjljFHA.2920@TK2MSFTNGP14.phx.gbl...
> 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
>
| |
|
| first, what is COALESCE?
I found the culprit, I inserted zeros for null values, staying up too late
again.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:ebj6m2ljFHA.2792@TK2MSFTNGP10.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" <sbailey@mileslumber.com> wrote in message
> news:ekJuHjljFHA.2920@TK2MSFTNGP14.phx.gbl...
>
>
| |
| Ray Costanzo [MVP] 2005-07-24, 8:19 pm |
| 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/...spx?qu=coalesce
Ray at home
"scott" <sbailey@mileslumber.com> wrote in message
news:OPrMC$ljFHA.3972@TK2MSFTNGP10.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]" <ten.xoc@dnartreb.noraa> wrote in
> message news:ebj6m2ljFHA.2792@TK2MSFTNGP10.phx.gbl...
>
>
| |
| Aaron Bertrand [SQL Server MVP] 2005-07-24, 8:19 pm |
| > 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.
| |
|
| Hi.
Did you try with the IsNull function in ASP?
This code worked for me:
if (IsNull (PokerConn.fields("StandBy"))) then Response.Write("n/a") else Response.Write(PokerConn.fields("StandBy"))
quote:
Originally posted by 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
| |
|
| 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
quote:
Originally posted by Jth
Hi.
Did you try with the IsNull function in ASP?
This code worked for me:
if (IsNull (PokerConn.fields("StandBy"))) then Response.Write("n/a") else Response.Write(PokerConn.fields("StandBy"))
|
|
|
|
|