Back

Originally published in FoxTALK in May 1998


 

TableUpdate() in Visual FoxPro

TableUpdate() is one of the functions used very frequently when using buffering in Visual FoxPro.  Version 5.0 of Visual FoxPro has enhanced the functionality of this function.  This month we will look at the improvements to the TableUpdate() function.

The Syntax

In Visual FoxPro version 5 the TableUpdate() function has been improved.  There are new options that can be used to make this function more useful to us as developers.  TableUpdate() takes four arguments which control it actions.  Listing 1 shows the syntax for the TableUpdate function.

TableUpdate( <nRecords>, <lForce>, <cAlias or nWorkArea>, <aErrorRecords> )

Listing 1 – The syntax for TableUpdate

nRecords

Lets look at each of the arguments for this function.  The first argument controls how the function will deal with multiple records in the buffer.  This is probably the most confusing of the arguments for the function.

The nRecords will accept one of three values 0, 1, or 2.  A value of 0 will only commit the current record regardless of whether the buffering is table or row.  This argument can be used to control single record editing when using table buffering or for processing each buffered record separately.

A value of 1 will update all buffered records in one call to TableUpdate.  If the TableUpdate encounters a record that cannot be updated it will fail at that point and return a value of .F. indicating that it failed.  No records beyond the one with the problem are processed.

Using 2 as the nRecords value also update all records in one call to the function, but it reacts differently to a failed record.  If a record cannot be updated the TableUpdate will continue with the rest of the records and update those that it can.  TableUpdate will return a .F. indicating that there was a problem and it will fill the array referenced in the fourth argument with the record numbers that could no be updated.

If no value is provided for nRecords the value of 0 is used.

lForce

The second argument is a logical value of .T. or .F. and it controls how TableUpdate will deal with conflicts from another user having changed the record while we were working on it.  A value of .T. will force our update and overwrite the changes made by the other user while a value of .F. will fail to update if another user has made changes to the same record.

It is a good idea to use .F. for this argument as overwriting changes of other users can produce unwanted results in the table.  We can always write recovery code for a failed TableUpdate that can rectify the differences and then force an update (we will discuss this type of code next month).

cAlias/nWorkArea

This argument is used to determine what work area or alias will be affected by the TableUpdate function call.  If this argument is omitted then the currently selected work area is affected.  It is recommended that you always provide the alias name to the TableUpdate() function in order to prevent unexpected results.  In Visual FoxPro it is very easy to have a work area changed on you and by using this argument you will leave no question as to which work area should be updated.

aErrorRecords

This argument is the name of an one dimensional array that will hold the record numbers of any records that failed update when using the first argument of 2.  This argument should always be provided when using a value of 2 for the first argument, as there is no other way to find out which records were not updated.

When the first argument is 0 or 1 the record pointer in the alias being updated is left sitting on the record that failed the update.

Show us some code

With all of these options on the function there are a number of ways we might use it in our code.  Let’s examine some scenarios.

 Scenario 1 – A single record update

We have a form that allows only one customer record to be edited and is using only the customer table.  The code we might have in the saving method of this form is seen in Listing 2.

LOCAL lcFldState

lcFldState = GetFldState( -1,”Customer”)

IF “2” $ lcFldState OR “4” $ lcFldState

   * We either have an edited existing record or an edited new record

   IF NOT TableUpdate( 0, .F., “Customer” )

      * The update failed

      TableRevert(.F., “Customer”)

   ENDIF

ENDIF

 Listing 2 – An example of a single record update.

 Scenario 2 – Multiple records all or none

In this situation we have a form editing customers and it allows the user to edit multiple customer records before saving the work.  We want to either save all of the edits or save none of them.

* We either have an edited existing record or an edited new record

BEGIN TRANSACTION

IF NOT TableUpdate( 1, .F., “Customer” )

   * The update failed

   ROLLBACK

   TableRevert(.T., “Customer”)

ELSE

   END TRANSACTION

ENDIF

Listing 3 – Updating multiple records using 1 as the first argument to TableUpdate()

 Scenario 3 – Multiple records do what you can

In this one we have the same editing situation as in scenario 2 but here we want to save whatever we can and revert only those records that could not be saved.

* We either have an edited existing record or an edited new record

DIMENSION laBadRecs(1)

IF NOT TableUpdate( 2, .F., “Customer”, laBadRecs )

   * The update failed

   FOR EACH nRecord IN laBadRecs

      GOTO nRecord

      TableRevert(.F., “Customer”)

   ENDFOR

ENDIF

Listing 4 – Updating multiple records using 2 as the first argument to TableUpdate()

In Summary

The TableRevert() function was used in each of the examples, this function takes only two arguments.   The first is .T. to revert all records and .F. to revert only the current record.  The second argument is the alias to be reverted.

In the second scenario a transaction was used to wrap the update into an all or none operation.  Transactions will be the subject of another paper.

The enhancements to the TableUpdate function provide us with some very fine control over the workings of the update operation.  As you can see in the examples we can control the updating of our tables to provide the exact behavior we desire for each form we build.