Nulls in ADO recordset How to handle them??


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Nulls in ADO recordset How to handle them??

  1. #1
    JimG Guest

    Nulls in ADO recordset How to handle them??


    Below is part of some v3 XSL code I have to translate an ADO Record Set in
    to a HTML Table. The code below is designed to handle the problem with null
    fields being left out of the XML node tree. What I'm doing is to loop
    through all the fields in the schema and request the corresponding data from
    the z:row collection by field name.

    If I enter
    <TD> <xsl:value-of select=$ThisRow/@Supplier /> </TD>
    Where "Supplier" is one of the fields in the record set, I get the value
    for
    that field.

    But... When I use something like.
    <TD> <xsl:value-of select="$ThisRow/@'@Name'" /> </TD>
    Or
    <TD> <xsl:value-of select="$ThisRow[@Name]" /> </TD>

    Where @Name is the name of the field from the schema I get nothing.

    Help!!! Please I've been at this for 3 days and I'm not getting any where.
    How do I reference an attribute in one node given the name from an other?

    Thanks Jim


    ****************************************************************************
    **************************
    <!-- Prints the rows out -->
    <xsl:template match="//z:row">

    <TR>

    <!-- get a collection of all the fields in the record set -->
    <xsl:variable name="ThisRow" select="." />

    <!-- Loop through all the fields in the schema -->
    <xsl:for-each select="//s:AttributeType">

    <!-- Get the data for the specific field -->
    <TD> <xsl:value-of select="$ThisRow/@'@Name'" /> </TD>

    </xsl:for-each>

    </TR>




  2. #2
    sean Guest

    Re: Nulls in ADO recordset How to handle them??


    check out an article on asptoday bt Michael Corming

    http://www.asptoday.com/articles/20001108.htm

    Sean
    "JimG" <Jgajimg@splusnet.com> wrote:
    >
    >Below is part of some v3 XSL code I have to translate an ADO Record Set

    in
    >to a HTML Table. The code below is designed to handle the problem with null
    >fields being left out of the XML node tree. What I'm doing is to loop
    >through all the fields in the schema and request the corresponding data

    from
    >the z:row collection by field name.
    >
    >If I enter
    > <TD> <xsl:value-of select=$ThisRow/@Supplier /> </TD>
    >Where "Supplier" is one of the fields in the record set, I get the value
    >for
    >that field.
    >
    >But... When I use something like.
    > <TD> <xsl:value-of select="$ThisRow/@'@Name'" /> </TD>
    >Or
    > <TD> <xsl:value-of select="$ThisRow[@Name]" /> </TD>
    >
    >Where @Name is the name of the field from the schema I get nothing.
    >
    >Help!!! Please I've been at this for 3 days and I'm not getting any where.
    >How do I reference an attribute in one node given the name from an other?
    >
    >Thanks Jim
    >
    >
    >****************************************************************************
    >**************************
    ><!-- Prints the rows out -->
    ><xsl:template match="//z:row">
    >
    ><TR>
    >
    ><!-- get a collection of all the fields in the record set -->
    ><xsl:variable name="ThisRow" select="." />
    >
    ><!-- Loop through all the fields in the schema -->
    ><xsl:for-each select="//s:AttributeType">
    >
    > <!-- Get the data for the specific field -->
    > <TD> <xsl:value-of select="$ThisRow/@'@Name'" /> </TD>
    >
    ></xsl:for-each>
    >
    ></TR>
    >
    >
    >



  3. #3
    JimG Guest

    Re: Nulls in ADO recordset How to handle them??


    Hi Sean
    Thanks for the pointer it was very interesting. But... Michael does not address
    the main problem here. Which is how does one deal with NULL's in the recordset.
    When ADO encounters a NULL field it removes the value from the XML steam
    and because of that all the fields to the right are off by one column. I
    ran Michael痴 XSLT file against my data and sure enough all the NULL fields
    are missing and the columns are all out alignment.

    I have just spent over 2 weeks searching a solution for this and I can hardly
    find a reference to the problem, let alone a solution. Every example on how
    to deal with ADO recordsets is done with extremely well behaved data. This
    is not how things are in the real world; Microsoft definitely has its head
    in the sand on this one. I知 having a hard time believing that I知 the only
    sole on earth that has NULLs in his database.

    I知 hoping that, as I new to XSL, I have some how taken a wrong turn here
    and there is some other way to handle this problem. I do not have control
    over the recordset that I知 dealing with so I cannot remove the NULLs.

    More suggestions welcome.

    Thanks Jim

    "sean" <sean@ireland.com> wrote:
    >
    >check out an article on asptoday bt Michael Corming
    >
    >http://www.asptoday.com/articles/20001108.htm
    >
    >Sean
    >"JimG" <Jgajimg@splusnet.com> wrote:
    >>
    >>Below is part of some v3 XSL code I have to translate an ADO Record Set

    >in
    >>to a HTML Table. The code below is designed to handle the problem with

    null
    >>fields being left out of the XML node tree. What I'm doing is to loop
    >>through all the fields in the schema and request the corresponding data

    >from
    >>the z:row collection by field name.
    >>
    >>If I enter
    >> <TD> <xsl:value-of select=$ThisRow/@Supplier /> </TD>
    >>Where "Supplier" is one of the fields in the record set, I get the value
    >>for
    >>that field.
    >>
    >>But... When I use something like.
    >> <TD> <xsl:value-of select="$ThisRow/@'@Name'" /> </TD>
    >>Or
    >> <TD> <xsl:value-of select="$ThisRow[@Name]" /> </TD>
    >>
    >>Where @Name is the name of the field from the schema I get nothing.
    >>
    >>Help!!! Please I've been at this for 3 days and I'm not getting any where.
    >>How do I reference an attribute in one node given the name from an other?
    >>
    >>Thanks Jim
    >>
    >>
    >>****************************************************************************
    >>**************************
    >><!-- Prints the rows out -->
    >><xsl:template match="//z:row">
    >>
    >><TR>
    >>
    >><!-- get a collection of all the fields in the record set -->
    >><xsl:variable name="ThisRow" select="." />
    >>
    >><!-- Loop through all the fields in the schema -->
    >><xsl:for-each select="//s:AttributeType">
    >>
    >> <!-- Get the data for the specific field -->
    >> <TD> <xsl:value-of select="$ThisRow/@'@Name'" /> </TD>
    >>
    >></xsl:for-each>
    >>
    >></TR>
    >>
    >>
    >>

    >



  4. #4
    sean Guest

    Re: Nulls in ADO recordset How to handle them??


    Jim,
    I have solved this problem.
    You basically have to first iterate thru the columns and then the rows as
    follows (pseudo xsl). Its a bit slower but it works.

    1. store the column data in a variable
    <xsl:variable name="columns" select="/xml/s:Schema/s:ElementType/s:AttributeType"
    />

    2. loop thru the rows
    <xsl:for-each select="rs:data/z:row">

    3. store the current rows data in a variable
    <xsl:variable name="data" select="." />

    4. for each column
    <xsl:for-each select="$columns">

    5. outout a table element and get the data element that matches the current
    column name

    <TD>
    <xsl:variable name="value" select="$data/@*[name()=current()/@name]"/>
    </TD>

    Hope this helps
    Feel free to e-mail me off-group if you require more details.
    Sean

    "JimG" <jgajimg@splusnet.com> wrote:
    >
    >Hi Sean
    >Thanks for the pointer it was very interesting. But... Michael does not

    address
    >the main problem here. Which is how does one deal with NULL's in the recordset.
    >When ADO encounters a NULL field it removes the value from the XML steam
    >and because of that all the fields to the right are off by one column. I
    >ran Michael痴 XSLT file against my data and sure enough all the NULL fields
    >are missing and the columns are all out alignment.
    >
    >I have just spent over 2 weeks searching a solution for this and I can hardly
    >find a reference to the problem, let alone a solution. Every example on

    how
    >to deal with ADO recordsets is done with extremely well behaved data. This
    >is not how things are in the real world; Microsoft definitely has its head
    >in the sand on this one. I知 having a hard time believing that I知 the only
    >sole on earth that has NULLs in his database.
    >
    >I知 hoping that, as I new to XSL, I have some how taken a wrong turn here
    >and there is some other way to handle this problem. I do not have control
    >over the recordset that I知 dealing with so I cannot remove the NULLs.
    >
    >More suggestions welcome.
    >
    >Thanks Jim
    >
    >"sean" <sean@ireland.com> wrote:
    >>
    >>check out an article on asptoday bt Michael Corming
    >>
    >>http://www.asptoday.com/articles/20001108.htm
    >>
    >>Sean
    >>"JimG" <Jgajimg@splusnet.com> wrote:
    >>>
    >>>Below is part of some v3 XSL code I have to translate an ADO Record Set

    >>in
    >>>to a HTML Table. The code below is designed to handle the problem with

    >null
    >>>fields being left out of the XML node tree. What I'm doing is to loop
    >>>through all the fields in the schema and request the corresponding data

    >>from
    >>>the z:row collection by field name.
    >>>
    >>>If I enter
    >>> <TD> <xsl:value-of select=$ThisRow/@Supplier /> </TD>
    >>>Where "Supplier" is one of the fields in the record set, I get the value
    >>>for
    >>>that field.
    >>>
    >>>But... When I use something like.
    >>> <TD> <xsl:value-of select="$ThisRow/@'@Name'" /> </TD>
    >>>Or
    >>> <TD> <xsl:value-of select="$ThisRow[@Name]" /> </TD>
    >>>
    >>>Where @Name is the name of the field from the schema I get nothing.
    >>>
    >>>Help!!! Please I've been at this for 3 days and I'm not getting any where.
    >>>How do I reference an attribute in one node given the name from an other?
    >>>
    >>>Thanks Jim
    >>>
    >>>
    >>>****************************************************************************
    >>>**************************
    >>><!-- Prints the rows out -->
    >>><xsl:template match="//z:row">
    >>>
    >>><TR>
    >>>
    >>><!-- get a collection of all the fields in the record set -->
    >>><xsl:variable name="ThisRow" select="." />
    >>>
    >>><!-- Loop through all the fields in the schema -->
    >>><xsl:for-each select="//s:AttributeType">
    >>>
    >>> <!-- Get the data for the specific field -->
    >>> <TD> <xsl:value-of select="$ThisRow/@'@Name'" /> </TD>
    >>>
    >>></xsl:for-each>
    >>>
    >>></TR>
    >>>
    >>>
    >>>

    >>

    >



  5. #5
    Jmg Guest

    Re: Nulls in ADO recordset How to handle them??


    Sean you are the Man!!!! It was the

    @*[name()=current()/@name]

    that I was missing. I would have never figured it out. I assume that the
    following is equlivent.

    @*[name()=(current()/@name)]

    Again thanks

    Jim

    "sean" <sean@ireland.com> wrote:
    >
    >Jim,
    >I have solved this problem.
    >You basically have to first iterate thru the columns and then the rows as
    >follows (pseudo xsl). Its a bit slower but it works.
    >
    >1. store the column data in a variable
    ><xsl:variable name="columns" select="/xml/s:Schema/s:ElementType/s:AttributeType"
    >/>
    >
    >2. loop thru the rows
    ><xsl:for-each select="rs:data/z:row">
    >
    >3. store the current rows data in a variable
    ><xsl:variable name="data" select="." />
    >
    >4. for each column
    ><xsl:for-each select="$columns">
    >
    >5. outout a table element and get the data element that matches the current
    >column name
    >
    ><TD>
    ><xsl:variable name="value" select="$data/@*[name()=current()/@name]"/>
    ></TD>
    >
    >Hope this helps
    >Feel free to e-mail me off-group if you require more details.
    >Sean
    >
    >"JimG" <jgajimg@splusnet.com> wrote:
    >>
    >>Hi Sean
    >>Thanks for the pointer it was very interesting. But... Michael does not

    >address
    >>the main problem here. Which is how does one deal with NULL's in the recordset.
    >>When ADO encounters a NULL field it removes the value from the XML steam
    >>and because of that all the fields to the right are off by one column.

    I
    >>ran Michael痴 XSLT file against my data and sure enough all the NULL fields
    >>are missing and the columns are all out alignment.
    >>
    >>I have just spent over 2 weeks searching a solution for this and I can

    hardly
    >>find a reference to the problem, let alone a solution. Every example on

    >how
    >>to deal with ADO recordsets is done with extremely well behaved data. This
    >>is not how things are in the real world; Microsoft definitely has its head
    >>in the sand on this one. I知 having a hard time believing that I知 the

    only
    >>sole on earth that has NULLs in his database.
    >>
    >>I知 hoping that, as I new to XSL, I have some how taken a wrong turn here
    >>and there is some other way to handle this problem. I do not have control
    >>over the recordset that I知 dealing with so I cannot remove the NULLs.
    >>
    >>More suggestions welcome.
    >>
    >>Thanks Jim
    >>
    >>"sean" <sean@ireland.com> wrote:
    >>>
    >>>check out an article on asptoday bt Michael Corming
    >>>
    >>>http://www.asptoday.com/articles/20001108.htm
    >>>
    >>>Sean
    >>>"JimG" <Jgajimg@splusnet.com> wrote:
    >>>>
    >>>>Below is part of some v3 XSL code I have to translate an ADO Record Set
    >>>in
    >>>>to a HTML Table. The code below is designed to handle the problem with

    >>null
    >>>>fields being left out of the XML node tree. What I'm doing is to loop
    >>>>through all the fields in the schema and request the corresponding data
    >>>from
    >>>>the z:row collection by field name.
    >>>>
    >>>>If I enter
    >>>> <TD> <xsl:value-of select=$ThisRow/@Supplier /> </TD>
    >>>>Where "Supplier" is one of the fields in the record set, I get the value
    >>>>for
    >>>>that field.
    >>>>
    >>>>But... When I use something like.
    >>>> <TD> <xsl:value-of select="$ThisRow/@'@Name'" /> </TD>
    >>>>Or
    >>>> <TD> <xsl:value-of select="$ThisRow[@Name]" /> </TD>
    >>>>
    >>>>Where @Name is the name of the field from the schema I get nothing.
    >>>>
    >>>>Help!!! Please I've been at this for 3 days and I'm not getting any where.
    >>>>How do I reference an attribute in one node given the name from an other?
    >>>>
    >>>>Thanks Jim
    >>>>
    >>>>
    >>>>****************************************************************************
    >>>>**************************
    >>>><!-- Prints the rows out -->
    >>>><xsl:template match="//z:row">
    >>>>
    >>>><TR>
    >>>>
    >>>><!-- get a collection of all the fields in the record set -->
    >>>><xsl:variable name="ThisRow" select="." />
    >>>>
    >>>><!-- Loop through all the fields in the schema -->
    >>>><xsl:for-each select="//s:AttributeType">
    >>>>
    >>>> <!-- Get the data for the specific field -->
    >>>> <TD> <xsl:value-of select="$ThisRow/@'@Name'" /> </TD>
    >>>>
    >>>></xsl:for-each>
    >>>>
    >>>></TR>
    >>>>
    >>>>
    >>>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center