PRB: Not Null Field Returns Error Using VFPODBC SQL Insert

• Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a, 6.0
• Microsoft ODBC Driver for Visual FoxPro, versions 5.0, 6.0

SYMPTOMS

When inserting a record into a table that has a field that does not allow nulls, records inserted through the VFPODBC driver react differently than native Visual FoxPro.

CAUSE

The default setting for SET NULL is ON for the Visual FoxPro ODBC Driver but it is OFF for the Visual FoxPro Development Environment.

NOTE: If SET NULL is ON in the Visual FoxPro development environment, you cannot insert a null value into a field that does not allow a null.

RESOLUTION

Two methods to resolve this problem follows:

• Mark all fields as allowing nulls.


-or-


• Make sure that you explicitly insert values into every non-null field.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Place the following code in a program file in a new directory:

   
         CREATE DATA TEST1
         CREATE TABLE TBL1(FLD1 C(10) PRIMARY KEY, FLD2 C(25))
         close_data()
         ?DBC()
         CREATE DATA TEST2
         CREATE CONNECTION CONN1 CONNSTR "driver=microsoft " ;
            + "visual foxpro driver;sourcetype=dbc;" ;
            + "sourcedb="+ SYS(5) + CURDIR() + "test1.dbc;backgroundfetch=no"
         CREATE SQL VIEW TEST2 REMOTE CONNECTION CONN1 AS SELECT * FROM TBL1
         DBSETPROP('Test2','View','SendUpdates',.T.)
         DBSETPROP('Test2.Fld1','field','updatable',.T.)
         USE TEST2
         INSERT INTO TEST2 (FLD1) VALUES ('1')
         ?TABLEUPDATE()
         USE
   
         PROCEDURE close_data
            CLOSE DATA ALL
         ENDPROC 

   ?TABLEREVERT(.T.) 

1. Run the code and note that TableUpdate() returns a .F. and Use returns the following error:

   Connectivity error: [Microsoft][ODBC Visual FoxPro Driver
   Field FLD2 does not accept null values.




2. Run the following code from a new program file in the same directory as the preceding sample:

   
         OPEN DATABASE TEST1
         USE TBL1
         INSERT INTO TBL1 (FLD1) VALUES('100')
         ?ISNULL(FLD2)
         ?EMPTY(FLD2)
         ?ISBLANK(FLD2)
         BROWSE NOWAIT 

Additional query words:

Keywords : kbDatabase kbODBC kbVFp300 kbVFp300b kbVFp500 kbVFp500a kbVFp600
Version : WINDOWS:3.0,3.0b,5.0,5.0a,6.0
Platform : WINDOWS
Issue type : kbprb