How to update data by using the TableUpdate function with the CursorAdapter object in Visual FoxPro (814184)


The information in this article applies to:

  • Microsoft Visual FoxPro 8.0
  • 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("IF _tally=0" + CHR(10) +
		  "ERROR('Update conflict')" + CHR(10) + "ENDIF")]
In this case, Tableupdate() returns false ( .F. ) and allows you to handle the failure.
#DEFINE CRLF CHR(13)+CHR(10)
Local loCursor,ovfp
CLEAR
 
ON ERROR
 
Set Exclusive Off
Close Databases All
Set Multilocks On
loCursor = Createobject('CA')
* Load loCursor with the data specified in SelectCmd and display error message if error occurs.
loCursor.CursorFill()
GO top
* Display the value of the company name before update.
? "Before:",companyname
?
ovfp=Createobject("visualfoxpro.application.8")
ovfp.DoCmd("set exclusive off")
ovfp.DoCmd("update (_samples+'\northwind\customers') set companyname='Alfreds Futterkisted' where customerid='ALFKI'")
GO top
* Update the data in the cursor.
replace companyname WITH 'Alfreds Futterkiste'
* Update the back end.
retval=TABLEUPDATE(0,.F.,locursor.alias)
Messagebox("Tableupdate="+Transform(retval))
 
* If update conflict occurs, display the error.
if(retval=.F.)
 LOCAL ARRAY errors(1)
 AERROR(errors)
* Displays the errors.

 IF "Update conflict"$errors[2]
  MESSAGEBOX("Update Conflict-reverting changes")
  =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
* Display the value of the company name after update.
?
? "After:",companyname
 
Define Class CA As CursorAdapter
 Alias = 'test1'
 DataSourceType = 'NATIVE'
 SelectCmd = 'select * from (_samples+"\northwind\customers")'
 Tables = 'Customers'
 KeyFieldList = "customerid"
 UpdatableFieldList = "companyname"
 UpdateNameList = "customerid customers.customerid,companyname customers.companyname"
 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("IF _tally=0" + CHR(10) + "ERROR('Update conflict')" + CHR(10) + "ENDIF")]
 
 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
  ? "Update Command sent="+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)
  ? "With the OLDVAL() and test1.companyname evaluated the update statement is :"+UpdateInsertCmd
  * Check tally.
  ? "Tally="+Transform(_Tally)
 
 Procedure BeforeUpdate
  Lparameters cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd
  cUpdateInsertCmd=cUpdateInsertCmd+this.ConflictCheckCmd
 
ENDDEFINE

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')

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


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

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

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

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

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

DEFINE CLASS CA AS CursorAdapter
	Alias = 'test1'
	SelectCmd = 'select * from customers'
	Tables = 'Customers'
	KeyFieldList = "Customerid"
	UpdatableFieldList = "companyname"
	UpdateNameList = "customerid customers.customerid,companyname customers.companyname"
	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 =";IF @@ROWCOUNT=0 RAISERROR (' Update conflict.', 16, 1)"
	
	
	* Initializing the connectivity to Data source (SQL Server) by using ODBC driver.
	PROCEDURE init 
		WITH THIS
			.DataSourceType = 'ODBC'
			.DataSource=SQLSTRINGCONNECT([Driver=SQL Server; SERVER=<SQL SERVER NAME>; DATABASE=NORTHWIND])
		ENDWITH
	ENDPROC
	
	PROCEDURE BeforeUpdate
		LPARAMETERS cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd
		
		? "Entering BeforeUpdate()"
		
		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.
		? "Update Command sent="+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)

		? "With the OLDVAL() and test1.companyname evaluated the update statement is :"+UpdateInsertCmd
		?
		? "Leaving AfterUpdate()"
	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

REFERENCES

For more information, see the following topics in the Visual FoxPro Help files.
  • "CursorAdapter Object Properties, Methods, and Events"
  • "Data Access Management Using CursorAdapters"
  • "Detecting and Resolving Conflicts"
  • "Locking Data"
  • "Management of Updates with Views"
  • "Programming for Shared Access"
Modification Type:MajorLast Reviewed:3/19/2005
Keywords:kbCodeSnippet kbinfo KB814184 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.