Tableupdate() on updatable view not working

[MathiasB]

I have remote views in my database. Everything appears to behave as should except when i try this

Open database JB
use jb!rv_tableA in 0 alias v_tableA
select v_tableA
**- navigate to a record
Replace fieldA With "junk"
=Tableupdate() //- I have tried varying tableupdate() syntax

**- just for completeness I am apply buffering 5 to the view, has a primary key

There is no error whatsoever
But when I requery the view or look at the record in management studio it hasn't changed

 

[tomk3]

Hello,

maybe you forgot the checkmark on "send updates" in the RV definition, page 6.

Regards
tom

 

[Mike Lewis]

Can we assume that all the Update Criteria settings are correct?

Have you checked the value returned by TABLEUPDATE()? If it is .F., that indicates an error on the server-side. Use AERROR() to determine what went wrong.

You might also try calling TABLEUPDATE() twice. I know that sound weird, but it is possible that the first call is only updating the view; you need a second call (or close the view) to actually send the updates.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[MathiasB]

Thanks tomk3 & Mike

Your reponses made me look again and it turns out to be this:

CursorSetProp("UpdateNameList", "fields nmes in here")

I thought having the view as updatable was enough, clearly not

Thanks both

 

[Mike Lewis]

Good to see you have got it working, Mathias.

Just to summarise - for the benefit of anyone with a similar problem:

If you are using the view designer, you need to make these settings on the Update Criteria tab:

- In the list of field names, identify the primary key (tick the "key" column) and the updatable fields (tick the "pencil" column).

- SQL WHERE clause: You would normally set this to "Key and modified fields". This is to do with resolving edit conflicts. It tells SQL Server (or other back end) to only update the record if the primary key and the fields that have actually been updated are the same on the server as they are in the view. If any of those values have changed, it means that another user has updated the record, in which case a conflict is reported.

- Update Using: You would normally leave this set to "SQL UPDATE". In practice, it doesn't make much difference because SQL Server itself will decide how to do the update (that might not be the case for other back ends).

- Send SQL Updates: This is a master switch. If you don't tick it, no update will take place, regardless of the other settings on this tab. (It's also the setting that most of us forget to tick from time to time.)

If you using DBSETPROP() to make the relevant settings, you need to set the following properties:

- KeyField: Set to .t. for the primary key.

- Updatable: Set to .t. for each updatable field.

- WhereType: This corresponds to the SQL Where setting; set it to 2 for "key and updatable fields".

- UpdateType: You can usually ignore this (for SQL Server).

The first two of the above are field-level properties (second parameter to DBSETPROP() is "Field"); the other two are at the view level (second parameter is "View").

I've simplified the above slightly, but I hope it will be useful for any programmers using updatable remote views.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads