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

SYMPTOMS

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:

Run-time error '-2147217887(8004e21)': Multiple-step operation generated errors. Check each status value.

With MDAC 2.1.4202.3:

Run-time error '-2147217887(8004e21)': Errors Occurred

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

STATUS

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:

http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp

MORE INFORMATION

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

Start Visual Basic.
Set a Project reference to Microsoft Data Objects 2.5 Library.

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