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 hierarchical 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 database 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 navigated. Once the first set of records from the Customers table have been navigated, the NextRecordset method is invoked.

(Con’t, page 9)

Page 8