PRB: Shaped Recordset Returns Incorrect Number of Records
ID: Q249012
|
The information in this article applies to:
-
ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2, 2.5
-
Microsoft Data Access Components versions 2.1, 2.1 (GA), 2.1 SP1, 2.1 SP2, 2.5
SYMPTOMS
When you open two recordsets with the Msdatashape provider, the second recordset has the incorrect number of records.
CAUSE
You may be inadvertently re-shaping the data in the first recordset.
RESOLUTION
Change the second SHAPE statement to be within curly brackets { }.
STATUS
This behavior is by design.
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
-
Using Visual Basic 5.0 or 6.0, create a new Standard EXE project.
-
Use the Project and References menu to add a reference to the following type library:
Microsoft ActiveX Data Objects 2.1 Library
-
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.
-
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
-
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
-
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
© Microsoft Corporation 1999, All Rights Reserved. Contributions by Malcolm Stewart, Microsoft Corporation
Additional query words:
kbdsupport kbgrpmdac kbgrpvbdb
Keywords : kbADO kbDatabase kbOLEDB kbGrpVBDB kbGrpMDAC kbDSupport kbMDAC250
Version : WINDOWS:2.1,2.1 (GA),2.1 SP1,2.1 SP2,2.5
Platform : WINDOWS
Issue type : kbprb
|