In the end, you get the same results... Also, since timestamp values are unique in a database, a timestamp field is really part of the key. In the back end, since we have identified the PK and the timestamp as the key fields, SQL Server is doing something on the order of:

Where id = @id and timestamp = @timestamp.

If you use a stored proc for an update, you would need to do this yourself.

Since you are not rendering your own update statement, you need to do this. I think you would rather do this, than create you update statements, which is a PITA outside of ADO....

Now, the SQL docs themselves say that you should not use a timestamp field as part of the key... However, I think this is limited to indexes and the PK. IN this case, we are using it as a basis for comparision.

Is this a workaround because it appears we are over-loading the key fields list? Perhaps? However, it may be that VFP only has the ability to automatically create the WHERE CLAUSE of the UDPATE STATEMENT for cursors created via a RV in a DBC. SQL Profiler clearly shows the WHERE CLAUSE getting built. Heck, only tables that are part of a DBC can participate in VFP transactions... So, there is some precedent for this.

Regardless of whether you include the timestamp field as part of the key field list or as part of the where clause, in reality, it all means the same thing as far as SQL is concerned.


>John,
>
>That works - for the obvious reason that VFP will use everything in the key field list any way. In fact, that works (uses the timestamp) even with the wheretype = 1.
>What is also interesting, is that the Remote View - which DOES use the timestamp in its where clause (with the wheretype set to 4), does NOT have the timestamp field in its key fields list!
>
>In short - I'm still calling it a bug or at least a real poor lack of documentation somewhere. You idea will work on a practical level even though I believe it to be a "work-around" rather than the answer - but hey - if it works practically - thats the biggest thing.
>
>Thanks much for your help. I appreciate the time you've spent on this. Let me know if you find out anything else.
>
>Ken
>
>>Hi Ken...
>>
>>OK.... I have this problem solved...
>>
>>The trick is to add the timestamp field name to the keyfieldlist property of the cursor:
>>
>>CursorSetProp( 'KeyFieldList',"id,lastedited","cCustomer")
>>
>>
>>In my sample, lastedited is the timestamp field.
>>
>>Here is the sample code to setup the cursor:
>>
>>lnHandle = sqlconnect("test","sa","")
>>=sqlexec(lnHandle,"exec getcustomers","cCustomer")
>>CursorSetProp('Tables',"customers","cCustomer")
>>CursorSetProp('UpdatableFieldList',"id,name,lastedited","ccustomer")
>>CursorSetProp('UpdateNameList',"id customers.id,name customers.name lastedited customers.lastedited","cCustomer")
>>CursorSetProp( 'KeyFieldList',"id,lastedited","cCustomer")
>>CursorSetProp( 'UpdateType', 1,"cCustomer")
>>CursorSetProp( 'WhereType', 4,"cCustomer")
>>CursorSetProp( 'FetchMemo', .T.,"cCustomer")
>>CursorSetProp( 'SendUpdates', .T.,"cCustomer")
>>CursorSetProp( 'UseMemoSize', 255,"cCustomer")
>>CursorSetProp( 'FetchSize', 100,"cCustomer")
>>CursorSetProp( 'MaxRecords', -1,"cCustomer")
>>CursorSetProp( 'Tables', 'dbo.customers',"cCustomer")
>>CursorSetProp( 'Prepared', .F.,"cCustomer")
>>CursorSetProp( 'CompareMemo', .T.,"cCustomer")
>>CursorSetProp( 'FetchAsNeeded', .F.,"cCustomer")
>>CursorSetProp( 'FetchSize', 100,"cCustomer")
>>CursorSetProp( 'BatchUpdateCount', 1,"cCustomer")
>>CursorSetProp('Tables',"customers","cCustomer")
>>
>>
>>Let me know how things work out!! :)