DOCUMENT:Q231879  25-JUN-1999  [foxpro]
TITLE   :PRB: No Update Tables are Specified when Updating Records
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:3.0,3.0b,5.0,5.0a,6.0,6.0 SP3
OPER/SYS:
KEYWORDS:kbDatabase kbvfp300b kbvfp500 kbvfp500a kbvfp600 KbDBFDBC kbGrpDSFox

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - 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.

In some cases, you might not receive an error message, but the table may not be
updated.

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')

Sample:

   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

To clear the error, select the V2 view, and put the following code in the Command
window:

   =TableRevert()

Then close all the tables and views.

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
---------------------------

Run the following code to reproduce the problem:

   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 kbGrpDSFox 
Technology        : kbVFPsearch kbAudDeveloper kbVFP300 kbVFP300b kbVFP500 kbVFP600 kbVFP500a
Version           : WINDOWS:3.0,3.0b,5.0,5.0a,6.0,6.0 SP3
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1999.