VFP Remote Views of MS SQL data - Can this be done?

[samoody]

I have 2 MS SQL tables, that I access through VFP using remote views.

Parent table
ID - Unique ID Field (default value at SQL level is NEWID())
Name - Name

Child Table
ID - Unque ID Field
Name - Name
FKParentID - ID from Parent.

In VFP, using my remote views, with buffering, I would like to insert a record into the parent table, then insert multiple records into the child table, but if there is a problem with a record in the child table, be able to revert all changes in both the child and parent table.

What I'm finding is that the ID field in the parent table can only be used in the insert of the child table (FkParentID field) if the parent table has been updated, (because of being a Unique ID type field). Therefore I am not able to revert the Parent table if there is a problem inserting records into the child table.

Is there anyway to handle this situation?

I tried using SQLSETPROP (setting to manual transactions) and then using SQLROLLBACK to revert, but it doesn't seem to work with Remote Views (buffering is set to 3).

Another option would be to remove the NEWID() as the default value and determine my own ID, but this could cause issues elsewhere.

Can anyone offer any other suggestions? Any help would be much appreciated.

 

[Mike Lewis]

By far the best way to do this is to write a stored procedure to do all the inserts. Put BEGIN TRANSACTION at the start of the procedure. After each INSERT, test @@ERROR to make to make sure it worked. If it failed, issue ROLLBACK TRANSACTION and exit the procedure. When the last INSERT has succeeded, issue COMMIT TRANSACTION.

Then, call the stored procedure from your VFP code.

Also, don't use NEWID() as the default value for the primary key. Instead, give the PK column an IDENTITY attribute. That way, you can use @@IDENTITY to determine the value of the PK in the new parent record; this is the value that you will insert into the FK column in the child records.

I know writing stored procedures is harder than using remote views, but this really is the best way to get this done.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)