Microsoft KB Archive/814184

= How to update data by using the TableUpdate function with the CursorAdapter object in Visual FoxPro =

Article ID: 814184

Article Last Modified on 3/19/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 9.0 Professional Edition

-



SUMMARY
This article describes the following topics:
 * How the TableUpdate function interacts with the CursorAdapter class object to update the back-end data.
 * How to handle update conflicts when you use the new CursorAdapter class with the TableUpdate function to update the data in Microsoft Visual FoxPro 8.0 and in Visual FoxPro 9.0.



MORE INFORMATION
You can retrieve data from local or remote data sources by using the CursorAdapter class for Visual FoxPro 8.0 and 9.0. By default, cursors that are created with the CursorAdapter class do not update the back-end data. To update the back-end data, you must set the following properties of the CursorAdapter class:
 * InsertCmd
 * UpdateCmd
 * DeleteCmd

If you do not set these properties, you can generate the back-end SQL update command automatically by setting the following CursorAdapter properties:
 * Tables
 * KeyFieldList
 * UpdatableFieldList
 * UpdateNameList
 * SendUpdates

Handling Update Conflict
When you try to update the back-end data by using CursorAdapter, the TableUpdate function returns the results for the update of the CursorAdapter cursor. If an update conflict occurs, the update of back-end data may not succeed. However, TableUpdate may still return True, because the data in the CursorAdapter cursor is updated.

An update conflict is a situation where a user tries to modify a record that has changed since it was retrieved. Following is an example where an update conflict can occur:
 * 1) User1 opens a cursor on the customer table.
 * 2) User2 updates record number 1 and commits the transaction.
 * 3) User1 updates record number 1 (by using the TableUpdate function).

At this point, User1 has an update conflict: the back-end record that User1 is trying to update is changed after it is retrieved.

Sample Code for Native DataSourceType
The following sample code uses Visual FoxPro 8.0 Native DataSourceType and updates a record in the SQL Server Northwind sample database. To verify whether the row is updated, the following is appended to the update command: CRLF+[EXECSCRIPT(&quot;IF _tally=0&quot; + CHR(10) +         &quot;ERROR('Update conflict')&quot; + CHR(10) + &quot;ENDIF&quot;)] In this case, Tableupdate returns false ( .F. ) and allows you to handle the failure.

Local loCursor,ovfp CLEAR ON ERROR Set Exclusive Off Close Databases All Set Multilocks On loCursor = Createobject('CA') loCursor.CursorFill GO top ? &quot;Before:&quot;,companyname ? ovfp=Createobject(&quot;visualfoxpro.application.8&quot;) ovfp.DoCmd(&quot;set exclusive off&quot;) ovfp.DoCmd(&quot;update (_samples+'\northwind\customers') set companyname='Alfreds Futterkisted' where customerid='ALFKI'&quot;) GO top replace companyname WITH 'Alfreds Futterkiste' retval=TABLEUPDATE(0,.F.,locursor.alias) Messagebox(&quot;Tableupdate=&quot;+Transform(retval)) if(retval=.F.) LOCAL ARRAY errors(1) AERROR(errors)
 * 1) DEFINE CRLF CHR(13)+CHR(10)
 * Load loCursor with the data specified in SelectCmd and display error message if error occurs.
 * Display the value of the company name before update.
 * Update the data in the cursor.
 * Update the back end.
 * If update conflict occurs, display the error.
 * Displays the errors.

IF &quot;Update conflict&quot;$errors[2] MESSAGEBOX(&quot;Update Conflict-reverting changes&quot;) =TABLEREVERT(.T.,locursor.alias) ENDIF endif * Refresh the Cursor to get the updated data. loCursor.CursorRefresh && Get the data again to be sure GO top ? ? &quot;After:&quot;,companyname Define Class CA As CursorAdapter Alias = 'test1' DataSourceType = 'NATIVE' SelectCmd = 'select * from (_samples+&quot;\northwind\customers&quot;)' Tables = 'Customers' KeyFieldList = &quot;customerid&quot; UpdatableFieldList = &quot;companyname&quot; UpdateNameList = &quot;customerid customers.customerid,companyname customers.companyname&quot; WhereType= 3 * This is a custom property, that is added to handle update conflicts. It does not do * anything by itself. It is added below to the automatically-generated UpdateInsertCmd to * test whether anything was actually updated. ConflictCheckCmd =CRLF+[EXECSCRIPT(&quot;IF _tally=0&quot; + CHR(10) + &quot;ERROR('Update conflict')&quot; + CHR(10) + &quot;ENDIF&quot;)] Procedure AfterUpdate Lparameters cFldState, lForce, nUpdateType, UpdateInsertCmd, DeleteCmd, lResult * To see why it will fail on the back end, look at the UpdateInsertCmd that is used ? &quot;Update Command sent=&quot;+UpdateInsertCmd * Swap the actual values in the command to see what occurred. UpdateInsertCmd=Strtran(UpdateInsertCmd,[OLDVAL('customerid','test1')],Oldval('customerid','test1')) UpdateInsertCmd=Strtran(UpdateInsertCmd,[OLDVAL('companyname','test1')],Oldval('companyname','test1')) UpdateInsertCmd=Strtran(UpdateInsertCmd,[test1.companyname],test1.companyname) ? &quot;With the OLDVAL and test1.companyname evaluated the update statement is :&quot;+UpdateInsertCmd * Check tally. ? &quot;Tally=&quot;+Transform(_Tally) Procedure BeforeUpdate Lparameters cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd cUpdateInsertCmd=cUpdateInsertCmd+this.ConflictCheckCmd ENDDEFINE
 * Display the value of the company name after update.

