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