Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Problem with Len() Function & Null Values

Reply
Thread Tools

Problem with Len() Function & Null Values

 
 
scott
Guest
Posts: n/a
 
      07-22-2005
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


 
Reply With Quote
 
 
 
 
Aaron Bertrand [SQL Server MVP]
Guest
Posts: n/a
 
      07-22-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
 
 
 
scott
Guest
Posts: n/a
 
      07-22-2005
first, what is COALESCE?

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


"Aaron Bertrand [SQL Server MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>

>
>



 
Reply With Quote
 
Ray Costanzo [MVP]
Guest
Posts: n/a
 
      07-22-2005
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/r...px?qu=coalesce

Ray at home


"scott" <(E-Mail Removed)> wrote in message
news:OPrMC$(E-Mail Removed)...
> first, what is COALESCE?
>
> I found the culprit, I inserted zeros for null values, staying up too late
> again.
>
>
> "Aaron Bertrand [SQL Server MVP]" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>

>>
>>

>
>



 
Reply With Quote
 
Aaron Bertrand [SQL Server MVP]
Guest
Posts: n/a
 
      07-22-2005
> 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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Unpack less values from function's return values Chris Rebert Python 1 05-28-2009 02:47 PM
createImage sometime returns null and sometime returns non-null. vizlab Java 3 10-17-2007 11:21 AM
"stringObj == null" vs "stringObj.equals(null)", for null check?? qazmlp1209@rediffmail.com Java 5 03-29-2006 10:37 PM
VB.NET Null to SQL Null (ASP.NET 2.0 GridView) Kivak Wolf ASP .Net 2 06-28-2005 02:01 PM
write a function such that when ever i call this function in some other function .it should give me tha data type and value of calling function parameter komal C++ 6 01-25-2005 11:13 AM



Advertisments