Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 12th, 2005, 05:23 PM
Authorized User
 
Join Date: Jul 2003
Location: Dallas, TX, USA.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to yossarian Send a message via AIM to yossarian Send a message via MSN to yossarian Send a message via Yahoo to yossarian
Default Nesting ADO recordsets

I have a small project at work that requires me to assign an ADO recordset to the field value of another ADO recordset. Both of these recordsets are in-memory recordsets that I create in code. This would seem to be the perfect situation for a hierarchical recordset; but, I don't know how to create one without writing it against a table. Below is some code that I wrote for testing the concept:

Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset

rs1.Fields.Append "Field1", adBSTR, 50, adFldIsNullable
rs1.Fields.Append "Field2", adBSTR, 50, adFldIsNullable
rs1.Fields.Append "FieldRS", adChapter

rs2.Fields.Append "Field1", adBSTR, 50, adFldIsNullable
rs2.Fields.Append "Field2", adBSTR, 50, adFldIsNullable

rs2.Fields(0) = "RS 2 Field 1"
rs2.Fields(1) = "RS 2 Field 2"

rs1.Fields(0) = "RS 1 Field 1"
rs1.Fields(1) = "RS 1 Field 2"
Set rs1.Fields(2).Value = rs2

I get an error on line 3 when I try to create the field to hold the second recordset. I would appreciate any suggestions, tips or help that can be provided. Thanks in advance.

Greg
  #2 (permalink)  
Old January 12th, 2005, 06:28 PM
Authorized User
 
Join Date: Jul 2003
Location: Dallas, TX, USA.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to yossarian Send a message via AIM to yossarian Send a message via MSN to yossarian Send a message via Yahoo to yossarian
Default

I went googling and found a code sample that demonstrates how to create an in-memory ADO hierarchical recordset.

Dim rs As New ADODB.Recordset
Dim rsCh As ADODB.Recordset
Dim rsGrndCh As ADODB.Recordset

         rs.ActiveConnection = "provider=msdatashape;data provider=none;"

         rs.Open " SHAPE APPEND new adInteger As PID, " & _
               " New adVarChar(10) As StudentName, " & _
               "((SHAPE APPEND new adInteger As ChID, " & _
               " New adVarChar(10) As Course, " & _
               "((SHAPE APPEND new adInteger As GrndChID, " & _
               " New adBSTR As Description) RELATE " & _
               " ChID TO GrndChID) As GrandChild) RELATE PID TO ChID) " & _
               "AS Child" , , adOpenStatic, adLockOptimistic

          ' Add a sample record in the parent recordset
          rs.AddNew Array("PID", "StudentName"), Array(1, "Jim Smith")

          ' Now add a two sample child records related to the original
          ' parent's record
          Set rsCh = rs("Child").Value
          For i = 0 To 1
             rsCh.AddNew Array("ChID", "Course"), Array(1, "Course #1" & i)

             ' Now add two sample Grand-child records for each child record
             Set rsGrndCh = rsCh("GrandChild").Value
             For j = 1 To 2
                rsGrndCh.AddNew Array("GrndChID", "Description"), _
                      Array(i, "Description" & Str(j))
             Next
          Next

          Set MSH1.DataSource = rs

          MsgBox "Successfully Done..."

          rs.Close
          rsCh.Close
          rsGrndCh.Close

Greg


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nesting queries??? giffordpinchot Access 0 September 20th, 2006 09:58 AM
Simple Nesting Loop PommyTom XSLT 2 June 9th, 2006 02:16 AM
Creating/Populating Recordsets in ADO dgulliver VB How-To 3 May 24th, 2006 03:31 AM
Subreports nesting svenvandevelde Crystal Reports 2 February 4th, 2004 12:20 PM
Bindind data-bound grids to ADO recordsets module0000 VB Databases Basics 4 June 19th, 2003 11:17 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.