Hi all

I am endeavouring to do transactions in VFP with the database as SQLServer. To access data I have a set of CursorAdapters.

The situation is this:

I have a form for data entry.CLIENTS. SO if the user wants to add a new client, I get an empty cursor thru the CA and append a record for the user to edit. When the user "Saves" I do the following:

* set transactions on
SQLSETPROP(oConn,"Transactions",2)
IF NOT ThisForm.SaveData()
SQLROLLBACK(oConn)
ELSE
IF SQLCOMMIT(oConn) < 0
AERROR(aer)
MESSAGEBOX(aer[2])
SQLROLLBACK(oConn)
ELSE
WAIT WINDOW NOWAIT "Data has been updated"
ENDIF
ENDIF
* set transactions off
SQLSETPROP(oConn,"Transactions",1)

In the SAVEDATA() method other tables as well as the client dataentry table are updated as well, hence the whole update is done in a transaction.

The problem I have is when the transaction FAILS and is ROLLEDBACK.

During the saving, just before the TABLEUPDATE is issued on the CLIENT table that is being edited, GETFLDSTATE(-1) returns 344444444444444 for as many fields as there, since I have just appended a record in the CA and edited it.

After the TABLEUPDATE (even if I issue SQLROLLBACK) the GETFLDSTATE of all fields in the Client table are 1111111111111.

This means that any subsequent TABLEUPDATES on this client table will NOT save the data since the CA looks at the status of the GETFLDSTATE before it issues an Insert or Update. If GETFLDSTATE returns 1 then the Cursor updates or inserts are not sent to SQL though TABLEUPDATE returns .T.

The Transaction failure can occur for any number of reasons and if it does, then the client will be unable to save the currently edited client record because TABLEUPDATE just ignores the fields after that since their GETFLDSTATE is all 1.
Also it then returns .T. though no fields have been inserted. Changing a field and then trying to save also fails since only that field has a FLDSTATE of 2, with the others remaining on 1. So TABLEUPDATE noe sends an UPDATE command to SQL that fails since the record does not exist in SQL.

I hope I have made my explanation clear.

Is there any way around this? Any solid way to do transactions using CA's and SQLServer?

TIA

Bernard Bout