The LA Fox Developer Newsletter

June 2000

ADO Jumpstart (Con’t from page 6)

Because a hierarchy exists, the ability to create drill-down

interfaces becomes a fairly simple task.

The preceding Visual FoxPro code illustrates how to traverse

the hierarchy.

Perhaps you want to use Microsoft Word or Excel as a report-

ing tool. With a combination of Visual FoxPro COM servers,

ADO, and Automation, the process becomes manageable. The

first and third parts of the solution have been around. However,

only now that a set of COM objects exists to handle and work

with data as Visual FoxPro does natively can the solution

become a reality.

Hierarchical Recordsets and Recursive Relationships

One of the nice features of SQL Server, and of most other server

back ends is provision for recursive relations. The following is

the SQL Server 7.0 database diagram for the Northwind data-

base:

In the Northwind database, the Employees table employs

recursion to support a manager/staff relationship. Both manag-

ers and staff are employees. In some cases, it happens that

some employees report to other employees. In Visual FoxPro,

you can create the same sort of relation by opening a table

twice using two different aliases. In ADO, the task is totally

supported and is quite easy to implement. The following is the

Shape syntax:

SHAPE (SELECT * FROM “dbo”.”Employees”} AS Managers APPEND

((SELECT * FROM “dbo”.”Employees”} AS Staff RELATE

‘EmployeelD’ TO ‘ReportsTo’) AS Staff

The following Visual FoxPro code displays a list of managers

and the staff that reports to each manager:

#Include adovfp.h

oRecordset = CreateObject(”adodb.recordset”)

oConnection = CreateObject(”adodb.connection”)

cShpStr = ‘SHAPE (SELECT * FROM “dbo”.”Employees”} AS Manag-

ers

cShpStr = cShpStr + ‘APPEND ((SELECT * FROM “dbo”.”Employees”}

AS Staff’

cShpStr = cShpStr + ‘RELATE “EmployeelD” TO “ReportsTo”) AS

Staff’

With oConnection

.Provider = “MSDataShape”

.ConnectionString = “Data Provider=SQLOLEDB.1 ;Persist Security

Info=False;User IDsa;lnitial CatalogNorthwind;Data Source=JVP”

.Open

EndWith

With oRecordset

.ActiveConnection oConnection

.Source = cShpStr

.CursorType = adOpenStatic

.LockType = adLockBatchOptimistic

.CursorLocation = adUseClient

.Open

End With

Do While !oRecordset.Eof

oStaff = oRecordset.Fields(”staff”).Value

If oStaff.Recordcount> 0

With oRecordset

?.Fields(”firstname”).Value + “ “+;

.Fields(”lastname”).Value,;

.Fields(”Title”).Value

Do While !oStaff.Eof

With oStaff

?Chr(9),;

.Flelds(”firstname”).Value +“ “+;

.Fields(”lastname”).Value,;

.Fields(”Title”).Value

EndWith

oStaff.MoveNext

EndDo

EndWith

Endif

oRecordset.MoveNext

EndDo

The output appears as follows:

(Con’t, page 8)

Page 7