FIX: When You Set a Date Field to Empty an Error Message Occurs

When you attempt to store the Microsoft Visual Basic keyword EMPTY to an ADODB Record column based on a Date field, the following error message occurs:

With MDAC 2.5: With MDAC 2.1.4202.3: This behavior does not occur if the Native Jet Provider is used against a Microsoft Access 2000 or Microsoft Access 97 database.

There was no conversion information for converting between Empty and DBTimeStamp in the products listed in the "Applies to" section.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem has been fixed in Microsoft ActiveX Data Objects versions 2.6 and later. You can install the latest version of Microsoft Data Access Components from the following Web site:

This error message occurs when you use the Native OLE DB Provider for SQL Server, the Native OLE DB Provider for Oracle, or OLE DB Provider for ODBC.

Steps to Reproduce Behavior

1. Start Visual Basic.
2. Set a Project reference to Microsoft Data Objects 2.5 Library.
3. Paste the following code into the Form_Load event:

   Dim cn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   
   cn.Open "Provider=SQLOLEDB.1;User ID=<uid>;Password=<pwd>;Initial Catalog=pubs;Data Source=<server_name>"
   'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<mdb>;"
   'cn.Open "Provider=MSDASQL;DRIVER={sql server};SERVER=<server_name>;UID=<uid>;PASSWORD=<pwd>;DATABASE=pubs;"
   'cn.Open "Provider=MSDAORA.1;User ID=<uid>;PASSWORD=<pwd>;Data Source=<server_name>;"
   
   cn.CursorLocation = adUseClient
   On Error Resume Next
   cn.Execute "drop table batchtest"
   On Error GoTo 0
   
   'If testing with Jet or SQL
   cn.Execute "create table batchtest (id int primary key," & _
                "fstring varchar(50) NOT NULL," & _
                "fnumber int NOT NULL," & _
                "fdate datetime NOT NULL)"
                
   'If testing with Oracle
   'cn.Execute "create table batchtest (id int primary key," & _
                "fstring varchar(50) NOT NULL," & _
                "fnumber int NOT NULL," & _
                "fdate date NOT NULL)"
                
                
   rs.Open "select * from batchtest", cn, adOpenStatic, adLockBatchOptimistic
   
       rs.AddNew
       rs.Fields("id").Value = 1
       rs.Fields("fstring").Value = "XXXXX"
       rs.Fields("fnumber").Value = 999
       rs.Fields("fdate").Value = #3/3/1999#
       rs.Update
       
       rs.AddNew
       rs.Update
       
       rs.AddNew
       rs.Fields("id").Value = 2
       rs.Fields("fstring").Value = Empty
       rs.Fields("fnumber").Value = Empty
       rs.Fields("fdate").Value = Empty 'Error occurs here.
       rs.Update
       
   cn.Close
   Set cn = Nothing
   					

4. Uncomment the cn.Open statement that matches the backend database that is being tested. Make the appropriate changes and supply valid connection parameters.
5. Save and then run the Project. The error message shown in the "Symptoms" section occurs.