Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q274506

↑ Back to the top


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.

↑ Back to the top


Cause

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

↑ Back to the top


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:

↑ Back to the top


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

  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.

↑ Back to the top


Keywords: KB274506, kbmdac260fix, kbfix, kbbug

↑ Back to the top

Article Info
Article ID : 274506
Revision : 5
Created on : 5/17/2007
Published on : 5/17/2007
Exists online : False
Views : 308