DOCUMENT:Q195623 14-JUL-1999 [foxpro] TITLE :FIX: Index Corruption When Table is Used in Read-Only Mode PRODUCT :Microsoft FoxPro PROD/VER:WINDOWS:5.0,5.0a,6.0 OPER/SYS: KEYWORDS:kbDatabase kbvfp500 kbvfp500a kbvfp500aBUG kbvfp500bug kbvfp600 kbvfp600bug KbDBFDBC kb ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual FoxPro for Windows, versions 5.0, 5.0a, 6.0 ------------------------------------------------------------------------------- SYMPTOMS ======== Under a specific set of circumstances, the indexes of a table do not properly update when you append or insert records into a table that you have opened in a private data session of two or more forms. CAUSE ===== This behavior occurs with the following conditions and sequence of events: 1. A table is first USEd in read-only mode or with the NOUPDATE clause in a private data session of a form. 2. The same table is USEd in read/write mode in a private data session of a second form. 3. A new record is inserted into the table from the second form. 4. The following sequence of commands is executed in the second form: BEGIN TRANSACTION TABLEUPDATE() END TRANSACTION RESOLUTION ========== Depending on the needs of the developer, the following two workarounds can be used for this problem: - If it is not essential to USE tables in read-only mode or with the NOUPDATE clause, you might elect the following: - Use shared tables in the default data session -or- - Use shared tables in read/write mode in the open forms -OR- - If it is necessary or desirable to USE tables in read only mode or with the NOUPDATE clause, in one or more forms, the developer may elect the following: - Instantiate the forms using shared tables, opened in read-only mode, after instantiating forms USEing shared tables in read/write mode. -or- - Avoid issuing a TABLEUPDATE() command within a transaction. -or- - Issue a REINDEX command after closing the form(s) using the shared table. STATUS ====== Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. This bug was corrected in Visual Studio 6.0 Service Pack 3. For more information about Visual Studio service packs, please see the following articles in the Microsoft Knowledge Base: Q194022 INFO: Visual Studio 6.0 Service Packs, What, Where, Why Q194295 HOWTO: Tell That Visual Studio 6.0 Service Packs Are Installed MORE INFORMATION ================ Opening a table with the following syntax is functionally equivalent to opening the data environment of a form, selecting a table and setting the table's ReadOnly property to .T.: USE TABLE_NAME AGAIN NOUPDATE Steps to Reproduce Behavior --------------------------- 1. Create a program file named Cdxdemo.prg containing the following code: PUBLIC oa, ob * Trap the verion of VFP ver_number=VAL(SUBSTR(VERSION(),15,2)) oa=CREATEOBJECT('indexpra') ob=CREATEOBJECT('indexprb') oa.SHOW ob.SHOW READ EVENTS CLOSE ALL IF ver_number>5 * Visual FoxPro 6.x. * Set path to samples. USE HOME(2)+'data\customer' EXCL ELSE * Visual FoxPro 3.x or 5.x. * Set path to samples. USE home()+'samples\data\customer' EXCL ENDIF GO BOTT * Native Order, so the newly added record is visible. BROWSE ; TITLE "Native Order, New Record Visible" ; TIMEOUT 15 SET ORDER TO TAG cust_id GO TOP * Order is CUST_ID, but the newly added record is not visible BROW ; TITLE "CUST_ID Order, New Record Not Visible" ; TIMEOUT 15 * REINDEX will fix the problem. REINDEX GO TOP * Order is CUST_ID, and the newly added record is visible BROW ; TITLE "REINDEX CUST_ID Order, New Record Visible" ; TIMEOUT 15 CLOSE ALL DEFINE CLASS indexpra AS FORM DATASESSION = 2 DOCREATE = .T. BUFFERMODE = 2 CAPTION = "Form1" NAME = "Form1" CLOSABLE=.F. MAXBUTTON=.F. MINBUTTON=.F. PROCEDURE INIT SET MULTILOCKS ON IF ver_number>5 USE HOME(2)+'data\customer' AGAIN NOUPDATE ELSE USE home()+'samples\data\customer' AGAIN NOUPDATE ENDIF ENDPROC ENDDEFINE DEFINE CLASS indexprb AS FORM DATASESSION = 2 DOCREATE = .T. BUFFERMODE = 2 CAPTION = "Form1" NAME = "Form1" AUTOCENTER=.T. CLOSABLE=.F. MAXBUTTON=.F. MINBUTTON=.F. ADD OBJECT command1 AS COMMANDBUTTON WITH ; TOP = 36, ; LEFT = 96, ; HEIGHT = 37, ; WIDTH = 193, ; CAPTION = "Add Record", ; NAME = "Command1" ADD OBJECT command2 AS COMMANDBUTTON WITH ; TOP = 96, ; LEFT = 96, ; HEIGHT = 37, ; WIDTH = 193, ; CAPTION = "Save Record & Close", ; NAME = "Command2", ; ENABLED=.F. PROCEDURE command1.CLICK INSERT INTO customer (cust_id,company, contact, TITLE, ; Address, city, REGION, postalcode, country, phone, ; fax, maxordamt) ; VALUES ("0000"+ALLTRIM(STR(RECCOUNT()+1,10)), ; "Company "+ ALLTRIM(STR(RECCOUNT()+1,10)), ; "Contact " + ALLTRIM(STR(RECCOUNT()+1,10)), ; "Title "+ ALLTRIM(STR(RECCOUNT()+1,10)), ; "Address " + ALLTRIM(STR(RECCOUNT()+1,10)), ; "City "+ ALLTRIM(STR(RECCOUNT()+1,10)), ; "Region " + ALLTRIM(STR(RECCOUNT()+1,10)), ; "Post "+ ALLTRIM(STR(RECCOUNT()+1,10)), ; "Country " + ALLTRIM(STR(RECCOUNT()+1,10)), ; "Phone "+ ALLTRIM(STR(RECCOUNT()+1,10)), ; "Fax " + ALLTRIM(STR(RECCOUNT()+1,10)), ; RECCOUNT()+1) THIS.ENABLED=.F. THISFORM.COMMAND2.ENABLED=.T. THISFORM.REFRESH ENDPROC PROCEDURE command2.CLICK LOCAL llSuccess BEGIN TRANSACTION IF TABLEUPDATE(.T.,.T.,"Customer") END TRANSACTION WAIT WINDOW "Update Succeeded" NOWAIT ELSE ROLLBACK WAIT WINDOW "Update Failed" NOWAIT ENDIF oa.RELEASE THISFORM.RELEASE CLEAR EVENTS ENDPROC PROCEDURE INIT SET MULTILOCKS ON IF ver_number>5 USE HOME(2)+'data\customer' AGAIN ELSE USE home()+'samples\data\customer' AGAIN ENDIF =CURSORSETPROP('BUFFERING',3,'Customer') ENDPROC ENDDEFINE 2. In the Command window type the following: DO CDXDEMO 3. Click the command button labeled "Add Record." 4. Click the command button labeled "Save Record & Close." 5. Note in the first Browse window that appears that a new record has been added to the Customer table. 6. Note that in the second Browse window, with the index order set to the CUST_ID tag, the newly-added record is not visible. 7. Note that in the third Browse window, after a REINDEX command is issued, that the newly-added record is visible. 8. To USE the shared tables in the default data session, comment the DataSession property of both forms or change the DataSession property of both forms to one (1). Run the program again, and note that the indexes update properly. 9. To USE the shared tables in read/write mode in both open forms, comment the NOUPDATE clause of the USE command in the Init event of the class definition for Indexpra. Run the program again and note that the indexes update properly. (c) Microsoft Corporation 1999, All Rights Reserved. Contributions by John Desch, Microsoft Corporation Additional query words: ====================================================================== Keywords : kbDatabase kbvfp500 kbvfp500a kbvfp500aBUG kbvfp500bug kbvfp600 kbvfp600bug KbDBFDBC kbVS600sp3fix kbGrpDSFox Technology : kbVFPsearch kbAudDeveloper kbVFP500 kbVFP600 kbVFP500a Version : WINDOWS:5.0,5.0a,6.0 Issue type : kbbug Solution Type : kbfix ============================================================================= 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.