The LA Fox Developer Newsletter

June 2000

ADO Jumpstart (Con’t from page 7)

Andrew Puller Vice President, Sales

Nancy Davolio Sales Representative

Janet Leverling Sales Representative

Margaret Peacock Sales Representative

St even Buchanan Sales Manager

Laura Callahan Inside Sales Coordinator

Steven Buchanan Sales Manager

Michael Suyaaa Sales Representative

Robert King Sales Representative

Anne Dodsworth Sales Representative

Finally, note that hierarchical recordsets are updateable. The

following code expands the previous example to illustrate how to

make a simple update:

Do While !oRecordset.Eof

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

If oStaff.Recordcount> 0

With oRecordset

Do While !oStaff.Eof

With oStaff

.Fields(”firstname”).Value =;

Upper(.Fields(”firstname”).Value)

.Fields(”Iastname”).Vatue =;

Upper(.Fields(”lastname”).Value)

.Fields(”Title”).Value

Upper(.Fields(”Title”).Vatue)

EndWith

oStaff.MoveNext

EndDo

~I Write changes to Staff recordset

oStaff.UpdateBatch

End With

Endif

oRecordset.MoveNext

EndDo

The ability to view related records, coupled with the ability to

make updates, places the ADO hierarchical recordset capability

on par with similar capabilities in Visual FoxPro.

Multiple Recordsets

Use of hierarchical recordsets represents only one method for

returning data from multiple recordsets in one object. For

starters, building hierarchical recordsets is not the most

straightforward of propositions. In many cases, a simpler

alternative may be all that is required.

Consider the case where you need a specific customer record

and the orders for that customer. Yes, you could use a hierar-

chical recordset. But, there is a simpler way: run two SQL

statements.

Some OLE DB providers can process multiple SQL Statements.

The OLE DB Provider for SQL Server has this capability.

Attempting to do this with Visual FoxPro tables via the OLE DB

Provider for ODBC will not work.

When using this technique, you have two choices on where the

logic exists to perform the task.

One choice is to build the SQL on the client and pass it to the

server through a Command object.

The other choice is to invoke a stored procedure on the data-

base server through a Command object. I’ll illustrate both

techniques. The Command object will be discussed in detail

later in this paper.

To illustrate the stored procedure method, the following stored

procedure must be created on the SQL Server Northwind

database:

CREATE PROCEDURE CustomerAndOrders @CustomerlD nchar(5)

AS Select * From Customers Where Customers.CustomerlD =

@CustomerlD

Select * From Orders Where Orders.CustomerlD = @customerlD

With the stored procedure created, the following code will create

the recordset:

#lnclude adovfp.h

oConnection = CreateObject(”adodb.connection”)

oCommand = CreateObject(”adodb.command”)

With oConnection

.Provider = “SQLOLEDB.1”

.ConnectionString ;

“Persist Security lnfo=False;User lDsa;lnitial

Catalog=Northwind;Data Source=JVP”

.Open

EndWith

With oCommand

.Commandlext = “CustomerAndOrders”

.ActiveConnection = oConnection

.CommandType = adCmdStoredProc

End With

oCommand.Parameters(”@CustomerlD”).Value = “ALFKI”

oRecordset = oCommand.Execute

Do While !oRecordset.Eof

?oRecordset.Fields(1).Value

oRecordset.MoveNext

EndDo

oRecordset = oRecordset.NextRecordset

Do While IoRecordset.Eof

?oRecordset.Fields(O).Value

oRecordset.MoveNext

EndDo

ALIREDS FUTTEPKISTR

1.0643

10692

10702

10835

10952

11011

Like any recordset, the recordset just produced can be navi-

gated. Once the first set of records from the Customers table

have been navigated, the NextRecordset method is invoked.

(Con’t, page 9)

Page 8