Hi Jozef

Please ignore my previous comments. Despite what the VFP Helpfile says, I have tried using BEGIN TRANSACTION - ROLLBACK END TRANS with a CA cursor and I am pleased to say thet IT WORKED!!! Yahoo!!!

So I will wrap a SQL Transaction with a VFP Begin/END TRANS and all is well.

Thanks for your help

Bernard



>Hi Jozef
>
>Please ignore my previous comments. Despite what the VFP Helpfile says, I have tried using BEGIN TRANSACTION - ROLLBACK END TRANS with a CA cursor and I am pleased to say thet IT WORKED!!! Yahoo!!!
>
>So I will wrap a SQL Transaction with a VFP Begin/END TRANS and all is well.
>
>Thanks for your help
>
>Bernard
>
>
>
>>Thanks Jozef for your prompt reply. I have downloaded the article and will read it.
>>
>>What I have gleaned so far is that I have to use VFP's Begin Trans - Rollback - ENDTRANS
>>
>>Fron VFP8 Helpfile:
>>
>>Begins a transaction. Transactions are supported only for tables in a database. See CREATE DATABASE and ADD TABLE for information on how to create and add tables to a database. :(
>>
>>
>>How do I add a cursor created with a Cursoradapter to a database so that I can use VFP Transactions or am I missing something?
>>
>>
>>
>>>Hi Bernard !
>>>SQLRollback does not revert changes in VFP tables - you can try to use ROLLBACK comamnd in addition to SQLROLLBACK()
>>>
>>>You can also read an interesting foxtalk article Transactions in a VFP Client/Server Application by Hector J. Correa (from september 2000).
>>>It is available at http://msdn.microsoft.com/library/default.asp (VFP periodicals node)
>>>
>>>
>>>
>>>>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