Dmitry,
If you wan to use SPTexec() then do the following
?SQLSETPROP(lnHandle, 'Transactions', 2)
scan
?sqlexec(lnHandle,"insert table (col1,col2) values(?col1,?col2)"
endscan
if SQLCOMMIT(lnHandle) < 0
aerror(....)
?sqlrollback(lnHandle)
endif
>>>>>>>I am in the process of converting a VFP app to SQL Server database.
>>>>>>>
>>>>>>>I have a routine (sort of business object) that updates several tables and within each table several records. Some process involves comparing values in a VFP cursor against values in the database tables and then making changes to the database table fields.
>>>>>>>
>>>>>>>I don't want this routine to be in a stored procedure. All code should reside in VFP with SQLEXEC calls getting and setting values to the tables and records. Of course, I have to rewrite the entire code because the current VFP code is not structured for SQL Server database.
>>>>>>>
>>>>>>>How do I create a BEGIN TRANSACTION / END TRANSACTION with a bunch of calls to the database with SQLEXEC that would either retrieve or update some tables and records?
>>>>>>>
>>>>>>>Any suggestions would be appreciated.
>>>>>>
>>>>>>Use table buffering on all tables. Make changes to all tables. Then do something like:
>>>>>>
>>>>>>llsuccess = .t.
>>>>>>begin transaction
>>>>>>if m.llsuccess and not tableupdate("table1")
>>>>>> aerror(gaError)
>>>>>> llsuccess = .f.
>>>>>>endif
>>>>>>if m.llsuccess and not tableupdate("table2")
>>>>>> aerror(gaError)
>>>>>> llsuccess = .f.
>>>>>>endif
>>>>>>if m.llsuccess
>>>>>> end transaction
>>>>>>else
>>>>>> rollback
>>>>>> *display error
>>>>>>endif
>>>>>
>>>>>The above approach looks like what I do in the VFP application. But I don't think you can't update SQL Server tables using TableUpdate(). And I don't use remote views in this application, so I don't see how TableUpdate would work there. Unless I am misunderstanding something.
>>>>
>>>>You can take a cursor and make it into an updateable remote view just by setting the correct cursorsetprop settings.
>>>
>>>That's an interesting approach. I will think/try this. Thank you very much.
>>
>>
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q138094>
>Mike,
>
>Thank you for the link.
>
>One thing that bothers me is what happens if another user changes the value in the database table while I am potsing around with the cursor/view? Then the program tries to update the table but the values are different there? How would you "trap" these type of conditions?