PRB: Update Conflict w/Remote View When WhereType Set to Time

The information in this article applies to:

• Microsoft Visual FoxPro for Windows, version 3.0

SYMPTOMS

Calling TABLEUPDATE() on a SQL table does not succeed in the following situation. A remote view on a SQL table contains a timestamp field, and the WhereType property of the view is set to "Key & Timestamp." When updates are sent to the remote SQL table, the first TABLEUPDATE() successfully updates records. However, if changes are made to the local tables, and you call TABLEUPDATE() a second time, the update is not successful, or the "Update Conflict" error is displayed. However, the TimeStamp field is not modified in the local cursor, and you expected the update to be successful.

CAUSE

When the SQL Where clause or the WhereType properties are set to "Key and TimeStamp," the update fails if the timestamp of the record on the remote table has changed since you first retrieved it. The TimeStamp field of the remote table is updated by the server. The first update to the table on the server modifies the TimeStamp field. If the data is not refreshed on the local server, the data on the cursor and the data on the remote server are not the same, and a conflict occurs when TABLEUPDATE() is called a second time.

The following example illustrates this. The TimeStamp values are fictitious in this example, they are only for demonstration.

DBKey     Field1    TimeStamp

1         "string1"    1
2         "string2"    2
3         "string3"    3

If you change record two to "changed" instead of "string2," the local FoxPro cursor now looks like this:

DBKey     FIELD1    TimeStamp

1         "string1"    1
2         "changed"    2
3         "string3"    3

DBKey     FIELD1    TimeStamp

  1       "string1"    1
  2       "changed"   10
  3       "string3"    3

RESOLUTION

Use GO RECNO() and call REFRESH() to refresh the data on the local cursor before the second update takes place. You can also call REQUERY() if you want to recreate the cursor from the remote data. For more information about the difference between the REQUERY() and the REFRESH() functions, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q130462
   TITLE     : Difference Between REQUERY and REFRESH in Visual FoxPro

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a SQL Server table called Test that contains these fields:

   pfrelease float

      Kkey       float
      timestamp  timestamp
   
   

2. Create a database in Visual FoxPro with a remote view called Rv.

3. In the remote view:

       - Select the Add All button in the Fields tab.
       - In the Update Criteria tab:
         Select Kkey field as the key to update.
         Click the Update All button.
         Select the Send SQL Updates check box.
         In the SQL WHERE clause, select Key and Timestamp button.
   

4. Close and save the view.

5. Browse the Rv view.

6. Change the pfrelease field.

7. Move to the second record, and change the pfrelease field.

8. Move back to the first field, and change pfrelease again. The error appears:

   Update conflict. Use TABLEUPDATE() with IForce parameter to commit the update or TABLEREVERT() to roll back the update.