How To Control Your Updates in ADO Through "Update Criteria"

Article translations Article translations
Article ID: 190727 - View products that this article applies to.
This article was previously published under Q190727
Expand all | Collapse all

SUMMARY

The ADO Client Cursor Engine allows you to control how it builds the action queries that update the database according to the changes you make to the recordset object. This article is designed to help you understand how to control how ADO performs these updates.

MORE INFORMATION

When you open a recordset against the Customers table in the Northwind database (NWind.MDB) and use a client side cursor, ADO retrieves enough information about the structure of the table in order to use an action query to update the table.

An action query is a query that modifies a database and does not return data. For example, "UPDATE Customers SET CompanyName = 'Acme' WHERE CustomerID = 17" is an action query.

ADO determines which field, or set of fields, is the primary key and uses that information to make sure it can find the correct row in the database to update. If you are going to perform updates with the client cursor engine, make sure you have a primary key defined in your table. If you don't, you may accidentally update more rows than you intended.

When you use a client side recordset, ADO exposes a property in the recordset's Properties collection called "Update Criteria." This property allows you to control the information in the WHERE clause in the action query that ADO builds to update the database. The default value for this property is 2 - adCriteriaUpdCols. By default, ADO will use the primary key and all fields being updated in the WHERE clause of the action query. For example:
   rsCustomers.CursorLocation = adUseClient
   rsCustomers.Open "SELECT * FROM Customers", cnNWind, _
                    adOpenStatic, adLockOptimistic, adCmdText
   rsCustomers.Fields("CompanyName").Value = "Acme"
   rsCustomers.Update
				
will cause ADO to execute the following action query
   UPDATE Customers SET CompanyName = 'Acme'
      WHERE CustomerID = 'ALFKI' AND CompanyName = 'Alfreds Futterkiste'
				
The WHERE clause contains information about the primary key and the original value for the field to update. This ensures that if another user has modified the value of the CompanyName field to a value other than the value that ADO originally retrieved, ADO will not update that row and will raise an error instead.

To change the value of this property, use code similar to the following
   rsCustomers.CursorLocation = adUseClient
   rsCustomers.Properties("Update Criteria").Value = adCriteriaAllCols
   rsCustomers.Open "SELECT * FROM Customers", cnNWind, _
                    adOpenStatic, adLockOptimistic, adCmdText
   rsCustomers.Fields("CompanyName").Value = "Acme"
   rsCustomers.Update
				
This code will cause ADO to include every field in the WHERE clause. You would use this value for the "Update Criteria" property if you want to make sure that the update made by the current user will only succeed if no changes have been made to any fields in that row in the table.

The available constants for this property are as follows:
   adCriteriaKey = 0

      Uses only the primary key

   adCriteriaAllCols = 1

      Uses all columns in the recordset

   adCriteriaUpdCols = 2  (Default)

      Uses only the columns in the recordset that have been modified

   adCriteriaTimeStamp = 3

      Uses the timestamp column (if available) in the recordset
				
NOTE: Specifying adCriteriaTimeStamp may actually use adCriteriaAllCols method to execute the Update if there is not a valid TimeStamp field in the table. Also, the timestamp field does not need to be in the recordset itself.

REFERENCES

For additional information about updating a database in Microsoft Visual Basic .NET, click the following article number to view the article in the Microsoft Knowledge Base:
301248 How To Update a Database from a DataSet Object by Using Visual Basic .NET

Properties

Article ID: 190727 - Last Review: June 29, 2004 - Revision: 1.4
APPLIES TO
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft ActiveX Data Objects 2.7
Keywords: 
kbdatabase kbhowto KB190727

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com