Microsoft KB Archive/169367: Difference between revisions

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - """ to """)
 
Line 131: Line 131:
   ***** Change properties for GOLF *****
   ***** Change properties for GOLF *****
   ***************** View setup for VGOLF ***************
   ***************** View setup for VGOLF ***************
   CREATE SQL VIEW "VGOLF" ;
   CREATE SQL VIEW "VGOLF" ;
       AS SELECT * FROM test24!golf
       AS SELECT * FROM test24!golf
   ****
   ****
Line 150: Line 150:
   =DBSetProp('VGOLF.id', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.id', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.id', 'Field', 'UpdateName', 'test24!golf.id')
   =DBSetProp('VGOLF.id', 'Field', 'UpdateName', 'test24!golf.id')
   =DBSETPROP("VGOLF.id", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.id", "Field", "DataType", "C(10)")
   * Props for the VGOLF.fname field.
   * Props for the VGOLF.fname field.
   =DBSetProp('VGOLF.fname', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.fname', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.fname', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.fname', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.fname', 'Field', 'UpdateName', 'test24!golf.fname')
   =DBSetProp('VGOLF.fname', 'Field', 'UpdateName', 'test24!golf.fname')
   =DBSETPROP("VGOLF.fname", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.fname", "Field", "DataType", "C(10)")
   * Props for the VGOLF.lname field.
   * Props for the VGOLF.lname field.
   =DBSetProp('VGOLF.lname', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.lname', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.lname', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.lname', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.lname', 'Field', 'UpdateName', 'test24!golf.lname')
   =DBSetProp('VGOLF.lname', 'Field', 'UpdateName', 'test24!golf.lname')
   =DBSETPROP("VGOLF.lname", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.lname", "Field", "DataType", "C(10)")
   * Props for the VGOLF.address field.
   * Props for the VGOLF.address field.
   =DBSetProp('VGOLF.address', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.address', 'Field', 'KeyField', .F.)
Line 166: Line 166:
   =DBSetProp('VGOLF.address', 'Field', 'UpdateName', ;
   =DBSetProp('VGOLF.address', 'Field', 'UpdateName', ;
   'test24!golf.address')
   'test24!golf.address')
   =DBSETPROP("VGOLF.address", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.address", "Field", "DataType", "C(10)")
   * Props for the VGOLF.city field.
   * Props for the VGOLF.city field.
   =DBSetProp('VGOLF.city', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.city', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.city', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.city', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.city', 'Field', 'UpdateName', 'test24!golf.city')
   =DBSetProp('VGOLF.city', 'Field', 'UpdateName', 'test24!golf.city')
   =DBSETPROP("VGOLF.city", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.city", "Field", "DataType", "C(10)")
   * Props for the VGOLF.state field.
   * Props for the VGOLF.state field.
   =DBSetProp('VGOLF.state', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.state', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.state', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.state', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.state', 'Field', 'UpdateName', 'test24!golf.state')
   =DBSetProp('VGOLF.state', 'Field', 'UpdateName', 'test24!golf.state')
   =DBSETPROP("VGOLF.state", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.state", "Field", "DataType", "C(10)")
   * Props for the VGOLF.zip field.
   * Props for the VGOLF.zip field.
   =DBSetProp('VGOLF.zip', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.zip', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.zip', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.zip', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.zip', 'Field', 'UpdateName', 'test24!golf.zip')
   =DBSetProp('VGOLF.zip', 'Field', 'UpdateName', 'test24!golf.zip')
   =DBSETPROP("VGOLF.zip", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.zip", "Field", "DataType", "C(10)")
   * Props for the VGOLF.handicap field.
   * Props for the VGOLF.handicap field.
   =DBSetProp('VGOLF.handicap', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.handicap', 'Field', 'KeyField', .F.)
Line 187: Line 187:
   =DBSetProp('VGOLF.handicap', 'Field', 'UpdateName', ;
   =DBSetProp('VGOLF.handicap', 'Field', 'UpdateName', ;
   'test24!golf.handicap')
   'test24!golf.handicap')
   =DBSETPROP("VGOLF.handicap", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.handicap", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole1 field.
   * Props for the VGOLF.hole1 field.
   =DBSetProp('VGOLF.hole1', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole1', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole1', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole1', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole1', 'Field', 'UpdateName', 'test24!golf.hole1')
   =DBSetProp('VGOLF.hole1', 'Field', 'UpdateName', 'test24!golf.hole1')
   =DBSETPROP("VGOLF.hole1", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole1", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole2 field.
   * Props for the VGOLF.hole2 field.
   =DBSetProp('VGOLF.hole2', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole2', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole2', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole2', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole2', 'Field', 'UpdateName', 'test24!golf.hole2')
   =DBSetProp('VGOLF.hole2', 'Field', 'UpdateName', 'test24!golf.hole2')
   =DBSETPROP("VGOLF.hole2", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole2", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole3 field.
   * Props for the VGOLF.hole3 field.
   =DBSetProp('VGOLF.hole3', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole3', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole3', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole3', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole3', 'Field', 'UpdateName', 'test24!golf.hole3')
   =DBSetProp('VGOLF.hole3', 'Field', 'UpdateName', 'test24!golf.hole3')
   =DBSETPROP("VGOLF.hole3", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole3", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole4 field.
   * Props for the VGOLF.hole4 field.
   =DBSetProp('VGOLF.hole4', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole4', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole4', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole4', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole4', 'Field', 'UpdateName', 'test24!golf.hole4')
   =DBSetProp('VGOLF.hole4', 'Field', 'UpdateName', 'test24!golf.hole4')
   =DBSETPROP("VGOLF.hole4", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole4", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole5 field.
   * Props for the VGOLF.hole5 field.
   =DBSetProp('VGOLF.hole5', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole5', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole5', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole5', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole5', 'Field', 'UpdateName', 'test24!golf.hole5')
   =DBSetProp('VGOLF.hole5', 'Field', 'UpdateName', 'test24!golf.hole5')
   =DBSETPROP("VGOLF.hole5", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole5", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole6 field.
   * Props for the VGOLF.hole6 field.
   =DBSetProp('VGOLF.hole6', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole6', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole6', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole6', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole6', 'Field', 'UpdateName', 'test24!golf.hole6')
   =DBSetProp('VGOLF.hole6', 'Field', 'UpdateName', 'test24!golf.hole6')
   =DBSETPROP("VGOLF.hole6", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole6", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole7 field.
   * Props for the VGOLF.hole7 field.
   =DBSetProp('VGOLF.hole7', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole7', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole7', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole7', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole7', 'Field', 'UpdateName', 'test24!golf.hole7')
   =DBSetProp('VGOLF.hole7', 'Field', 'UpdateName', 'test24!golf.hole7')
   =DBSETPROP("VGOLF.hole7", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole7", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole8 field.
   * Props for the VGOLF.hole8 field.
   =DBSetProp('VGOLF.hole8', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole8', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole8', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole8', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole8', 'Field', 'UpdateName', 'test24!golf.hole8')
   =DBSetProp('VGOLF.hole8', 'Field', 'UpdateName', 'test24!golf.hole8')
   =DBSETPROP("VGOLF.hole8", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole8", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole9 field.
   * Props for the VGOLF.hole9 field.
   =DBSetProp('VGOLF.hole9', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole9', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole9', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole9', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole9', 'Field', 'UpdateName', 'test24!golf.hole9')
   =DBSetProp('VGOLF.hole9', 'Field', 'UpdateName', 'test24!golf.hole9')
   =DBSETPROP("VGOLF.hole9", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole9", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole10 field.
   * Props for the VGOLF.hole10 field.
   =DBSetProp('VGOLF.hole10', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole10', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole10', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole10', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole10', 'Field', 'UpdateName', 'test24!golf.hole10')
   =DBSetProp('VGOLF.hole10', 'Field', 'UpdateName', 'test24!golf.hole10')
   =DBSETPROP("VGOLF.hole10", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole10", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole11 field.
   * Props for the VGOLF.hole11 field.
   =DBSetProp('VGOLF.hole11', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole11', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole11', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole11', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole11', 'Field', 'UpdateName', 'test24!golf.hole11')
   =DBSetProp('VGOLF.hole11', 'Field', 'UpdateName', 'test24!golf.hole11')
   =DBSETPROP("VGOLF.hole11", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole11", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole12 field.
   * Props for the VGOLF.hole12 field.
   =DBSetProp('VGOLF.hole12', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole12', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole12', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole12', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole12', 'Field', 'UpdateName', 'test24!golf.hole12')
   =DBSetProp('VGOLF.hole12', 'Field', 'UpdateName', 'test24!golf.hole12')
   =DBSETPROP("VGOLF.hole12", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole12", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole13 field.
   * Props for the VGOLF.hole13 field.
   =DBSetProp('VGOLF.hole13', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole13', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole13', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole13', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole13', 'Field', 'UpdateName', 'test24!golf.hole13')
   =DBSetProp('VGOLF.hole13', 'Field', 'UpdateName', 'test24!golf.hole13')
   =DBSETPROP("VGOLF.hole13", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole13", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole14 field.
   * Props for the VGOLF.hole14 field.
   =DBSetProp('VGOLF.hole14', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole14', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole14', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole14', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole14', 'Field', 'UpdateName', 'test24!golf.hole14')
   =DBSetProp('VGOLF.hole14', 'Field', 'UpdateName', 'test24!golf.hole14')
   =DBSETPROP("VGOLF.hole14", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole14", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole15 field.
   * Props for the VGOLF.hole15 field.
   =DBSetProp('VGOLF.hole15', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole15', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole15', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole15', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole15', 'Field', 'UpdateName', 'test24!golf.hole15')
   =DBSetProp('VGOLF.hole15', 'Field', 'UpdateName', 'test24!golf.hole15')
   =DBSETPROP("VGOLF.hole15", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole15", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole16 field.
   * Props for the VGOLF.hole16 field.
   =DBSetProp('VGOLF.hole16', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole16', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole16', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole16', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole16', 'Field', 'UpdateName', 'test24!golf.hole16')
   =DBSetProp('VGOLF.hole16', 'Field', 'UpdateName', 'test24!golf.hole16')
   =DBSETPROP("VGOLF.hole16", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole16", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole17 field.
   * Props for the VGOLF.hole17 field.
   =DBSetProp('VGOLF.hole17', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole17', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole17', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole17', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole17', 'Field', 'UpdateName', 'test24!golf.hole17')
   =DBSetProp('VGOLF.hole17', 'Field', 'UpdateName', 'test24!golf.hole17')
   =DBSETPROP("VGOLF.hole17", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole17", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole18 field.
   * Props for the VGOLF.hole18 field.
   =DBSetProp('VGOLF.hole18', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole18', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole18', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole18', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole18', 'Field', 'UpdateName', 'test24!golf.hole18')
   =DBSetProp('VGOLF.hole18', 'Field', 'UpdateName', 'test24!golf.hole18')
   =DBSETPROP("VGOLF.hole18", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.hole18", "Field", "DataType", "C(10)")
   * Props for the VGOLF.test field.
   * Props for the VGOLF.test field.
   =DBSetProp('VGOLF.test', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.test', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.test', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.test', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.test', 'Field', 'UpdateName', 'test24!golf.test')
   =DBSetProp('VGOLF.test', 'Field', 'UpdateName', 'test24!golf.test')
   =DBSETPROP("VGOLF.test", "Field", "DataType", "C(10)")
   =DBSETPROP("VGOLF.test", "Field", "DataType", "C(10)")


   * ----------------------------------------------------------------
   * ----------------------------------------------------------------
Line 288: Line 288:
   USE VGolf
   USE VGolf
   BROWSE NOWAIT
   BROWSE NOWAIT
   WAIT WINDOW TIME 3 "About to insert a new record into the view."
   WAIT WINDOW TIME 3 "About to insert a new record into the view."


   * Create one blank record *
   * Create one blank record *
Line 294: Line 294:
   * Fill in first record *
   * Fill in first record *
   FOR i = 1 TO FCOUNT()
   FOR i = 1 TO FCOUNT()
     REPLACE (FIELD(i)) WITH "I"
     REPLACE (FIELD(i)) WITH "I"
   ENDFOR
   ENDFOR


   l_lSuccess = TABLEUPDATE()
   l_lSuccess = TABLEUPDATE()
   SHOW WINDOW VGolf REFRESH SAME
   SHOW WINDOW VGolf REFRESH SAME
   WAIT WINDOW TIME 3 IIF(l_lSuccess, "Insert sucessfully complete", ;
   WAIT WINDOW TIME 3 IIF(l_lSuccess, "Insert sucessfully complete", ;
   "Insert Failed, here's why...")
   "Insert Failed, here's why...")


   IF l_lSuccess
   IF l_lSuccess
     WAIT WINDOW TIME 3 "Now here comes an update of 27 fields."
     WAIT WINDOW TIME 3 "Now here comes an update of 27 fields."
     FOR i = 1 TO FCOUNT()
     FOR i = 1 TO FCOUNT()
       REPLACE (FIELD(i)) WITH "U"
       REPLACE (FIELD(i)) WITH "U"
     ENDFOR
     ENDFOR


     l_lSuccess = TABLEUPDATE()
     l_lSuccess = TABLEUPDATE()
     SHOW WINDOW VGolf REFRESH SAME
     SHOW WINDOW VGolf REFRESH SAME
     WAIT WINDOW TIME 3 IIF(l_lSuccess, "Update successful", "Update ;
     WAIT WINDOW TIME 3 IIF(l_lSuccess, "Update successful", "Update ;
     Failed, here's why...")
     Failed, here's why...")
   ENDIF
   ENDIF


Line 317: Line 317:
     DIMENSION g_aError[2]
     DIMENSION g_aError[2]
     =AERROR(g_aerror)
     =AERROR(g_aerror)
     =MESSAGEBOX("Error " + STR(g_aerror[1]) + " " + g_aerror[2])
     =MESSAGEBOX("Error " + STR(g_aerror[1]) + " " + g_aerror[2])
     WAIT WINDOW TIME 2 "Reverting the update..."
     WAIT WINDOW TIME 2 "Reverting the update..."
     =TABLEREVERT()
     =TABLEREVERT()
   ENDIF
   ENDIF


   WAIT WINDOW TIME 2 "Test complete..."
   WAIT WINDOW TIME 2 "Test complete..."
                     </pre>
                     </pre>



Latest revision as of 10:04, 21 July 2020

Article ID: 169367

Article Last Modified on 3/23/2000



APPLIES TO

  • Microsoft Visual FoxPro 5.0 Standard Edition
  • Microsoft Visual FoxPro 5.0a



This article was previously published under Q169367

SYMPTOMS

When you try to update more than 23 columns of a view, the update fails and the following error message appears:

Error 1812 SQL: Statement too long

CAUSE

There is a fixed limit to how complex the WHERE clause of a query can be. It is limited to 200 simple expression elements or less (where one element is a variable reference, or a function call, etc.). With the WhereType view property set to KeyAndModified, and then updating all the fields will generate an UPDATE query that exceeds the WHERE clause limits.

RESOLUTION

Setting the WhereType of the View to Key (1) instead of KeyAndModified (3) resolves this problem.

In the sample code in the Steps to Reproduce Behavior below, change the following line of code:

   =DBSetProp('VGOLF', 'View', 'WhereType', 3)
                

to:

   =DBSetProp('VGOLF', 'View', 'WhereType', 1)
                

It is the 5th line below:

                                  • View setup for VGOLF ***************


After making the change, rerun the program and the update will be successful.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. This has been corrected in Visual FoxPro 6.0.

MORE INFORMATION

Steps to Reproduce Behavior

Place the following code into a program file and then run the program:

   CLOSE DATA ALL
   CREATE DATABASE 'TEST24.DBC'

   ***** Table setup for GOLF *****
   CREATE TABLE 'GOLF.DBF' NAME 'GOLF' (ID C(10) NOT NULL, ;
                      FNAME C(10) NOT NULL, ;
                      LNAME C(10) NOT NULL, ;
                      ADDRESS C(10) NOT NULL, ;
                      CITY C(10) NOT NULL, ;
                      STATE C(10) NOT NULL, ;
                      ZIP C(10) NOT NULL, ;
                      HANDICAP C(10) NOT NULL, ;
                      HOLE1 C(10) NOT NULL, ;
                      HOLE2 C(10) NOT NULL, ;
                      HOLE3 C(10) NOT NULL, ;
                      HOLE4 C(10) NOT NULL, ;
                      HOLE5 C(10) NOT NULL, ;
                      HOLE6 C(10) NOT NULL, ;
                      HOLE7 C(10) NOT NULL, ;
                      HOLE8 C(10) NOT NULL, ;
                      HOLE9 C(10) NOT NULL, ;
                      HOLE10 C(10) NOT NULL, ;
                      HOLE11 C(10) NOT NULL, ;
                      HOLE12 C(10) NOT NULL, ;
                      HOLE13 C(10) NOT NULL, ;
                      HOLE14 C(10) NOT NULL, ;
                      HOLE15 C(10) NOT NULL, ;
                      HOLE16 C(10) NOT NULL, ;
                      HOLE17 C(10) NOT NULL, ;
                      HOLE18 C(10) NOT NULL, ;
                      TEST C(10) NOT NULL)

   ***** Create each index for GOLF *****
   SET COLLATE TO 'MACHINE'
   ALTER TABLE 'GOLF' ADD PRIMARY KEY ID TAG PKEY

   ***** Change properties for GOLF *****
   ***************** View setup for VGOLF ***************
   CREATE SQL VIEW "VGOLF" ;
      AS SELECT * FROM test24!golf
   ****
   =DBSetProp('VGOLF', 'View', 'UpdateType', 1)
   =DBSetProp('VGOLF', 'View', 'WhereType', 3)
   =DBSetProp('VGOLF', 'View', 'FetchMemo', .T.)
   =DBSetProp('VGOLF', 'View', 'SendUpdates', .T.)
   =DBSetProp('VGOLF', 'View', 'FetchSize', 100)
   =DBSetProp('VGOLF', 'View', 'MaxRecords', -1)
   =DBSetProp('VGOLF', 'View', 'Tables', 'test24!golf')
   =DBSetProp('VGOLF', 'View', 'Prepared', .F.)
   =DBSetProp('VGOLF', 'View', 'CompareMemo', .T.)
   =DBSetProp('VGOLF', 'View', 'FetchAsNeeded', .F.)

   *! Field Level Properties for VGOLF
   * Props for the VGOLF.id field.
   =DBSetProp('VGOLF.id', 'Field', 'KeyField', .T.)
   =DBSetProp('VGOLF.id', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.id', 'Field', 'UpdateName', 'test24!golf.id')
   =DBSETPROP("VGOLF.id", "Field", "DataType", "C(10)")
   * Props for the VGOLF.fname field.
   =DBSetProp('VGOLF.fname', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.fname', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.fname', 'Field', 'UpdateName', 'test24!golf.fname')
   =DBSETPROP("VGOLF.fname", "Field", "DataType", "C(10)")
   * Props for the VGOLF.lname field.
   =DBSetProp('VGOLF.lname', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.lname', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.lname', 'Field', 'UpdateName', 'test24!golf.lname')
   =DBSETPROP("VGOLF.lname", "Field", "DataType", "C(10)")
   * Props for the VGOLF.address field.
   =DBSetProp('VGOLF.address', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.address', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.address', 'Field', 'UpdateName', ;
   'test24!golf.address')
   =DBSETPROP("VGOLF.address", "Field", "DataType", "C(10)")
   * Props for the VGOLF.city field.
   =DBSetProp('VGOLF.city', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.city', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.city', 'Field', 'UpdateName', 'test24!golf.city')
   =DBSETPROP("VGOLF.city", "Field", "DataType", "C(10)")
   * Props for the VGOLF.state field.
   =DBSetProp('VGOLF.state', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.state', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.state', 'Field', 'UpdateName', 'test24!golf.state')
   =DBSETPROP("VGOLF.state", "Field", "DataType", "C(10)")
   * Props for the VGOLF.zip field.
   =DBSetProp('VGOLF.zip', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.zip', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.zip', 'Field', 'UpdateName', 'test24!golf.zip')
   =DBSETPROP("VGOLF.zip", "Field", "DataType", "C(10)")
   * Props for the VGOLF.handicap field.
   =DBSetProp('VGOLF.handicap', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.handicap', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.handicap', 'Field', 'UpdateName', ;
   'test24!golf.handicap')
   =DBSETPROP("VGOLF.handicap", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole1 field.
   =DBSetProp('VGOLF.hole1', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole1', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole1', 'Field', 'UpdateName', 'test24!golf.hole1')
   =DBSETPROP("VGOLF.hole1", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole2 field.
   =DBSetProp('VGOLF.hole2', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole2', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole2', 'Field', 'UpdateName', 'test24!golf.hole2')
   =DBSETPROP("VGOLF.hole2", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole3 field.
   =DBSetProp('VGOLF.hole3', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole3', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole3', 'Field', 'UpdateName', 'test24!golf.hole3')
   =DBSETPROP("VGOLF.hole3", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole4 field.
   =DBSetProp('VGOLF.hole4', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole4', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole4', 'Field', 'UpdateName', 'test24!golf.hole4')
   =DBSETPROP("VGOLF.hole4", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole5 field.
   =DBSetProp('VGOLF.hole5', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole5', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole5', 'Field', 'UpdateName', 'test24!golf.hole5')
   =DBSETPROP("VGOLF.hole5", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole6 field.
   =DBSetProp('VGOLF.hole6', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole6', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole6', 'Field', 'UpdateName', 'test24!golf.hole6')
   =DBSETPROP("VGOLF.hole6", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole7 field.
   =DBSetProp('VGOLF.hole7', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole7', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole7', 'Field', 'UpdateName', 'test24!golf.hole7')
   =DBSETPROP("VGOLF.hole7", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole8 field.
   =DBSetProp('VGOLF.hole8', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole8', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole8', 'Field', 'UpdateName', 'test24!golf.hole8')
   =DBSETPROP("VGOLF.hole8", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole9 field.
   =DBSetProp('VGOLF.hole9', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole9', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole9', 'Field', 'UpdateName', 'test24!golf.hole9')
   =DBSETPROP("VGOLF.hole9", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole10 field.
   =DBSetProp('VGOLF.hole10', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole10', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole10', 'Field', 'UpdateName', 'test24!golf.hole10')
   =DBSETPROP("VGOLF.hole10", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole11 field.
   =DBSetProp('VGOLF.hole11', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole11', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole11', 'Field', 'UpdateName', 'test24!golf.hole11')
   =DBSETPROP("VGOLF.hole11", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole12 field.
   =DBSetProp('VGOLF.hole12', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole12', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole12', 'Field', 'UpdateName', 'test24!golf.hole12')
   =DBSETPROP("VGOLF.hole12", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole13 field.
   =DBSetProp('VGOLF.hole13', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole13', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole13', 'Field', 'UpdateName', 'test24!golf.hole13')
   =DBSETPROP("VGOLF.hole13", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole14 field.
   =DBSetProp('VGOLF.hole14', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole14', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole14', 'Field', 'UpdateName', 'test24!golf.hole14')
   =DBSETPROP("VGOLF.hole14", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole15 field.
   =DBSetProp('VGOLF.hole15', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole15', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole15', 'Field', 'UpdateName', 'test24!golf.hole15')
   =DBSETPROP("VGOLF.hole15", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole16 field.
   =DBSetProp('VGOLF.hole16', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole16', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole16', 'Field', 'UpdateName', 'test24!golf.hole16')
   =DBSETPROP("VGOLF.hole16", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole17 field.
   =DBSetProp('VGOLF.hole17', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole17', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole17', 'Field', 'UpdateName', 'test24!golf.hole17')
   =DBSETPROP("VGOLF.hole17", "Field", "DataType", "C(10)")
   * Props for the VGOLF.hole18 field.
   =DBSetProp('VGOLF.hole18', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.hole18', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.hole18', 'Field', 'UpdateName', 'test24!golf.hole18')
   =DBSETPROP("VGOLF.hole18", "Field", "DataType", "C(10)")
   * Props for the VGOLF.test field.
   =DBSetProp('VGOLF.test', 'Field', 'KeyField', .F.)
   =DBSetProp('VGOLF.test', 'Field', 'Updatable', .T.)
   =DBSetProp('VGOLF.test', 'Field', 'UpdateName', 'test24!golf.test')
   =DBSETPROP("VGOLF.test", "Field", "DataType", "C(10)")

   * ----------------------------------------------------------------
   CLEAR
   USE VGolf
   BROWSE NOWAIT
   WAIT WINDOW TIME 3 "About to insert a new record into the view."

   * Create one blank record *
   APPEND BLANK
   * Fill in first record *
   FOR i = 1 TO FCOUNT()
     REPLACE (FIELD(i)) WITH "I"
   ENDFOR

   l_lSuccess = TABLEUPDATE()
   SHOW WINDOW VGolf REFRESH SAME
   WAIT WINDOW TIME 3 IIF(l_lSuccess, "Insert sucessfully complete", ;
   "Insert Failed, here's why...")

   IF l_lSuccess
     WAIT WINDOW TIME 3 "Now here comes an update of 27 fields."
     FOR i = 1 TO FCOUNT()
       REPLACE (FIELD(i)) WITH "U"
     ENDFOR

     l_lSuccess = TABLEUPDATE()
     SHOW WINDOW VGolf REFRESH SAME
     WAIT WINDOW TIME 3 IIF(l_lSuccess, "Update successful", "Update ;
     Failed, here's why...")
   ENDIF

   IF !l_lSuccess
     DIMENSION g_aError[2]
     =AERROR(g_aerror)
     =MESSAGEBOX("Error " + STR(g_aerror[1]) + " " + g_aerror[2])
     WAIT WINDOW TIME 2 "Reverting the update..."
     =TABLEREVERT()
   ENDIF

   WAIT WINDOW TIME 2 "Test complete..."
                    


Additional query words: kbvfp600fix

Keywords: kbbug kbfix KB169367