Problem with Len() Function & Null Values

Discussion in 'ASP General' started by scott, Jul 22, 2005.

  1. scott

    scott Guest

    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
    scott, Jul 22, 2005
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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], Jul 22, 2005
    #2
    1. Advertising

  3. scott

    scott Guest

    first, what is COALESCE?

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


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

    >
    >
    scott, Jul 22, 2005
    #3
  4. 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


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

    >>
    >>

    >
    >
    Ray Costanzo [MVP], Jul 22, 2005
    #4
  5. > 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.
    Aaron Bertrand [SQL Server MVP], Jul 22, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    5
    Views:
    26,611
    Mike Schilling
    Mar 29, 2006
  2. Josh Taylor
    Replies:
    0
    Views:
    277
    Josh Taylor
    Dec 30, 2005
  3. Tor Erik Soenvisen
    Replies:
    14
    Views:
    557
    Tim Roberts
    Nov 23, 2006
  4. maestro
    Replies:
    1
    Views:
    302
    Chris
    Aug 11, 2008
  5. MikeR
    Replies:
    5
    Views:
    148
    MikeR
    Jan 7, 2009
Loading...

Share This Page