Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    19

    Unanswered: Problems with null values

    I'm reading data with ADO into a recordset. From the recordset, I create an XML string.

    Unfortunately, when I go from rst to XML, any null values are chopped off and don't appear in the XML tree. This causes problems in further developments.

    It would appear that the easiest way to fix the problem is to guarrantee that there are no nulls in the output data.

    I've tried ' ' + tbl.column as NewCol but when tbl.column is null, the result is null also.

    suggestions?

    For the record, for some damn reason the FOR XML RAW command does not work on my SQL. Don't know why, but I'm trying to find a work around.
    DerFarm
    It IS as bad as you think
    and they ARE out to get you

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select coalesce(tbl.column,'') as newcol

    replaces null with zero-length string

    use 0 for numeric fields, i guess


    rudy

  3. #3
    Join Date
    Oct 2002
    Location
    delhi,india
    Posts
    1
    u can check the null value while retreiving the value from a recordset to a variable by iif(isnull(rsAdo("column")),"",rsAdo("column"))

  4. #4
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    A variation of r937's answer:

    SELECT IsNull(Field, 0) AS Field1
    or
    SELECT IsNull(Field, 'unknown') AS Field1
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •