Calculated/Derived Field Problem

[SGLong]

I'm using VFP to connect to a MS SQL table (tblMain). The SQL table contains a Calculated/Derived field based upon another field in the table. There is a SQL view on tblMain called Main. When records are added or edited in the SQL Query Analyzer they go in without error.

Now for the problem. There is a VFP Remote view (main) based on the SQL View (Main). The VFP view clearly has the derived field marked as not being updatable, and VFP is set to send SQL updates for modified and updateable fields only. When a record is edited in VFP it flows thru to the SQL view and tables without error. When a record is added in VFP, however, I get the following message:

Connectivity error: Microsoft - ODBC SQL Server Driver - SQL Server - Update or insert of view or function 'main' failed because it contains a derived or constant field.

What am I doing wrong?

Steve

 

[Mike Lewis]

Steve,

In the Update Criteria page in the View Designer, is there a tick under the "pencil" icon for the field in question? If so, can you remove it?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[SGLong]

Mike,

Nope - I've made sure that the 'pencil' icon is unchecked...

Steve

 

[Mike Lewis]

Steve,

Sorry ... you said that in your original message.

I wonder if changing the Update Where to "key fields only" will make a difference. It shouldn't, but you never know. (In fact, for an insert, "key fields only" should be OK in any case.)

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[SGLong]

Mike,

I always thought that Key fields referred to fields that are either unique or indexed. For this particular table there are no key fields defined. Should there be?

Steve

 

[JimWWinter]

Steve,

I'm pretty sure that SQL Server requires a primary key in the table. So yes, there should be a primary key defined and selected in the update criteria.

Regards,
Jim

 

[Mike Lewis]

Actually, SQL Server doesn't require a primary key (although it is good design practice to include one).

However, in VFP, an updatable remote view does require at least one unique key -- otherwise it has no way of knowing which record to update.

Steve, you said that there are no unique keys defined. Are you about that? I would have thought you wouldn't be able to do the update if that was so.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk