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

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

Assume you've created a remote view against this table, and set all the fields to be updatable. Additionally, the Wheretype is "Key & Timestamp."

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

When you call TABLEUPDATE(), Visual FoxPro sends the change to the server. The server, in turn, updates the timestamp value for that record:

DBKey     FIELD1    TimeStamp
-----------------------------
  1       "string1"    1
  2       "changed"   10
  3       "string3"    3

Now if you change record two again and try to update it, the "Update Conflict" message appears.

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:

130462 Difference Between REQUERY and REFRESH in Visual FoxPro

With Visual FoxPro for Windows 3.0b, you do not need the GO RECNO(). REQUERY or REFRESH should be sufficient.

MORE INFORMATION

Steps to Reproduce Behavior

Create a SQL Server table called Test that contains these fields:
```
      DBKey      float
      FIELD1     char
      TimeStamp  timestamp
						
```
Add some records similar to the ones illustrated in the CAUSE section of this article. Using the ODBC manager, create a DataSource to access the Database of the table.
Create a database in Visual FoxPro with a remote view called Rv, based on the datasource created in the previous step.
In the remote view:
- Select the Add All button in the Fields tab.
- In the Update Criteria tab:
  
  Select DBkey field as the key and make it updatable. Click the Update All button. Select the Send SQL Updates check box. In the SQL WHERE clause, select Key and Timestamp button.
Close and save the view.
Browse the Rv view.
Change the FIELD1 field.
Move to the second record, and change the FIELD1 field.
Move back to the first field, and change FIELD1 again. The "Update Conflict" error appears.