Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 193946 - Last Review: July 1, 2004 - Revision: 4.3
How To Demo of ADO AddNew, Update, Delete, Find and Filter
This article was previously published under Q193946
ActiveX Data Objects (ADO) implements record manipulation commands
analogous to those found in the native FoxPro language.
The AddNew method adds a new, blank record to the end of the current
Recordset. You use the Update method to write data to the Recordset. The
Delete method removes the current record from the Recordset.
The Find and Filter methods allow you to search the Recordset for specific
field values. Both methods support compound expressions.
The following example instantiates a Recordset from the AUTHORS table in
the SQL Server PUBS sample database. The example demonstrates the AddNew
and Update methods by adding a new record to the Recordset, uses the Find
method to locate the new record and the Delete method to remove the new
record. It then adds the same record again and uses the Filter property to
locate and remove the record.
NOTE: Substitute the SERVER, UID and PWD parameters appropriate to your SQL
Server installation in the Recordset.Open method.
In order to use this example, you must have Microsoft Data Access
Components (MDAC) version 2.x or later installed, which is included in the
data components of Visual Studio 6.0 or can be downloaded from the
following 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")
APPLIES TO
- 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
| kbctrl kbdatabase kbhowto kbsqlprog KB193946 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate