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 environments 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

oRecordset.Update

If Err

For Each oError In oRecordset.ActiveConnection. Errors

With oError

( Con’t, page 7)

Page 6