The LA Fox Developer Newsletter

April 2000

ADO Jumpstart (Con’t from page 5)

interpret things. The Visual FoxPro ODBC driver generates an

“Unrecognized Command Verb” error message if you only

specify a table name as the source and you fail to use the

optional fifth argument. Note that if you use the ODBC OLE DB

Provider to access SQL Server, you must employ the same

technique that is needed for Visual FoxPro.

Which method should you employ when you populate the

properties individually before invoking the Open method or

passing the arguments to the Open method? Once again, it is a

matter of preference. Of the two, manually populating the

properties makes for more readable code.

Sorting and filtering data are just matters of manipulating the

Sort and Filter properties respectively. The following code sorts

the recordset created from TasTrade in the example above, by

country, ascending, then by region, descending:

oRecordset.Sort = “Country ,Region Desc”

The following code displays the sort and the functionality of the

AbsolutePosition and Bookmark properties.

oRecordset.MoveFlrst

Do While Not oRecordset.Eof

With oRecordset

?.Fields("country").Value,;

.Flelds(”reglon”).Value,;

.AbsolutePosition,;

.Bookmark

.MoveNext

EndWith

EndDo

Setting a filter is as easy as setting the sort. The following code

filters for records where the country is Germany:

oRecordset.FlIter = “Country = ‘Germany”

The Filter property also supports multiple values:

oRecordset.Filter = “Country = ‘Germany’ Or Country = “Mexico”

Finally, wild card characters are also supported:

oRecordset.Filter “Country Like

To reset either the Filter or Sort properties, set them equal to an

empty string:

oRecordset.Sort =

oRecordset.Fllter =

Finding Data

Another important capability of an ADO recordset is the ability

to find records based on a search string. This capability works

like searching for records in a Visual FoxPro cursor. Unlike the

Seek or Locate statement in Visual FoxPro, the Find method

provides control over the scope of records that are searched.

The following code searches for a country that begins with the

letter “B.”

oRecordset.Find(”country Like ‘B%”)

Although multiple criteria are not allowed, wild card searches

are permitted:

oRecordset.Find(”country Like

Searches for multiple criteria, such as the following, would

result in an error

oRecordset.Flnd(”country Like 'G*' Or country Like ‘B”’”)

Transactions/Updating Data/Conflict Resolution

Updating data in an ADO recordset is a fairly simple process.

As in any environment, conflict resolution in multi-user environ-

ments is always an issue to be dealt with. This is where the

Errors collection comes into play. Error trapping and handling

needs to become an integral part of your ADO-related code. The

following code samples employ a simple error handling scenario

and use the Errors collection to determine whether conflicts

have occurred. For a complete list and description of ADO error

codes, consult the online documentation.

When you update data, you can update either a single row, or

several rows at a time in batch mode. These methods most

closely correspond to row and table buffering, respectively, in

Visual FoxPro. Building on the recordset already created, the

lock type is Batch Optimistic. While updates are normally

conducted in batches, you can also update one row at a time,

just as in Visual FoxPro.

The following code modifies the CompanyName field and

attempts to update the SQL Server data source:

oRecordset.Fields(”companyname” ).Value =

“Ace Tomato Company”

oRecordset.Update

Depending on a variety of scenarios, this code may or may not

work. Perhaps a contention issue exists? Perhaps the user

does not have rights to modify data. Hundreds of issues can

cause an attempted update to fail. Therefore, anytime you

attempt an update, you should employ error trapping. The

following code expands the previous example and makes it a bit

more robust:

Local Err,cOldErr,oError

cOldError = On(”Error”)

On Error Err = .T.

oRecordset.Fields(”companyname”).Value

=

“Ace Tomato Company”

oRecordset.Update

If Err

For Each oError In oRecordset.ActiveConnection. Errors

With oError

( Con’t, page 7)

Page 6