Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

HOWTO: Set a Primary Key to Updateable in a View


View products that this article applies to.

This article was previously published under Q174689

↑ Back to the top


Summary

By default, when creating a view on a table, any field with a primary key index is not marked as updateable. This article illustrates how you can mark the primary key as updateable through the View Designer, and programmatically.

↑ Back to the top


More information

View Designer

To mark the primary key as updateable through the View Designer interface, use the following steps:

  1. Open the view in the View Designer.
  2. Click the Update Criteria tab.
  3. Click the Update column beside the primary key field.

    NOTE: The Update column has a column header that looks like a pencil. If you do not select the Update column and you attempt to INSERT a new record, you may receive one of the following errors:
    Connectivity error: [Microsoft][ODBC Visual FoxPro Driver Field <fieldname> does not accept null values.
    -or-
    Mandatory not null field - missing or null during insert.

Programmatically

To mark the primary key as updateable programmatically, use the following steps:

  1. Open the Tastrade database in the VFP\Samples\Tastrade\Data folder in Visual FoxPro 5.0, or the home(2)+"Tastrade\Data" folder in Visual FoxPro 6.0.
  2. Run the following program to create a new SQL view based on the Customer table.
          ***********RunFirst.prg***********
          CREATE SQL VIEW MYTEST AS SELECT * FROM CUSTOMER
          DBSETPROP("MYTEST","VIEW","SENDUPDATES",.T.)
          ***********End RunFirst.prg*************
    							
  3. Modify the view in the View Designer and note that the primary key is not marked for updates.
  4. Create a program called MarkPrimary and place the following code in it:
          ************MarkPrimary.prg****************
          PARAMETERS ViewName
    
          x=ALIAS()
          USE IN 0 &viewname
          PrimKeys = CURSORGETPROP('KeyFieldList',viewname)
          i=1
          remField=PrimKeys
          DO WHILE i <> 0
             nextcomma=AT(remField,",")
             IF nextcomma=0 and len(remfield)=0 THEN
                i=0
                EXIT
             ELSE
                IF nextcomma=0 and LEN(remfield)<>0 then
                   tmpfield=remfield
                   y= DBSETPROP(ViewName + "." + ;
                   tmpField,'Field','UPDATABLE',.T.)
                   i=0
                   EXIT
                ELSE
                   tmpField=SUBSTR(remField,i,NextComma -1)
                   remfield=SUBSTR(remfield,nextcomma + 1)
                   i=nextcomma
                   y= DBSETPROP(ViewName + "." + ;
                   tmpField,'Field','UPDATABLE',.T.)
                ENDIF
             ENDIF
          ENDDO
    
          SELECT  (viewname)
          USE
          IF NOT EMPTY(x) THEN
             SELECT  (x)
          ENDIF
          **************End MarkPrimary.prg*******************
    							
  5. Run the MarkPrimary.prg code by typing the following into the Command window:
          DO MARKPRIMARY WITH "MYTEST"
    							
  6. Modify the view MyTest in the View Designer and notice that the primary key field is now marked for updates.

↑ Back to the top


Keywords: KB174689, kbhowto, kbdatabase, kbcode

↑ Back to the top

Article Info
Article ID : 174689
Revision : 4
Created on : 2/16/2005
Published on : 2/16/2005
Exists online : False
Views : 376