>>>>>>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;Q138094Mike,
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?
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham