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.

PRB: Not Null Field Returns Error Using VFPODBC SQL Insert


View products that this article applies to.

This article was previously published under Q196333

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


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
				

↑ Back to the top


Status

This behavior is by design.

↑ Back to the top


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.

↑ Back to the top


Keywords: kbcode, kbpending, kbprb, KB196333

↑ Back to the top

Article Info
Article ID : 196333
Revision : 4
Created on : 6/28/2004
Published on : 6/28/2004
Exists online : False
Views : 420