Opening a Shaped Recordset

A very useful feature of ADO is that you can create shaped recordsets. Data shaping allows you to define the columns of a recordset, the relationships between them, and the manner in which the recordset is populated with data. These columns can contain data from a provider, such as Access or the SQL Server, references to another recordset, values derived from a calculation on a row, and so on.

Let's take a simple example. In the NorthwindCS sample database (an ADP), there are two tables, organized into a parent-child relationship: Orders, which contains the header information for customer orders, and Order Details, which contains the individual line items for each order. Figure 7-4 shows this relationship at work.

H Orders : Table

Order ID 1 .Customer

Employee i

Order Date

.0246 Wilman Kali

Buchanan, Steven

04-Jul-1996

im

Product

I Unit Price I

Quantity |

Discount I

¡Queso Cabrales^^^^^mH^B^

Í14.00

12

0%^

Singaporean Hokkien Frieci Mee

$9: as

ÍÜ

m

Mozzarella di Giovanni

$34 80

5

my

ÏCi.GC

1

og

i

10249 Tracûçâo Hipermercados_

Suyama, Michael

05-Jul-1996

+

10250| Hanari Carnes

Peacock, Margaret

OS-Jul-1996 -H

Record:

H ! - I. ► ! H I Mí J of 3

Let's say you wanted to populate a list with the details of a select set of orders. In the past, you would have created a recordset based on a query very much like the following:

SELECT O.OrderlD, O.CustomerlD, O.OrderDate,

D.ProductID, D.UnitPrice, D.Quantity,

D.Discount

FROM Orders As O

INNER JOIN [Order Details] As D ON D.OrderlD

= O.OrderID

WHERE Year(O.OrderDate) = 19 9 6

AND OrderlD BETWEEN 10248 AND 10250

ORDER BY O.OrderDate DESC

The above query returns a dataset that contains all the orders in 1996 where the OrderlD is between 10248 and 10250, in descending date order. Such a recordset would return the following rows (Figure 7-5).

¡Í? Queryl : S

elect Query

X

Order ID

| Customer

Order Date

I Product

Unit Price

| Quantity

Discount

10250 Hanari Cames

□S-Jul-1996 Louisiana Fiery Hot Pepper Sauce

116.80

15

15%

10250

Hanari Cames

08-Jul-1996

Manjimup Dried Apples

142.40

35

15%

10250

Hanari Cames

08-Jul-1996

Jack's New England Clam Chowder

$7.70

10

0%

10249

Tradigao Hipermercados

05-Jul-1996

Manjimup Dried Apples

$42.40

40

0%

10249

Tradigao Hipermercados

05-Jul-1996

Tofu

$18.60

9

0%

10248

Wilman Kala

04-Jul-1996

Mozzarella di Giovanni

$34.80

5

0%

10248

Wilman Kala

04-Jul-1996

Singaporean Hokkien Fried Mee

$9.80

10

0%

10248

Wilman Kala

04-Jul-1996

Queso Cabrales

$14.00

12

0%

d

1 Record: H I

II 1 H ►■!►*! °f

S

You'll notice that the columns from the Orders table (OrderlD, CustomerlD, and OrderDate) are repeated unnecessarily for every row of data returned from the Order Details table. Wouldn't it be nice if you could return only one row of Orders data for each group of related rows from Order Details? Closely examine the following ADO code, paying particular attention to the SQL statement:

Dim cn As New ADODB.Connection

Dim rsOrders As New ADODB.Recordset

Dim rsDetails As New ADODB.Recordset

Dim strSQL As String

'Define and create the connection

cn.CursorLocation = adUseClient

'We have to use this provider

cn.Provider = "MSDataShape"

'Open a connection to SQL Server

cn.Open "Data Provider=SQLOLEDB;" & _

"Integrated Security=SSPI;Database=NorthwindCS"

Now we need to create the SQL statement that will do all

the work.

'Create the SQL statement that does all the work

strSQL = "SHAPE {SELECT DISTINCT OrderID," & _

"CustomerID, OrderDate" &

"FROM Orders" & _

"WHERE Year(OrderDate) = 1996" & _

"AND OrderID BETWEEN 10248 AND 10250" & _

"ORDER BY OrderDate DESC}" & _

"APPEND ({SELECT OrderID, ProductID, UnitPrice,"

& _

"Quantity, Discount" & _

"FROM [Order Details]}" & _

"RELATE OrderID TO OrderID)"

Once the SQL statement is formed, open a recordset based

on it.

'Create the recordset for the orders table

rsOrders.Open strSQL, cn

Do While Not rsOrders.EOF

'Print out the header rows, one at a time

Debug.Print rsOrders!OrderID, _

rsOrders!CustomerID, _

rsOrders!OrderDate

Now return the child records in a second recordset. If you examine the above SQL statement, you'll see that the child dataset is aliased as Details. The Details column is actually a reference to a child recordset.

'Create the child recordset

Set rsDetails = rsOrders("Details").Value

Do While Not rsDetails.EOF

'Print out the child records, one at a time Debug.Print vbTab & rsDetails!ProductID, _ rsDetails!UnitPrice, _ rsDetails!Quantity, _ rsDetails!Discount rsDetails.MoveNext

Loop rsOrders.MoveNext

Loop

This is what would be returned by the above code. The obvious difference between the data returned here and what you see in Figure 7-4 is because the CustomerlD and ProductID columns are designed using Access lookups, and although Access displays the lookup values, the provider used to shape the recordsets does not.

10250

HANAR

07/08/1996

41

7.7

10

0

51

42.4

35

0.15

65

16.8

15

0.15

10249

TOMSP

07/05/1996

14

18.6

9

0

51

42.4

40

0

10248

VINET

07/04/1996

11

14

12

0

42

9.8

10

0

72

34.8

5

0

When you return the value of a column that contains a reference to another recordset, ADO returns an actual recordset represented by that reference. Recordsets such as this are called hierarchical recordsets. Hierarchical recordsets exhibit a parent-child relationship, in which the parent is the container recordset and the child is the contained recordset. The reference to the child recordset is actually a reference to a subset of the child, called a Chapter. A single parent may reference more than one child recordset.

The Shape statement allows you to create a shaped recordset, which you can then access programmatically or through a visual control. You can issue the Shape statement like any other ADO command text.

This simple example demonstrates only a fraction of what can be accomplished using the Shape statement. Unfortunately, an in-depth examination of SQL is outside the scope of this book; refer to the Access help for more information.

0 0

Post a comment