Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
TableUpdate() uses Insert, not Update, with compound key
Message
 
 
À
23/11/2011 11:00:24
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Versions des environnements
Visual FoxPro:
VFP 9 SP2
Database:
MS SQL Server
Divers
Thread ID:
01529677
Message ID:
01529701
Vues:
56
The difference I can see by the quick comparison between our routines, that we include PK fields in the update list. Here is relevant piece of code:
llIsIdentity  = .F.
llHasIdentity = ! empty(trim(csrKeyFields.identcols))
select (lcTable)

for lnI=1 to fcount()				&&make a list of all fields in the view
	if ! left(trim(upper(field(lnI))),3) == 'NUP_'   && ignore any field (do not add to list) any field starting with 'nup_', which stands for "no update"
	  if llHasIdentity					&&don't include identity columns as updatable
	    llIsIdentity = (upper(trim(csrKeyFields.identcols))==upper(trim(field(lnI))))
	    if llIsIdentity
				cursorsetprop('AutoIncError',.F.)  && ignore error message when updating identity field
	      ** loop
	    endif
	  endif

	  if ! llIsIdentity
		  lcFieldList=lcFieldList+field(lnI)+','
		endif
	  lcUpdList=lcUpdList+field(lnI)+' dbo.'+lcTable+'.'+field(lnI)+','
	endif
endfor
&&trim off that last ","
lcFieldList=substr(lcFieldList, 1, len(lcFieldList)-1)
lcUpdList=substr(lcUpdList, 1, len(lcUpdList)-1)

if !CursorSetProp('UpdateNameList', lcUpdList)
  messagebox("CursorSetProp('UpdateNameList', lcUpdList) failed."+chr(13)+chr(10)+;
   "lcUpdList="+lcUpdList, 0, "Error Making View Updatable")
endif
if !CursorSetProp('KeyFieldList', trim(csrKeyFields.pkeycols))
  messagebox("CursorSetProp('KeyFieldList', csrIDX2.index_keys) failed."+chr(13)+chr(10)+;
   "csrIDX2.index_keys="+csrIDX2.index_keys, 0, "Error Making View Updatable")
endif
if !CursorSetProp('UpdatableFieldList', lcFieldList)
  messagebox("CursorSetProp('UpdatableFieldList', lcFieldList) failed." + chr(13) + chr(10) + ;
   "lcFieldList="+lcFieldList, 0, "Error Making View Updatable")
endif
if !CursorSetProp('SendUpdates', .T.)
  messagebox("CursorSetProp('SendUpdates', .T.) failed.", ;
   0, "Error Making View Updatable")
endif
if !CursorSetProp('UpdateType', 1)
  messagebox("CursorSetProp('UpdateType', 1) failed.", ;
   0, "Error Making View Updatable")
endif

if vartype(tnBufferMode)='N' and between(tnBufferMode, 3, 5)
  if !CursorSetProp('Buffering', tnBufferMode)
    messagebox("CursorSetProp('Buffering', tnBufferMode) failed."+chr(13)+chr(10)+;
     "tnBufferMode=" + transform(tnBufferMode), 0, "Error Making View Updatable")
  endif
else
  if !CursorSetProp('Buffering', 3)			&&default to optimistic row...
    messagebox("CursorSetProp('Buffering', 3) failed.",;
     0, "Error Making View Updatable")
  endif
endif

cursorsetprop('CompareMemo',.F.)  			&&updating mug_shot if one exists causes update conflict
use in select('csrKeyFields')
Also, in our case we always use a select from just one table. I think I recently tried to support multiple tables (where only one will be updatable), but my code modifications didn't work.

>Here's my routine that makes the cursor updateable.
>
>
>LPARAMETERS tcTable, tcAlias, tlHasCalcFields, tcSpecialFields
>
>LOCAL cPK, lPKIsIdentity, lSuccess
>
>lSuccess = .T.
>
>CURSORSETPROP("Tables", "dbo." + m.tcTable, m.tcAlias)
>CURSORSETPROP("WhereType", 1, m.tcAlias)
>
>* Find PK
>cPK = This.GetPK(m.tcTable)
>IF NOT EMPTY(m.cPK)
>	CURSORSETPROP("KeyFieldList", m.cPK, m.tcAlias)
>	* Only need to check for identity column if
>	* the PK is a single field.
>	IF NOT "," $ m.cPK
>		lPKIsIdentity = This.IsIdentity(m.cPK, m.tcTable)			
>	ELSE
>		lPKIsIdentity = .F.
>	ENDIF 
>ENDIF 
>
>LOCAL cFieldList, cUpdateNameList, aFieldList[1], nFields, nField
>
>nFields = AFIELDS(aFieldList, m.tcAlias)
>cFieldList = ""
>cUpdateNameList = ""
>
>IF m.tlHasCalcFields
>	IF SQLCOLUMNS(This.nHandle, m.tcTable, "FOXPRO", "csrColumns") <> 1
>		lSuccess = .F.
>	ENDIF
>ENDIF 
>		
>IF m.lSuccess
>
>	FOR nField = 1 TO m.nFields
>		* Modified 19-October-2011 by TEG
>		* If necessary, check whether the field exists in the table.
>		IF m.tlHasCalcFields
>			SELECT csrColumns
>			LOCATE FOR UPPER(csrColumns.Field_Name) = UPPER(aFieldList[m.nField, 1])
>			IF NOT FOUND("csrColumns")
>				LOOP
>			ENDIF 
>		ENDIF 
>
>		IF NOT (UPPER(aFieldList[m.nField, 1]) == UPPER(m.cPK)) OR ;
>		   NOT m.lPKIsIdentity
>			* Don't put identity field in list of updateable fields
>			* Wrap field name in brackets in case it's a reserved word.
>			cFieldList = m.cFieldList + ", " + aFieldList[m.nField, 1] 
>		ENDIF 
>		cUpdateNameList = m.cUpdateNameList + ", " + ;
>		                  aFieldList[m.nField, 1] + ;
>		                  " dbo." + ALLTRIM(m.tcTable) + ".[" + aFieldList[m.nField, 1] + "]"
>	ENDFOR 
>
>	cFieldList = SUBSTR(m.cFieldList, 3)
>	cUpdateNameList = SUBSTR(m.cUpdateNameList, 3)
>
>	CURSORSETPROP("UpdatableFieldList", m.cFieldList, m.tcAlias)
>	CURSORSETPROP("UpdateNameList", m.cUpdateNameList, m.tcAlias)
>
>	CURSORSETPROP("SendUpdates",.T., m.tcAlias)
>ENDIF 
>
>RETURN m.lSuccess
>
>
>It assumes that the original table name and the alias can be different (which is something we need to support, since it's not unusual in this app to pull more than one data set from the same table). The GetPK method queries the information_schema table and builds a comma-separated list of fields marked as primary keys.
>
>Tamar
If it's not broken, fix it until it is.


My Blog
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform