Difference between revisions of "Microsoft KB Archive/249027"

From BetaArchive Wiki
m (Text replacement - "&" to "&")
m (Text replacement - """ to """)
 
Line 69: Line 69:
 
The second procedure illustrates using re-shaping to achieve the desired results. This method works with any Data Provider.<br />
 
The second procedure illustrates using re-shaping to achieve the desired results. This method works with any Data Provider.<br />
 
<br />
 
<br />
'''WARNING''': USE THE CODE PROVIDED IN THIS ARTICLE AT YOUR OWN RISK. Microsoft provides this code &quot;as is&quot; 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.
+
'''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.
 
<ol>
 
<ol>
 
<li>In Microsoft Visual Basic 5.0 or 6.0, create a Standard EXE project.</li>
 
<li>In Microsoft Visual Basic 5.0 or 6.0, create a Standard EXE project.</li>
Line 81: Line 81:
 
Dim cn As ADODB.Connection, rs As ADODB.Recordset, cmd As ADODB.Command
 
Dim cn As ADODB.Connection, rs As ADODB.Recordset, cmd As ADODB.Command
 
   Set cn = New ADODB.Connection
 
   Set cn = New ADODB.Connection
   cn.Open &quot;Provider=MSDATASHAPE;Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB&quot;
+
   cn.Open "Provider=MSDATASHAPE;Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB"
'  cn.Open &quot;Provider=MSDATASHAPE;Data Provider=SQLOLEDB;Server=MyServer;UID=<User ID>;PWD=<Strong Password>;Database=Northwind&quot;
+
'  cn.Open "Provider=MSDATASHAPE;Data Provider=SQLOLEDB;Server=MyServer;UID=<User ID>;PWD=<Strong Password>;Database=Northwind"
 
   Set cmd = New ADODB.Command
 
   Set cmd = New ADODB.Command
 
   Set cmd.ActiveConnection = cn
 
   Set cmd.ActiveConnection = cn
 
   cmd.CommandType = adCmdText
 
   cmd.CommandType = adCmdText
   cmd.CommandText = &quot;SHAPE {SELECT * FROM Employees WHERE EmployeeID = ?} AS Employees &quot; & _
+
   cmd.CommandText = "SHAPE {SELECT * FROM Employees WHERE EmployeeID = ?} AS Employees " & _
                     &quot;APPEND ({SELECT * FROM Orders WHERE Freight < ?} AS Orders &quot; & _
+
                     "APPEND ({SELECT * FROM Orders WHERE Freight < ?} AS Orders " & _
                     &quot;RELATE EmployeeID TO EmployeeID)&quot;
+
                     "RELATE EmployeeID TO EmployeeID)"
 
'
 
'
 
' Set parameters
 
' Set parameters
Line 116: Line 116:
 
Dim cn As ADODB.Connection, rs As ADODB.Recordset, rsTemp As ADODB.Recordset, cmd As ADODB.Command
 
Dim cn As ADODB.Connection, rs As ADODB.Recordset, rsTemp As ADODB.Recordset, cmd As ADODB.Command
 
   Set cn = New ADODB.Connection
 
   Set cn = New ADODB.Connection
   cn.Open &quot;Provider=MSDATASHAPE;Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB&quot;
+
   cn.Open "Provider=MSDATASHAPE;Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB"
'  cn.Open &quot;Provider=MSDATASHAPE;Data Provider=SQLOLEDB;Server=MyServer;UID=<User ID>;PWD=<Strong Password>;Database=Northwind&quot;
+
'  cn.Open "Provider=MSDATASHAPE;Data Provider=SQLOLEDB;Server=MyServer;UID=<User ID>;PWD=<Strong Password>;Database=Northwind"
 
   Set cmd = New ADODB.Command
 
   Set cmd = New ADODB.Command
 
   Set cmd.ActiveConnection = cn
 
   Set cmd.ActiveConnection = cn
Line 124: Line 124:
 
'
 
'
 
   cmd.CommandType = adCmdText
 
   cmd.CommandType = adCmdText
   cmd.CommandText = &quot;SHAPE {SELECT * FROM Orders WHERE Freight < ?} AS Orders&quot;
+
   cmd.CommandText = "SHAPE {SELECT * FROM Orders WHERE Freight < ?} AS Orders"
 
   cmd.Parameters.Refresh
 
   cmd.Parameters.Refresh
 
   cmd.Parameters(0).Value = 50
 
   cmd.Parameters(0).Value = 50
Line 135: Line 135:
 
   Set cmd = New ADODB.Command
 
   Set cmd = New ADODB.Command
 
   Set cmd.ActiveConnection = cn
 
   Set cmd.ActiveConnection = cn
   cmd.CommandText = &quot;SHAPE {SELECT * FROM Employees WHERE EmployeeID = ?} AS Employees &quot; & _
+
   cmd.CommandText = "SHAPE {SELECT * FROM Employees WHERE EmployeeID = ?} AS Employees " & _
                     &quot;APPEND (Orders As Orders RELATE EmployeeID TO EmployeeID)&quot;
+
                     "APPEND (Orders As Orders RELATE EmployeeID TO EmployeeID)"
 
   cmd.Parameters.Refresh
 
   cmd.Parameters.Refresh
 
   cmd.Parameters(0).Value = 2
 
   cmd.Parameters(0).Value = 2
Line 156: Line 156:
  
 
Private Sub Print_Records(rs As ADODB.Recordset)
 
Private Sub Print_Records(rs As ADODB.Recordset)
   Debug.Print &quot;Recordset has &quot;; rs.RecordCount; &quot; records.&quot;
+
   Debug.Print "Recordset has "; rs.RecordCount; " records."
 
   rs.MoveFirst
 
   rs.MoveFirst
 
   Do While Not rs.EOF
 
   Do While Not rs.EOF

Latest revision as of 13:51, 21 July 2020

Knowledge Base


How To Call a Parameterized Child Command Through the MSDataShape Provider

Article ID: 249027

Article Last Modified on 6/29/2004



APPLIES TO

  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft ActiveX Data Objects 2.7



This article was previously published under Q249027

SUMMARY

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.

MORE INFORMATION

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.

  1. In Microsoft Visual Basic 5.0 or 6.0, create a Standard EXE project.
  2. From the Program menu, choose References and add a reference to the Microsoft ActiveX Data Objects 2.x Library.
  3. 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
                        

    Note You may have to change the connect string to correctly point to the Nwind.mdb database or the SQL Server Northwind database. You must change User ID <User ID> and password = to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

  4. Run the application and then click Command1. The run-time error 3265 occurs.
  5. 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 
      ...          ...            ...
                        
  6. Comment the line that connects with the Microsoft Jet Data Provider and uncomment the one that connects with SQL Server, fix the connect string.
  7. Re-run the program. Both Command1 and Command2 return the expected data correctly.


REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

189657 How To Use the ADO SHAPE Command



Additional query words: Msdatabase

Keywords: kbhowto kbprovider kbdatabase kbjet KB249027