Microsoft KB Archive/251329: Difference between revisions
m (Text replacement - "&" to "&") |
m (Text replacement - """ to """) |
||
Line 70: | Line 70: | ||
<br /> | <br /> | ||
In the sample program provided in this article, the command is modified to show the following: | In the sample program provided in this article, the command is modified to show the following: | ||
* All orders where the CustomerID starts with the letter | * All orders where the CustomerID starts with the letter "A".<br /> | ||
<br /> | <br /> | ||
Line 146: | Line 146: | ||
<pre class="codesample">Option Explicit | <pre class="codesample">Option Explicit | ||
Const preSHAPE = | Const preSHAPE = "SHAPE {" | ||
Const SQL = | Const SQL = "SELECT Customers.CompanyName, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID" | ||
Const postSHAPE = | Const postSHAPE = "} AS Command1 COMPUTE Command1, ANY(Command1.'CompanyName') AS Company BY 'CustomerID'" | ||
Private Sub Command1_Click() | Private Sub Command1_Click() | ||
Line 157: | Line 157: | ||
With DataEnvironment1 | With DataEnvironment1 | ||
If .rsCustomer.State Then .rsCustomer.Close | If .rsCustomer.State Then .rsCustomer.Close | ||
.Commands!Customer.CommandText = preSHAPE & SQL & | .Commands!Customer.CommandText = preSHAPE & SQL & " WHERE Customers.CustomerID LIKE 'A%'" & postSHAPE | ||
.Customer | .Customer | ||
End With | End With | ||
Line 171: | Line 171: | ||
With DataEnvironment1 | With DataEnvironment1 | ||
If .rsCustomer.State Then .rsCustomer.Close | If .rsCustomer.State Then .rsCustomer.Close | ||
.Commands!Customer.CommandText = preSHAPE & SQL & | .Commands!Customer.CommandText = preSHAPE & SQL & " WHERE ShipVia=1" & postSHAPE | ||
.Customer | .Customer | ||
End With | End With | ||
Line 185: | Line 185: | ||
With DataEnvironment1 | With DataEnvironment1 | ||
If .rsCustomer.State Then .rsCustomer.Close | If .rsCustomer.State Then .rsCustomer.Close | ||
.Commands!Customer.CommandText = preSHAPE & SQL & | .Commands!Customer.CommandText = preSHAPE & SQL & " WHERE Year(OrderDate)=1996" & postSHAPE | ||
.Customer | .Customer | ||
End With | End With | ||
Line 193: | Line 193: | ||
Private Sub Form_Load() | Private Sub Form_Load() | ||
Command1.Caption = | Command1.Caption = "CustomerID starts with 'A'" | ||
Command2.Caption = | Command2.Caption = "Orders via Shipper #1" | ||
Command3.Caption = | Command3.Caption = "1996 Orders" | ||
End Sub | End Sub | ||
</pre> | </pre> | ||
<p>You may have to modify the '''SHAPE''' statement in the code based on your hierarchical command. You can copy the Command1 '''SHAPE''' statement by right-clicking it in the DataEnvironment and then select '''Hierarchy Info'''. You can paste the '''SHAPE''' statement in the code and use it to modify the preSHAPE, postSHAPE, and SQL constants. The DataEnvironment Command and Recordset names used in the preceding code, in this case | <p>You may have to modify the '''SHAPE''' statement in the code based on your hierarchical command. You can copy the Command1 '''SHAPE''' statement by right-clicking it in the DataEnvironment and then select '''Hierarchy Info'''. You can paste the '''SHAPE''' statement in the code and use it to modify the preSHAPE, postSHAPE, and SQL constants. The DataEnvironment Command and Recordset names used in the preceding code, in this case "Customer" and "rsCustomer", match the name that displays in the top level of the hierarchy.<br /> | ||
<br /> | <br /> | ||
</p></li> | </p></li> | ||
<li>Save the project and run it. Click each of the command buttons. You see the '''DataReport''' show data based on the modified command text, namely: | <li>Save the project and run it. Click each of the command buttons. You see the '''DataReport''' show data based on the modified command text, namely: | ||
<ol style="list-style-type: lower-alpha;"> | <ol style="list-style-type: lower-alpha;"> | ||
<li>Orders where the CustomerID starts with | <li>Orders where the CustomerID starts with "A".</li> | ||
<li>Orders shipped by shipper #1.</li> | <li>Orders shipped by shipper #1.</li> | ||
<li>Orders placed in 1996.</li></ol> | <li>Orders placed in 1996.</li></ol> |
Latest revision as of 13:51, 21 July 2020
Article ID: 251329
Article Last Modified on 7/1/2004
APPLIES TO
- Microsoft Visual Basic 6.0 Professional Edition
- Microsoft Visual Basic 6.0 Enterprise Edition
- 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 Q251329
SUMMARY
This article demonstrates how to base a DataReport on a hierarchical DataEnvironment and refresh the report after changing the command text.
MORE INFORMATION
The following Microsoft Knowledge Base article illustrates how to refresh a DataReport when re-executing a parameterized command:
244779 How To Refresh a Parameterized DataReport
This article demonstrates a similar technique, which modifies the command itself.
NOTE: Any modification to the command cannot change the shape of the hierarchy or the names of the fields.
In the sample program provided in this article, the command is modified to show the following:
- All orders where the CustomerID starts with the letter "A".
- All orders shipped through shipper #1.
- All orders placed in 1996.
To create the sample, use these steps:
- Open a new Standard EXE Project in Microsoft Visual Basic. Form1 is created by default.
- Add a DataEnvironment (DataEnvironment1) and a DataReport (DataReport1) to the project.
- Add a Connection (Connection1) to the DataEnvironment that points to the SQL Server Northwind database using the OLE DB Provider for SQL Server.
- Add a Command (Command1) to Connection1. Right-click the command button and then select Properties.
- In the Properties dialog box, click the General tab and add the following:
SQL Statement: SELECT Customers.CompanyName, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
- In the Properties dialog box, click the Grouping tab and set the following:
Group Command Object: (select this item)
Grouping Command Name: Customer
Fields Used for Grouping: CustomerID - In the Properties dialog box, click the Aggregates tab. Click Add and then edit the following properties:
Name: Company
Function: Any
Aggregate On: Grouping
Field: CompanyName Click OK to close the Properties dialog box. The hierarchy changes to the following:
Command1 grouped using Customer Summary fields in Customer CustomerID Company Detail fields in Command1 CompanyName OrderID CustomerID EmployeeID ...
- In the DataReport, set the following properties:
DataSource: DataEnvironment1
DataMember: Customer - Right-click the body of the report, and then select the Insert Group Header/Footer menu item.
- Drag the following fields from the DataEnvironment into the Detail section of the DataReport:
OrderID
CustomerID
OrderDate
ShipViaDrag the following fields from the DataEnvironment into the Group Header section of the DataReport:
CustomerID
Company Add three command buttons (Command1, Command2, Command3) and the following code to the form:
Option Explicit Const preSHAPE = "SHAPE {" Const SQL = "SELECT Customers.CompanyName, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID" Const postSHAPE = "} AS Command1 COMPUTE Command1, ANY(Command1.'CompanyName') AS Company BY 'CustomerID'" Private Sub Command1_Click() ' ' Selects orders with CustomerID starting with A. ' Load DataEnvironment1 With DataEnvironment1 If .rsCustomer.State Then .rsCustomer.Close .Commands!Customer.CommandText = preSHAPE & SQL & " WHERE Customers.CustomerID LIKE 'A%'" & postSHAPE .Customer End With DataReport1.Refresh If DataReport1.Visible = False Then DataReport1.Show End Sub Private Sub Command2_Click() ' ' Selects orders shipped via Shipper #1 ' Load DataEnvironment1 With DataEnvironment1 If .rsCustomer.State Then .rsCustomer.Close .Commands!Customer.CommandText = preSHAPE & SQL & " WHERE ShipVia=1" & postSHAPE .Customer End With DataReport1.Refresh If DataReport1.Visible = False Then DataReport1.Show End Sub Private Sub Command3_Click() ' ' Selects orders in 1996 ' Load DataEnvironment1 With DataEnvironment1 If .rsCustomer.State Then .rsCustomer.Close .Commands!Customer.CommandText = preSHAPE & SQL & " WHERE Year(OrderDate)=1996" & postSHAPE .Customer End With DataReport1.Refresh If DataReport1.Visible = False Then DataReport1.Show End Sub Private Sub Form_Load() Command1.Caption = "CustomerID starts with 'A'" Command2.Caption = "Orders via Shipper #1" Command3.Caption = "1996 Orders" End Sub
You may have to modify the SHAPE statement in the code based on your hierarchical command. You can copy the Command1 SHAPE statement by right-clicking it in the DataEnvironment and then select Hierarchy Info. You can paste the SHAPE statement in the code and use it to modify the preSHAPE, postSHAPE, and SQL constants. The DataEnvironment Command and Recordset names used in the preceding code, in this case "Customer" and "rsCustomer", match the name that displays in the top level of the hierarchy.
- Save the project and run it. Click each of the command buttons. You see the DataReport show data based on the modified command text, namely:
- Orders where the CustomerID starts with "A".
- Orders shipped by shipper #1.
- Orders placed in 1996.
REFERENCES
For additional information regarding the SHAPE syntax, click the article numbers below to view the articles in the Microsoft Knowledge Base:
189657 How To Use the ADO SHAPE Command
185425 INFO ADO Hierarchical Recordsets via SHAPE APPEND w/C++/VBA/Java
249097 How To Re-Shape a Hierarchical Recordset
247662 How To Access a Secured Jet Database w/ the MSDataShape Provider
Keywords: kbhowto kbdatabase KB251329