SHAPE with ADO and Recordsets - unable to see data in 2nd child

From: Mike UK (anonymous_at_discussions.microsoft.com)
Date: 05/27/04 

Date: Thu, 27 May 2004 07:36:11 -0700

Hi All, hope someone can help me,
 I am creating a shape command using ADO with the following structure. I can see the
Parent level and the first child level but when I try to see the data in the grand child (rstChild2)
the resultset tells me its .EOF I have triple checked and the data exists.

'' RowId
'' Site
'' rsB < -- I get all this data ok
'' RowId
'' SiteRowId
'' ShortDesc
'' Description
'' rsFloor <--- not picking up data from
'' RowId this level altho there are rows in db
'' BldgRowId
'' Description
The above structure is gathered from getting the rs Type (adChapter) from the Recordset using:-
For I = 0 To rs.Fields.Count - 1
    LogText Space$(Level * 3) & rs(I).Name
    If rs(I).Type = adChapter Then
      ListChapteredFields rs(I).Value, Level + 1
    End If
  Next I

I have tried pasing the recordset to a text box, MSHFlexgrid, debug etc and i can never get to use rs floor
I am using the following code to spit it out:-

  strCn = "PROVIDER=MSDataShape;DATA PROVIDER=SQLOLEDB;" & _
            "SERVER=LONMIK01\SQL_DEV;DATABASE=AssetPlusV2;Trusted_Connection=yes"

    strSh = "SHAPE {Select RowId, ShortDesc as Site from Site } as Site " & _
            "APPEND ({SELECT * " & _
                    " from Building} AS rsB " & _
            " RELATE RowId to SiteRowId), " & _
            " ({SELECT * " & _
            " from Floor } " & _
            " RELATE RowId to BldgRowId) as rsFloor"

    '' above gives 3 layers of resultsets
            
    '----- create rowsets
    Set rstParent = New ADODB.Recordset
    rstParent.Open strSh, strCn
 

    '----- process parent rowset
    Do While Not rstParent.EOF
        
        sBuf = sBuf & rstParent("Site") & vbCrLf
            
        '----- process chapter of 1st child rowset
        Set rstChild1 = rstParent("rsb").Value ' was rsB
        Do While Not rstChild1.EOF
            'Building short desc & Description
            sBuf = sBuf & vbTab & vbTab & rstChild1("ShortDesc") & vbTab & rstChild1("Description") & vbCrLf
      
            ''heres where we will put in the 3rd level of hierarchy
            Set rstChild2 = rstParent("rsFloor").Value
            Do While Not rstChild2.EOF '<<- this resultset is always .eof
                sBuf = sBuf & vbTab & vbTab & rstChild2("Description") & vbCrLf
                rstChild2.MoveNext
            Loop
            rstChild1.MoveNext
        Loop
        rstParent.MoveNext
    Loop
    
Does anyone have anyclues why my rs is not showing me the data in my grandchild (rstChild2)
NB excuse the slack coding b ut I am just doing a few proof of concepts for a project, just cant get this to go!
thanks in advance,
M.