PRB: No Update Tables are Specified When Updating Records in a View Based on Another View

• Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a, 6.0, 6.0 SP3

SYMPTOMS

After creating an updateable SQL View in Visual FoxPro that is based on another updateable view using the CREATE SQL VIEW command, updating a record could result in the following error:

No update tables are specified. Use the Tables property of the cursor.

CAUSE

This could be caused by not specifying the update tables in the Tables property of the view or by not specifying the UpdateName property of each field in the view.

RESOLUTION

When creating a SQL View in Visual FoxPro using the Create SQL View command, ensure the Tables and UpdateName fields are properly set.

Notice in the sample below that the code uses the database name in the table and field references for the Tables and UpdateName properties.

If V1 was a remote view to SQL Server, you would change the database name to the database owner (dbo). So the setting for the Tables property in the sample would be:

DBSETPROP('v1','view','tables','dbo.test1') 

SET SAFETY OFF
CLOSE DATA ALL
CREATE DATA kbtest1
CREATE TABLE test1 (fld1 c(10) PRIMARY KEY, fld2 b(3),fld3 T)
INSERT INTO test1 VALUES ('1',2.322,DATETIME())
CREATE SQL VIEW v1 AS SELECT test1.fld1,test1.fld2,test1.fld3 FROM test1
CREATE SQL VIEW v2 AS SELECT v1.fld1,v1.fld2,v1.fld3 FROM v1

=DBSETPROP('v1','view','sendupdates',.T.)
=DBSETPROP('v1','view','tables','kbtest1!test1')
=DBSETPROP('v1.fld1','field','keyfield',.T.)
=DBSETPROP('v1.fld1','field','updatable',.T.)
=DBSETPROP('v1.fld1','field','updatename','kbtest1!test1.fld1')
=DBSETPROP('v1.fld2','field','updatable',.T.)
=DBSETPROP('v1.fld2','field','updatename','kbtest1!test1.fld2')
=DBSETPROP('v1.fld3','field','updatable',.T.)
=DBSETPROP('v1.fld3','field','updatename','kbtest1!test1.fld3')


=DBSETPROP('v2','view','sendupdates',.T.)
=DBSETPROP('v2','view','tables','kbtest1!v1')
=DBSETPROP('v2.fld1','field','keyfield',.T.)
=DBSETPROP('v2.fld1','field','updatable',.T.)
=DBSETPROP('v2.fld1','field','updatename','kbtest1!v1.fld1')
=DBSETPROP('v2.fld2','field','updatable',.T.)
=DBSETPROP('v2.fld2','field','updatename','kbtest1!v1.fld2')
=DBSETPROP('v2.fld3','field','updatable',.T.)
=DBSETPROP('v2.fld3','field','updatename','kbtest1!v1.fld3')

USE v2
UPDATE v2 SET fld2=3.255
SELECT v2
BROWSE
?TABLEUPDATE()
BROWSE
SELECT v1
BROWSE
CLEAR ALL

SET SAFETY ON 

=TableRevert() 

STATUS

This behavior is by design.

MORE INFORMATION

Notice in the sample below that the Tables property is correctly set. The problem occurs when you do not specify the UpdateName property of the field or specify these incorrectly.

Steps to Reproduce Behavior

SET SAFETY OFF
CLOSE DATA ALL
CREATE DATA kbtest1
CREATE TABLE test1 (fld1 c(10) PRIMARY KEY, fld2 b(3),fld3 T)
INSERT INTO test1 VALUES ('1',2.322,DATETIME())
CREATE SQL VIEW v1 AS SELECT test1.fld1,test1.fld2,test1.fld3 FROM test1
CREATE SQL VIEW v2 AS SELECT v1.fld1,v1.fld2,v1.fld3 FROM v1

=DBSETPROP('v1','view','sendupdates',.T.)
=DBSETPROP('v1','view','tables','kbtest1!test1')
=DBSETPROP('v1.fld1','field','keyfield',.T.)
=DBSETPROP('v1.fld1','field','updatable',.T.)
=DBSETPROP('v1.fld2','field','updatable',.T.)
=DBSETPROP('v1.fld3','field','updatable',.T.)

=DBSETPROP('v2','view','sendupdates',.T.)
=DBSETPROP('v2','view','tables','kbtest1!v1')
=DBSETPROP('v2.fld1','field','keyfield',.T.)
=DBSETPROP('v2.fld1','field','updatable',.T.)
=DBSETPROP('v2.fld2','field','updatable',.T.)
=DBSETPROP('v2.fld3','field','updatable',.T.)

USE v2
UPDATE v2 SET fld2=3.255
SELECT v2
BROWSE
?TABLEUPDATE()
BROWSE
SELECT v1
BROWSE
CLEAR ALL

SET SAFETY ON 

Additional query words:

Keywords : kbDatabase kbVFp300b kbVFp500 kbVFp500a kbVFp600 KbDBFDBC kbGrpFox
Version : WINDOWS:3.0,3.0b,5.0,5.0a,6.0,6.0 SP3
Platform : WINDOWS
Issue type : kbprb