Cannot Add Rows to Updateable Outer Join

Last reviewed: December 20, 1996
Article ID: Q161358
The information in this article applies to:
  • Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0

SUMMARY

When a local view performing a Right Outer Join executes, the result set in the view's Browse shows .NULL. in all of the left-side table's fields where there is an unmatched row in the right-side table. If the view is defined with an updateable left-side table when data is entered into the primary key field of that table and the record pointer is moved, an information message box shows the error "Update conflict" with the "OK," "Revert," and "Help" options.

If data is entered into any of the other fields, you receive a "Syntax error" message. You can remove this by issuing the following command:

   =TableRevert(.T.)

If you modify only the primary key field and the "Update error" message appears, and if you click Revert, the row again shows .NULL. for all the fields in the left table portion of that row.

If you click OK, the command " = TableUpdate(0,.T.)" (without the quotes) is issued and that row in the view is marked as deleted.

If, then, the command "SET DELETED ON" is issued, the row disappears from the view. It reappears with the command "SET DELETED OFF."

MORE INFORMATION

The NULL row in the view is a "virtual row" with no correspondent in the base left-side table. If a forced update based only on the Primary Key fails, it indicates that the base-table row is inaccessible (as is the virtual row in the view). In this case Visual FoxPro deletes the conflicting row from the view. Because of the soft delete mechanism in Visual FoxPro, the user can control the visibility of deleted rows via the command SET DELETED ON /OFF.

Attempting to fill in the fields in the view from the left-side base table would require performing an INSERT... SQL for the full corresponding row in the left-side table.

REFERENCES

For more information about creating Outer Joins, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q124589
   TITLE     : How To Create a Left-right Outer Join in FoxPro for Windows


KBCategory: kbusage
KBSubcategory: FxprgSql FxprgTable FxprgSet
Additional reference words: 3.00 3.00b 5.00 kbdsd vfoxwin



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.

Last reviewed: December 20, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.