Sample Code for SQL Server DataSourceType
The following sample code uses the SQL Server DataSourceType and updates a record in the SQL Server Northwind sample database. To verify whether the row is updated, add the following to the update command: IF @@ROWCOUNT=0 RAISERROR (' Update         conflict.', 16, 1) In this case, Tableupdate returns false ( .F. ) and allows you to handle the failure.

LOCAL loCursor,ovfp,nhnd,lsuccess

CLEAR SET EXCLUSIVE OFF CLOSE DATABASES ALL SET MULTILOCKS ON

loCursor = CREATEOBJECT('CA')

IF !loCursor.CursorFill =AERROR(lar) MESSAGEBOX(lar[2]) ENDIF
 * Load loCursor with the data specified in SelectCmd and display error message if error occurs.

? &quot;Company Name Before Update:&quot;,companyname ? nhnd=SQLSTRINGCONNECT([Driver=SQL Server; SERVER=; DATABASE=NORTHWIND]) =SQLEXEC(nhnd,[update customers set companyname='Alfreds Futterkiste' where customerid='ALFKI']) =SQLDISCONNECT(nhnd)
 * Display the value of the company name before update.
 * Create a connection handle for SQL Server so you can set up the update conflict.

GO TOP REPLACE companyname WITH 'Alfreds Futterkisted'
 * Now make a change to the local data, and then try to update it.

lsuccess=TABLEUPDATE(0,.F.,locursor.alias) Messagebox(&quot;Tableupdate=&quot;+Transform(lsuccess)) IF !lsuccess =AERROR(lar) IF &quot;Update conflict&quot;$lar[2] MESSAGEBOX(&quot;Update conflict!-Reverting changes&quot;) =TABLEREVERT(.f.,locursor.alias) ENDIF ENDIF
 * Error handling function. Displaying the error message if update conflict occurs.

loCursor.CursorRefresh GO TOP
 * Get the current data from the CursorAdapter.

? ?&quot;Company Name After Update:&quot;, companyname
 * Displaying the value of the company name after update.

DEFINE CLASS CA AS CursorAdapter Alias = 'test1' SelectCmd = 'select * from customers' Tables = 'Customers' KeyFieldList = &quot;Customerid&quot; UpdatableFieldList = &quot;companyname&quot; UpdateNameList = &quot;customerid customers.customerid,companyname customers.companyname&quot; WhereType= 3 && Key and modified * This is a custom property that is added to handle update conflicts. It does not do   * anything by itself. It is added below to the automatically-generated UpdateInsertCmd to   * test whether anything was actually updated. ConflictCheckCmd =&quot;;IF @@ROWCOUNT=0 RAISERROR (' Update conflict.', 16, 1)&quot; * Initializing the connectivity to Data source (SQL Server) by using ODBC driver. PROCEDURE init WITH THIS .DataSourceType = 'ODBC' .DataSource=SQLSTRINGCONNECT([Driver=SQL Server; SERVER=; DATABASE=NORTHWIND]) ENDWITH ENDPROC PROCEDURE BeforeUpdate LPARAMETERS cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd ? &quot;Entering BeforeUpdate&quot; cUpdateInsertCmd=cUpdateInsertCmd + THIS.ConflictCheckCmd ENDPROC PROCEDURE AfterUpdate

LPARAMETERS cFldState, lForce, nUpdateType, UpdateInsertCmd, DeleteCmd, lResult

* To see why it will fail on the back-end, look at the UpdateInsertCmd that is used. ? &quot;Update Command sent=&quot;+UpdateInsertCmd

* Swap the actual values in the command to see what occurred. lcActualCmd =Strtran(UpdateInsertCmd,[OLDVAL('customerid','test1')],Oldval('customerid','test1')) lcActualCmd =Strtran(UpdateInsertCmd,[OLDVAL('companyname','test1')],Oldval('companyname','test1')) lcActualCmd =Strtran(UpdateInsertCmd,[test1.companyname],test1.companyname)

? &quot;With the OLDVAL and test1.companyname evaluated the update statement is :&quot;+UpdateInsertCmd ?       ? &quot;Leaving AfterUpdate&quot; ENDPROC * Destroying the connection. PROCEDURE destroy =SQLDISCONNECT(THIS.DataSource) ENDDEFINE Note This approach in the previous code does not work when you use batch updating. For example, you may use cursoradapter.batchupdatcount>1. When you use batch updating, the following events do not fire:
 * BeforeInsert
 * AfterInsert
 * BeforeUpdate
 * AfterUpdate
 * BeforeDelete
 * AfterDelete

