Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

PRB: Shaped Recordset Returns Incorrect Number of Records


View products that this article applies to.

This article was previously published under Q249012

↑ Back to the top


Symptoms

When you open two recordsets with the Msdatashape provider, the second recordset has the incorrect number of records.

↑ Back to the top


Cause

You may be inadvertently re-shaping the data in the first recordset.

↑ Back to the top


Resolution

Change the second SHAPE statement to be within curly brackets { }.

↑ Back to the top


Status

This behavior is by design.

↑ Back to the top


More information

The Msdatashape provider, that ships with Microsoft Data Access Components 2.1 and later, allows the results of a previous Shape statement to be re-shaped. This is often useful when you want to see all the child records at once without having to access them through the parent records.

To re-shape a recordset, specify the Alias name in the new statement without using curly brackets.

Steps to Reproduce Behavior

  1. Using Visual Basic 5.0 or 6.0, create a new Standard EXE project.
  2. Use the Project and References menu to add a reference to the following type library:
    Microsoft ActiveX Data Objects 2.1 Library
  3. Add a Command button (Command1) and the following code to the default form:
    Option Explicit
    
    Private Sub Command1_Click()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, rs2 As ADODB.Recordset
      Set cn = New ADODB.Connection
      cn.Open "Provider=MSDATASHAPE;Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB"
      Set rs = New ADODB.Recordset
      rs.CursorLocation = adUseClient
      rs.Open "SHAPE {SELECT * FROM Employees WHERE EmployeeID < 5} AS Employees " & _
              "APPEND ({SELECT * FROM Orders} RELATE EmployeeID TO EmployeeID)", _
              cn, adOpenStatic, adLockReadOnly, adCmdText
      Print_Records rs
      Set rs2 = New ADODB.Recordset
      rs2.Open "SHAPE Employees", cn, adOpenStatic, adLockReadOnly, adCmdText
      Print_Records rs2
      rs.Close
      rs2.Close
    End Sub
    
    Private Sub Print_Records(rs As ADODB.Recordset)
      Debug.Print "Recordset has "; rs.RecordCount; " records."
      rs.MoveFirst
      Do While Not rs.EOF
        Debug.Print rs(0), rs(1), rs(2)
        rs.MoveNext
      Loop
    End Sub
    						
    NOTE: You may have to change the Connect string to correctly point to the Nwind.mdb file.
  4. Run the application and click Command. You see the following results:
    Recordset has  4  records.
     1            Davolio       Nancy
     2            Fuller        Andrew
     3            Leverling     Janet
     4            Peacock       Margaret
    Recordset has  4  records.
     1            Davolio       Nancy
     2            Fuller        Andrew
     3            Leverling     Janet
     4            Peacock       Margaret
    					
  5. In order to return all of the employees to the second recordset, change the second Shape statement to:
      rs2.Open "SHAPE {SELECT * FROM Employees}", cn, adOpenStatic, adLockReadOnly, adCmdText
    					
  6. Re-run the application. The following output is produced:
    Recordset has  4  records.
     1            Davolio       Nancy
     2            Fuller        Andrew
     3            Leverling     Janet
     4            Peacock       Margaret
    Recordset has  9  records.
     1            Davolio       Nancy
     2            Fuller        Andrew
     3            Leverling     Janet
     4            Peacock       Margaret
     5            Buchanan      Steven
     6            Suyama        Michael
     7            King          Robert
     8            Callahan      Laura
     9            Dodsworth     Anne
    					

↑ Back to the top


Keywords: kbdatabase, kbprb, KB249012

↑ Back to the top

Article Info
Article ID : 249012
Revision : 3
Created on : 5/8/2003
Published on : 5/8/2003
Exists online : False
Views : 242