PRB: Chaptered Recordset from Parameterized Query Not Saved

• ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2

SYMPTOMS

If you try to save a hierarchical recordset generated by a parameterized query to a file, the following error appears:

An unknown error has occurred.

STATUS

Saving a hierarchical recordset generated by a parameterized query is not supported. This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start Visual Basic and from the File menu, select New Project.



2. Set a reference to the Microsoft ActiveX Data Objects Library.



3. Paste the following code in the GENERAL section of the form:




      Const strFileName As String = "C:\Orders.rst"

        Private Sub Form_Load()
        Dim cnNWindSQL As New ADODB.Connection
        Dim rsCustomers As New ADODB.Recordset
        Dim rsOrders As ADODB.Recordset
        Dim strConn As String
        Dim strSQL As String

        On Error Resume Next
          Kill strFileName
        On Error GoTo 0

        strConn = "Provider=MSDataShape;" & _
                  "Data Provider=SQLOLEDB;" & _
                  "Data Source=scep;" & _
                  "Initial Catalog=NWindSQL;"

        cnNWindSQL.CursorLocation = adUseClient
        cnNWindSQL.Open strConn, "sa", ""

        If MsgBox("Use parameterized shape?", vbYesNo) = vbYes Then
          strSQL = "SHAPE {SELECT * FROM Customers} " & _
                   "AS Customers APPEND " & _
                   "({SELECT * FROM Orders WHERE CustomerID = ?} " & _
                   "AS Orders RELATE CustomerID TO PARAMETER 0) " & _
                   "AS Orders"
        Else
          strSQL = "SHAPE {SELECT * FROM Customers} AS Customers " & _
                   "APPEND ({SELECT * FROM Orders} AS Orders " & _
                   "RELATE CustomerID TO CustomerID) AS Orders"
        End If

        With rsCustomers
          .Open strSQL, cnNWindSQL, adOpenStatic, _
               adLockReadOnly, adCmdText
          Set rsOrders = !Orders.Value

          If MsgBox("Retrieve all chapters?", vbYesNo) = vbYes Then
              While Not .EOF
                  Debug.Print !CustomerID & " has " _
                              & rsOrders.RecordCount & " orders."
                  .MoveNext
              Wend
          End If
          .Save strFileName
         .Close
        End With

       Set rsCustomers = Nothing
       cnNWindSQL.Close
       Set cnNWindSQL = Nothing

      End Sub 

4. Run the code.



5. If you choose to execute the parameterized SHAPE command and try to save it, the following error appears:




An unknown error has occurred.

6. If you choose not to execute the parameterized SHAPE command, you can save the recordset to the C:\Orders.rst file.

REFERENCES

For additional information about SHAPE command, please see the following article in the Microsoft Knowledge Base:

Q189657 HOWTO: Use the ADO SHAPE Command

Additional query words:

Keywords : kbADO kbADO200bug kbprb
Version : WINDOWS:2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbprb