SETTING NULL OFF in FoxPro Using VB and ADO 
Author Message
 SETTING NULL OFF in FoxPro Using VB and ADO

When attempting to INSERT a record into a FoxPro 2.6 table from VB code
using
ADO, I get the following error message:

        'Error -2147217900: [Microsoft][ODBC Visual FoxPro Driver]Field DATE_INPUT
        'does not accept null values.

The statements I am using for the insert are as follows:

        strSql = "INSERT INTO Ra010111 ( SSN ) VALUES ('123456789')"
        cnnX.Execute strSql, intRecsAffected, adCmdText

The problem appears to occur because the FoxPro environmental setting SET
NULL
defaults to ON, which results
in the behavior described in the MSDN help file for FoxPro:

        "If SET NULL is ON, INSERT - SQL attempts to insert null values
          into any fields not specified in the VALUES clause."

Since FoxPro 2.6 tables do not support NULL values, the insert fails.

So my question is: can I change the setting of SET NULL using VB/ADO?  How?

Thanks.  I'm cross-posting this in the appropriate FoxPro newsgroup.

Greg Dunn

A LITTLE MORE MATERIAL, FOR REFERENCE:

The code I am using to do the insert is as follows:

 Dim cmdX As ADODB.Command
 Dim cnnX As ADODB.Connection
 ...
 strConnection = _
         "Driver={Microsoft Visual FoxPro Driver};" & _
         "SourceType=DBF;" & _
         "SourceDB=" & strDbsSourcePath & ";" & _
         "Exclusive=No;"
 cnnX.ConnectionString = strConnection
 cnnX.Open
 ...
 strSql = "INSERT INTO Ra010111 ( SSN ) VALUES ('" & strValue & "')"
 cnnX.Execute strSql, intRecsAffected, adCmdText



Sun, 03 Aug 2003 06:39:20 GMT  
 SETTING NULL OFF in FoxPro Using VB and ADO
Found my own answer. In case anybody else ever needs to do this, the trick
is to include Null=No in the connection string.

--
Greg Dunn
(Please delete all capitalized letters from my return email address to send
me email.)


Quote:
> When attempting to INSERT a record into a FoxPro 2.6 table from VB code
> using
> ADO, I get the following error message:

> 'Error -2147217900: [Microsoft][ODBC Visual FoxPro Driver]Field DATE_INPUT
> 'does not accept null values.

> The statements I am using for the insert are as follows:

> strSql = "INSERT INTO Ra010111 ( SSN ) VALUES ('123456789')"
> cnnX.Execute strSql, intRecsAffected, adCmdText

> The problem appears to occur because the FoxPro environmental setting SET
> NULL
> defaults to ON, which results
> in the behavior described in the MSDN help file for FoxPro:

> "If SET NULL is ON, INSERT - SQL attempts to insert null values
>   into any fields not specified in the VALUES clause."

> Since FoxPro 2.6 tables do not support NULL values, the insert fails.

> So my question is: can I change the setting of SET NULL using VB/ADO?
How?

> Thanks.  I'm cross-posting this in the appropriate FoxPro newsgroup.

> Greg Dunn

> A LITTLE MORE MATERIAL, FOR REFERENCE:

> The code I am using to do the insert is as follows:

>  Dim cmdX As ADODB.Command
>  Dim cnnX As ADODB.Connection
>  ...
>  strConnection = _
>          "Driver={Microsoft Visual FoxPro Driver};" & _
>          "SourceType=DBF;" & _
>          "SourceDB=" & strDbsSourcePath & ";" & _
>          "Exclusive=No;"
>  cnnX.ConnectionString = strConnection
>  cnnX.Open
>  ...
>  strSql = "INSERT INTO Ra010111 ( SSN ) VALUES ('" & strValue & "')"
>  cnnX.Execute strSql, intRecsAffected, adCmdText



Sun, 03 Aug 2003 11:23:20 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Using FoxPro 3.0 tables in VB using ADO

2. Set Null Value using ADO??

3. ADO Connection - How to set ANSI_NULLS OFF through the connection

4. Set Databound ADO Field to Null (VB6)

5. Set ADO Parameter value to Null?

6. Error setting field to Null with Jet 4 (SP5) and ADO 2.5

7. Link FoxPro Table in Access 2000 using ADO

8. set stored proc param to null using crystal activex

9. SELECT INTO using ADO and Foxpro tables

10. HELP!!! Using ADO to read FOXPRO 2.5 database

11. Read FoxPro Using ADO?

12. running Foxpro programs using ADO

 

 
Powered by phpBB® Forum Software