DOCUMENT:Q256925  27-JUL-2001  [foxpro]
TITLE   :BUG: INSERT INTO View w/Default Value Does Not Work as Expected
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:6.0
OPER/SYS:
KEYWORDS:kbDatabase kbSQL kbvfp600 KbDBFDBC kbGrpDSFox kbDSupport kbCodeSnippet kbSQLProg

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

 - Microsoft Visual FoxPro for Windows, version 6.0 
-------------------------------------------------------------------------------

SYMPTOMS
========

When using an updateable view to a table with a default value in the view but
not in the table, the value appears in the view but is not saved in the table.

RESOLUTION
==========

1. Use the APPEND BLANK command and then the REPLACE command instead of the
   (SQL) INSERT statement.
2. Specify the default value in the table instead of in the view.

STATUS
======

Microsoft has confirmed that this is a bug in the Microsoft products that are
listed at the beginning of this article.

MORE INFORMATION
================

Steps to Reproduce Behavior
---------------------------

Create a program and paste following code into file, then run the program:

   SET MULTILOCKS ON
   CLOSE ALL
   CLEAR
   ERASE TestDB.d??
   ERASE TestTable.*
   CREATE DATABASE TestDB
   CREATE TABLE TestTable (iID I, cData c(10))
   CREATE SQL VIEW "View1" AS SELECT * FROM TestDB!TestTable
   DBSETPROP('VIEW1', 'View', 'UpdateType', 1)
   DBSETPROP('VIEW1', 'View', 'WhereType', 3)
   DBSETPROP('VIEW1', 'View', 'FetchMemo', .T.)
   DBSETPROP('VIEW1', 'View', 'SendUpdates', .T.)
   DBSETPROP('VIEW1', 'View', 'UseMemoSize', 255)
   DBSETPROP('VIEW1', 'View', 'FetchSize', 100)
   DBSETPROP('VIEW1', 'View', 'MaxRecords', -1)
   DBSETPROP('VIEW1', 'View', 'Tables', 'testdb!TestTable')
   DBSETPROP('VIEW1', 'View', 'Prepared', .F.)
   DBSETPROP('VIEW1', 'View', 'CompareMemo', .T.)
   DBSETPROP('VIEW1', 'View', 'FetchAsNeeded', .F.)
   DBSETPROP('VIEW1', 'View', 'FetchSize', 100)
   DBSETPROP('VIEW1', 'View', 'Comment', "")
   DBSETPROP('VIEW1', 'View', 'BatchUpdateCount', 1)
   DBSETPROP('VIEW1', 'View', 'ShareConnection', .F.)
   *!* Field Level Properties for VIEW1
   * Props for the VIEW1.iid field.
   DBSETPROP('VIEW1.iid', 'Field', 'KeyField', .T.)
   DBSETPROP('VIEW1.iid', 'Field', 'Updatable', .T.)
   DBSETPROP('VIEW1.iid', 'Field', 'UpdateName', 'testdb!TestTable.iid')
   DBSETPROP('VIEW1.iid', 'Field', 'DataType', "I")
   DBSETPROP('VIEW1.iid', 'Field', 'DefaultValue', "RECCOUNT()+1")
   * Props for the VIEW1.cdata field.
   DBSETPROP('VIEW1.cdata', 'Field', 'KeyField', .F.)
   DBSETPROP('VIEW1.cdata', 'Field', 'Updatable', .T.)
   DBSETPROP('VIEW1.cdata', 'Field', 'UpdateName', 'testdb!TestTable.cdata')
   DBSETPROP('VIEW1.cdata', 'Field', 'DataType', "C(10)")
   CLOSE TABLES ALL
   SELECT 0
   USE View1
   * Insert with values for both fields
   INSERT INTO View1 (iID, cData) VALUES (1, "Test1")
   * Append Blank with Replace for cData only
   APPEND BLANK
   REPLACE cData WITH "Test2"
   * Insert with value for cData only
   INSERT INTO View1 (cData) VALUE ("Test3")
   ?
   ? "Listing of View1"
   LIST
   ?
   SELECT TestTable
   ? "Listing of TestTable"
   LIST
   CLOSE ALL 2
   ?
   ? "Reopening table"
   USE TestDB!View1
   ? "Listing of View1"
   LIST
   ?
   SELECT TestTable
   ? "Listing of TestTable"
   LIST

The program creates a database called TESTDB, a table called TestTable, and an
updateable view called View1. View1 contains a default value expression of
RECCOUNT()+1 for the iId field.

The program will then do the following:

 - INSERT a record into the view, supplying values for both fields
 - INSERT a record into the view, supplying only a value for the character field
   cData
 - APPEND BLANK and REPLACE cData with a value
 - List out the records in the view and the table
 - Close all files
 - Open the view
 - List out the records in the view and the table

In the first set of listings on the screen, the third record in View1 has a value
in the iId field while the iId field in the third record in TestTable is zero.
In the second set of listings, both View1 and TestTable have zero in the iId
field of the third record.

Additional query words:

======================================================================
Keywords          : kbDatabase kbSQL kbvfp600 KbDBFDBC kbGrpDSFox kbDSupport kbCodeSnippet kbSQLProg 
Technology        : kbVFPsearch kbAudDeveloper kbVFP600
Version           : WINDOWS:6.0
Issue type        : kbbug
Solution Type     : kbpending

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

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