How To Demo of ADO AddNew, Update, Delete, Find and Filter

This article was previously published under Q193946
This article has been archived. It is offered "as is" and will no longer be updated.
ActiveX Data Objects (ADO) implements record manipulation commandsanalogous to those found in the native FoxPro language.

The AddNew method adds a new, blank record to the end of the currentRecordset. You use the Update method to write data to the Recordset. TheDelete method removes the current record from the Recordset.

The Find and Filter methods allow you to search the Recordset for specificfield values. Both methods support compound expressions.
The following example instantiates a Recordset from the AUTHORS table inthe SQL Server PUBS sample database. The example demonstrates the AddNewand Update methods by adding a new record to the Recordset, uses the Findmethod to locate the new record and the Delete method to remove the newrecord. It then adds the same record again and uses the Filter property tolocate and remove the record.

NOTE: Substitute the SERVER, UID and PWD parameters appropriate to your SQLServer installation in the Recordset.Open method.

In order to use this example, you must have Microsoft Data AccessComponents (MDAC) version 2.x or later installed, which is included in thedata components of Visual Studio 6.0 or can be downloaded from thefollowing Web address:

Sample Code

* Demonstrate the ADO AddNew, Update, Find,   * Filter and Delete functions.   #DEFINE adOpenDynamic 2   #DEFINE adLockOptimistic 3   oRecordSet = CREATEOBJECT("ADODB.Recordset")   * SQL Server driver defaults to server-side cursor,   * this would otherwise be necessary to use adOpenDynamic.   oRecordSet.OPEN("select * from authors", ;      "DRIVER={SQL Server};"+;      "SERVER=YourServerName;"+;      "DATABASE=pubs;"+;      "UID=YourUserName;"+;      "PWD=YourPassword",;      adOpenDynamic, adLockOptimistic)   =AddRec()   * Now the record is added - find it and delete it.   oRecordSet.FIND("au_id = '987-65-4321'")   IF NOT oRecordSet.EOF      oRecordSet.DELETE      =MESSAGEBOX("Record deleted")   ENDIF   * Remove comment to display the AU_IDs in the RecordSet.   * =ShowRS()   * Add it again, this time, use a compound Filter to find   * and delete it.   =AddRec()   oRecordSet.FILTER = ("au_id = '987-65-4321' and au_lname = 'Smith'")   IF NOT oRecordSet.EOF      oRecordSet.DELETE      =MESSAGEBOX("Record deleted")   ENDIF   * Remove comment to display the AU_IDs in the RecordSet   * =ShowRS()   * Remove the filter.   oRecordSet.FILTER = ""   * Function ShowRS:   * Display all the au_ids in the RecordSet.   FUNCTION ShowRs   CLEAR   oRecordSet.MoveFirst   ? oRecordSet.RecordCount   * print the au_id field values   DO WHILE ! oRecordSet.EOF      ?oRecordSet.FIELDS("au_id").VALUE   oRecordSet.MoveNext   ENDDO   * Function AddRec:   * Add a new record to the authors table.   FUNCTION AddRec   oRecordSet.AddNew   oRecordSet.FIELDS("au_id")= '987-65-4321'   oRecordSet.FIELDS("au_lname") = "Smith"   oRecordSet.FIELDS("au_fname") = "John"   oRecordSet.FIELDS("phone") = 9999999999   oRecordSet.FIELDS("address") = "123 4th Street"   oRecordSet.FIELDS("city") = "New York"   oRecordSet.FIELDS("state") = "NY"   oRecordSet.FIELDS("zip") = "99999"   oRecordSet.FIELDS("contract") = .T.   oRecordSet.UPDATE   =MESSAGEBOX("Record added")				
Update AddNew Find Filter Delete ADO Recordset kbVFp600 kbActiveX kbADO kbSQL kbCtrl kbMDAC

Article ID: 193946 - Last Review: 12/05/2015 09:26:37 - Revision: 4.3

Microsoft Visual FoxPro 6.0 Professional Edition, Microsoft Data Access Components 2.1 Service Pack 2, Microsoft Data Access Components 2.5, Microsoft Data Access Components 2.6

  • kbnosurvey kbarchive kbctrl kbdatabase kbhowto kbsqlprog KB193946