Microsoft KB Archive/168837: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
Line 52: | Line 52: | ||
<div class="errormessage"> | <div class="errormessage"> | ||
"Run-time error '3162':<br /> | |||
<br /> | <br /> | ||
Null Is Invalid. | Null Is Invalid." | ||
</div> | </div> | ||
Line 95: | Line 95: | ||
USE | USE | ||
</pre></li> | </pre></li> | ||
<li>In Visual FoxPro, modify the Database | <li>In Visual FoxPro, modify the Database "ztest" and add the table "ztest" to the database. Add an additional character field and create a Primary Key (this is required for the ODBC driver to write back to the table). Exit Visual FoxPro and create a Visual FoxPro data source called ztest, which points to the database ZTEST.DBC.</li> | ||
<li>Create a new Visual Basic or VBA project. Make sure that Microsoft DAO 3.0 or 3.5 object library is included in your References.</li> | <li>Create a new Visual Basic or VBA project. Make sure that Microsoft DAO 3.0 or 3.5 object library is included in your References.</li> | ||
<li><p>Add the following code to a Module:</p> | <li><p>Add the following code to a Module:</p> | ||
<pre class="codesample"> Sub Test_VFP_Date() | <pre class="codesample"> Sub Test_VFP_Date() | ||
Dim db As Database, rs As Recordset | Dim db As Database, rs As Recordset | ||
Set db = OpenDatabase( | Set db = OpenDatabase("", False, False, _ | ||
"ODBC;database=testdata;uid='',pwd=;dsn=ztest") | |||
Set rs = db.OpenRecordset( | Set rs = db.OpenRecordset("ztest") | ||
rs.Edit | rs.Edit | ||
rs!dfield = Null | rs!dfield = Null |
Latest revision as of 10:04, 21 July 2020
Article ID: 168837
Article Last Modified on 10/11/2006
APPLIES TO
- Microsoft Visual Basic 5.0 Professional Edition
- Microsoft Visual Basic 5.0 Enterprise Edition
- Microsoft Visual Basic 4.0 Professional Edition
- Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
- Microsoft Visual FoxPro 3.0 Standard Edition
- Microsoft Visual FoxPro 5.0 Standard Edition
This article was previously published under Q168837
SYMPTOMS
Trying to set a Visual FoxPro Date field to Null via DAO/ODBC results in the following error:
CAUSE
The FoxPro ODBC driver does not use Null for an empty date.
RESOLUTION
Set the date field to 12/30/1899 instead. The Visual FoxPro ODBC driver translates this value into an empty date.
STATUS
This behavior is by design.
MORE INFORMATION
In Visual Basic and other VBA and DAO applications, dates are stored as the number of days since 12/30/1899. This date is stored internally as the number 0. The Visual FoxPro ODBC driver takes this 0 value to represent an empty date.
Steps to Reproduce Behavior
Create and execute the following Visual FoxPro program:
CREATE DATABASE ztest CREATE TABLE ztest (dfield d) APPEND BLANK REPLACE dfield WITH {3/15/96} USE
- In Visual FoxPro, modify the Database "ztest" and add the table "ztest" to the database. Add an additional character field and create a Primary Key (this is required for the ODBC driver to write back to the table). Exit Visual FoxPro and create a Visual FoxPro data source called ztest, which points to the database ZTEST.DBC.
- Create a new Visual Basic or VBA project. Make sure that Microsoft DAO 3.0 or 3.5 object library is included in your References.
Add the following code to a Module:
Sub Test_VFP_Date() Dim db As Database, rs As Recordset Set db = OpenDatabase("", False, False, _ "ODBC;database=testdata;uid='',pwd=;dsn=ztest") Set rs = db.OpenRecordset("ztest") rs.Edit rs!dfield = Null rs.Update End Sub
- Run the code from the Immediate Window. If prompted for ODBC connection information, select the ztest data source. Then, select the ztest table and click OK.
- You will receive the run-time error described above for the line:
- Change the line to either:
rs!dfield = #12/30/1899#
-or-
rs!dfield = 0
- Open the ztest table in Visual FoxPro to verify the date is now blank.
REFERENCES
Keywords: kb32bitonly kberrmsg kbprb kbprogramming KB168837