PRB: Chaptered Recordset from Parameterized Query Not Saved

• ActiveX Data Objects (ADO), version 2.0

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 2.0 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

1. Connect to Support Online at the following Internet address:




http://support.microsoft.com/support/default.asp

2. In the My Question Is About box, select All Products.



3. In the My Question Is box, type the Article ID (qnumber) of the article that you want to see. For example, type "Q162192" (without the quotation marks).



4. Click Find (next to the My Question Is box). This search will return from one to several article titles. The Article ID appears in the upper-left corner of the article. You can see the Article ID only after you click to view the article.



5. Click the title of the article to view it.

Additional query words:

Keywords          : kbADO200 kbADO200bug kbprb 
Version           : WINDOWS:2.0
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: July 13, 1999