Depending on the Data Provider, the MSDataShape provider
may only evaluate parameters for the parent statement. Parameters in child
statements may not be evaluated except to create a parameterized SHAPE
statement.
This article describes how to use re-shaping to create a
child recordset from a parameterized statement.
Starting with Microsoft Data Access Components (MDAC) 2.1,
the MSDataShape provider allows re-shaping of recordsets. Therefore, you can
use a parameterized parent statement to retrieve the child statements and then
re-shape the existing records in a second SHAPE statement.
The
following sample application has two procedures. The first illustrates a method
that works with some Data Providers, such as the SQLOLEDB provider, but for
other Data Providers, such as Microsoft.Jet.OLEDB.4.0, the code results in the
following error message:
Run-time error '3265'
Item
cannot be found in the collection corresponding to the requested name or
ordinal.
The second procedure illustrates using re-shaping to
achieve the desired results. This method works with any Data
Provider.
WARNING: USE THE CODE PROVIDED IN THIS ARTICLE AT YOUR OWN RISK.
Microsoft provides this code "as is" without warranty of any kind, either
express or implied, including but not limited to the implied warranties of
merchantability and/or fitness for a particular purpose.
- In Microsoft Visual Basic 5.0 or 6.0, create a Standard
EXE project.
- From the Program menu, choose References and add a reference to the Microsoft ActiveX Data Objects 2.x
Library.
- Add two command buttons (Command1 and Command2) and the
following code to the default form:
Private Sub Command1_Click()
'
' This procedure fails with the Jet Provider.
' It works with the SQl Server Provider.
'
Dim cn As ADODB.Connection, rs As ADODB.Recordset, cmd As ADODB.Command
Set cn = New ADODB.Connection
cn.Open "Provider=MSDATASHAPE;Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB"
' cn.Open "Provider=MSDATASHAPE;Data Provider=SQLOLEDB;Server=MyServer;UID=<User ID>;PWD=<Strong Password>;Database=Northwind"
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.CommandText = "SHAPE {SELECT * FROM Employees WHERE EmployeeID = ?} AS Employees " & _
"APPEND ({SELECT * FROM Orders WHERE Freight < ?} AS Orders " & _
"RELATE EmployeeID TO EmployeeID)"
'
' Set parameters
'
cmd.Parameters.Refresh
cmd.Parameters(0).Value = 2
cmd.Parameters(1).Value = 50 ' Error occurs here with Jet provider
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockReadOnly, adCmdText
'
' Process data
'
Print_Records rs!Orders.Value
'
' Clean up
'
rs.Close
Set cmd = Nothing
cn.Close
End Sub
Private Sub Command2_Click()
'
' This procedure succeeds with all providers
'
Dim cn As ADODB.Connection, rs As ADODB.Recordset, rsTemp As ADODB.Recordset, cmd As ADODB.Command
Set cn = New ADODB.Connection
cn.Open "Provider=MSDATASHAPE;Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB"
' cn.Open "Provider=MSDATASHAPE;Data Provider=SQLOLEDB;Server=MyServer;UID=<User ID>;PWD=<Strong Password>;Database=Northwind"
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
'
' Get Parameterized child records
'
cmd.CommandType = adCmdText
cmd.CommandText = "SHAPE {SELECT * FROM Orders WHERE Freight < ?} AS Orders"
cmd.Parameters.Refresh
cmd.Parameters(0).Value = 50
Set rsTemp = New ADODB.Recordset
rsTemp.CursorLocation = adUseClient
rsTemp.Open cmd, , adOpenStatic, adLockReadOnly
'
' Get parameterized parent records and re-shaped child records
'
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = "SHAPE {SELECT * FROM Employees WHERE EmployeeID = ?} AS Employees " & _
"APPEND (Orders As Orders RELATE EmployeeID TO EmployeeID)"
cmd.Parameters.Refresh
cmd.Parameters(0).Value = 2
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockReadOnly, adCmdText
'
' Process data
'
Print_Records rs!Orders.Value
'
' Clean up
'
rs.Close
rsTemp.Close
Set cmd = Nothing
cn.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!OrderID, rs!CustomerID, rs!Freight
rs.MoveNext
Loop
End Sub
- Run the application and then click Command1. The run-time error 3265 occurs.
- Re-run the program and click Command2. You will get the following results:
Recordset has 49 records.
10339 MEREP 15.66
10280 BERGS 8.98
10295 VINET 1.15
10300 MAGAA 17.68
10307 LONEP 0.56
10312 WANDK 40.26
10313 QUICK 1.96
... ... ...
- Comment the line that connects with the Microsoft Jet Data
Provider and uncomment the one that connects with SQL Server, fix the connect
string.
- Re-run the program. Both Command1 and Command2 return the expected data correctly.
For additional information, click the article
number below to view the article in the Microsoft Knowledge Base:
189657
(http://support.microsoft.com/kb/189657/EN-US/
)
How To Use the ADO SHAPE Command
Back to the top
