The LA, Fox Developer Newsletter

May 2000

ADO J urn psta rt (Con't from page 4)

Cancel local changes

Marshall local changes to the server

Save (persist) the recordset locally.

You can save (persist) the recordset locally for both later use

and, ultimately, for marshalling those persisted changes back to

the server.

The first choice is pretty simple to implement, since it takes

one line of code:

oRecordset.Cancel Batch

The second choice is also simple to implement. Much of the

work in updating multiple records and transactions has already

been detailed. This procedure really involves two separate

steps:

1. .Re-establish an active connection.

2.Marshal modified records back to the data source.

The following code re-establishes the connection:

With oconnection

.Provider = “SQLOLEDB.1”

.ConnectionString = “Persist Security lnfoFalse;

User ID=salnitlal CatalogNorthwind;Data Source=JVP”

.Open

End With

= oConnection

Then the code marshals the records by attempting the updates

Local Err,cOldErr

cOldErr = On(”error”

On Error Err = .T.

With oRecordset

.ActiveConnection.BeginTrans

.UpdateBatch

If Err

.ActiveConnection.RollBackTrans

.CancelBatch

Else

.ActiveConnection.Com mitTra ns

Endif

End With

On Error &cOldErr

Often, however, there’s a need to shut things down and then

reopen the recordset at another time. To be effective, the

recordset must reflect incremental changes. This cycle may

repeat any number of times.

To illustrate how to persist a recordset, consider again the

following code that modifies records in a Recordset object:

oRecordset.MoveFirst

Do While !oRecordset.Eof

?oRecordset.Fields(”companyname”).Value

oRecordset.Fields(”companyname”).Value =

Upper(oRecordset.Flelds(”com pany name”).Value)

oRecordset.MoveNext

EndDo

Now you can invoke the Save method to persist the recordset:

oRecordset.Save(”c:~tem p~customers.rs”)

At a later time, you can open the persisted recordset:

oRecordset = createobject(”adodb.recordset”)

oRecordset.Open(”c:~temp~customers.rs”)

After the persisted recordset is reopened, you can use the

same code, which establishes a connection to a disconnected

recordset, to make additional modifications. You can marshal

changes made in the persisted recordset to the underlying data

source.

Hierarchical/Shaped Recordsets

Visual FoxPro not only provides the ability to work with local

data, but also the ability to set up relations using the Set

Relation command. When you move the record pointer in the

parent table, the record pointer automatically moves in any child

tables that exist. This makes working with and building inter-

faces for one to many relationships very simple in Visual

FoxPro. Fortunately, the same capability exists in ADO, in the

form of hierarchical recordsets, also referred to as shaped

recordsets.

There are two necessary components when creating and

working with hierarchical recordsets:

The Microsoft DataShape OLE DB Provider, MSDataShape

The Shape language, a superset of the SQL syntax

The first requirement is fairly easy to fulfill because it only

entails setting the Provider property of the ADO Connection

object to the proper value:

oconnection.Provider = “MSDataShape”

The second requirement, using the Data Shape language, is a

bit more challenging. When you first see Data Shape language,

it can be fairly intimidating, just as FoxPro may have been when

you first worked with it. But like anything else, with a bit of

practice and patience, Microsoft Data Shape language will

become second nature.

To examine Shape language, consider a parent-child common

scenario of customers and orders. For each customer, zero or

more orders can exist. In turn, each order can contain one or

more line items. The following code employs Shape syntax to

relate customers and orders in the SQL Server Northwind

database:

(Con’t, page 6)

Page 5