Knowledge Base

PRB: Not Null Field Returns Error Using VFPODBC SQL Insert

Article ID: 196333

Article Last Modified on 6/28/2004

APPLIES TO

This article was previously published under Q196333

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.

When you are inserting the record into a linked table through the Microsoft Access user interface, you see the following error message:
ODBC call failed
followed by:
[Microsoft][ODBC Visual FoxPro Driver] Field <FieldName> does not accept null values.
When you are inserting the record into a linked table through Microsoft Visual Basic for Applications (VBA) code within Microsoft Access, you see the following error message:
Run-time error '3146':
ODBC --Call failed.

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

You can resolve this problem in the following ways:

  • Mark all fields as allowing nulls. -or-

  • Make sure that you explicitly insert values into every non-null field.
You can use the following VBA code within Access to add a record:

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

Sub Test_VFP_ODBC_Driver()

   Dim wks As DAO.Workspace
   Dim foo As DAO.Connection
   Dim db As DAO.Database
   Dim rs As DAO.Recordset

   Set wks = DBEngine.CreateWorkspace("", "Admin", "", dbUseODBC)
   wks.DefaultCursorDriver = dbUseODBCCursor
   Set foo = wks.OpenConnection("", dbDriverNoPrompt, False, "ODBC;dsn=MyNameDSN;NULL=NO")
   Set rs = foo.OpenRecordset("MyOrders", dbOpenDynaset, 0, dbOptimistic)

   rs.MoveFirst

   Do Until rs.EOF
       rs.Edit
       rs.Fields(0) = "150"
       rs.Update
       rs.MoveNext
   Loop
End Sub

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
NOTE: To stop the error that occurs when you use the preceding code, issue the following command in the Command window: 
   ?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
Note that the record is added without an error, and the value of FLD2 is not '.Null.'. Whereas, as demonstrated in steps 1 and 2, the Vfpodbc.dll seems to be passing a null value for each field not being updated.

Keywords: kbprb kbpending kbcode KB196333


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.