RV GetFldState() and SQL Transactions

I'm doing detail level transaction testing with SQL Server 2000 as the back end and am running into an extremely annoying condition. Here is the scenario:

v_Office - parent and identifies offices around the country (Record Buffered)
v_OfficeTerritories - child and identifies 0, 1 to n offices that belong to a territory.

The validation rule is that each office territory must be unique (name as well as code) not only to the office but also to the entire company. This is implemented at the srever level using SQL Server FK constraints.

The test scenario is to enter a office (which passes all validation rules both at the client level and the server level), enter two territories with the exact same name (or code, either one) and press the Save button.

At this point the framework takes over and performs admirably (except for one small insignificant detail that I'll address in a subsequent post). (Just as an aside, the parent business object is set to use transactions and the child is not ... this really doesn't matter as nested transactions are not supported with remote views ... anywhoo). When the update to the child records take place, the error message from SQL Server is properly processed, the transaction rolls back and the parent and child tables on the server are not corrupt. This, however, is where things get interesting.

I placed code in the double click of the form that issues the GETFLDSTATE() function on the parent AND all of the child records in the remote views. The parent returns the following "11111111" the first child record returns "11111111" and the second child record ... the one that causes the problem in the first place ... returns "3444444". Previous to this Server level rule failure all of these values were as expected "34444444" ... appended not modified or appended modified.

Basically, what happens is that Visual FoxPro is not smart enough to know that not all of the records got through after the TABLEUPDATE is executed, and failed, and the SQLROLLBACK() function is executed. The v_Office view's status is changed to "existing record not modified". The first record in the v_OfficeTerritory view is also changed to "existing record not modified" ... the second record, the one that caused the problem due to duplicate data, remains "appended record modified" except for the key field.

At this point we have a client/server mismatch. On SQL Server the tables are pristene ... no new records present in any of them. On the client side we have two cursors that are telling the framework that the information does exist on SQL Server. At this point, the user has been presented with a message that states "Duplicates not allowed, please try again". So, they do, they fix the second record and press save. But lo and behold nothing happens becuase the parent record is telling the business object "Hey, I don't need saving ... no changes have been made". Things then deteriorate from there with all possible variations.

The problem comes when I try to fix it/get around it. I can't change field state manually as you can't change from "existing" to "appended" classifications a "1" or "2" TO A "3" or "4" using SETFLDSTATE(). I can't have the user press Cancel as only the last record (the one with "34444444") disappears which misleads the user they can modify the remaining ones, they can't. If they try they get the correct error message saying that someone else has deleted the records from the server.

In this situation it appears the only work around is to do presave validations on the client for duplicate records ... basically duplicate the FK Contraints for the child in the client so that as long as people are using the client for modifications the server doesn't have to deal with this issue. The FK Constraints protect the server from manual updates using Enterprise Mangler (that'd be a Duffyism).

Is this a correct assessment?

Thanks,

CT

Congrats on the new book, BTW. It is quite lucid so far ... Chapter 3 and counting. At this point I think it should be mandatory reading for every VFP'r interested in .NET. Well done Mr. M.