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.

Cannot set Empty to Date field using Oracle, SQL Provider, or Kagera


View products that this article applies to.

This article was previously published under Q247202

↑ Back to the top


Symptoms

If you try to store the Visual Basic keyword Empty to an ADODB Record column based on Date type field as in the following code
rs("fDate") = Empty 
				
it causes this error:
Run-time error '-2147217887(8004e21)': Errors Occurred
Under MDAC 2.5 or later, you receive this message:
Run-time error '-2147217887(8004e21)': Multiple-step operation generated errors. Check each status value.

↑ Back to the top


Resolution

If it is necessary to re-initialize a date type field back to a blank value, cancel the AddNew operation using CancelUpdate and re-issue the AddNew method.

↑ Back to the top


Status

Microsoft is currently researching this problem and will post new information here as it becomes available.

↑ Back to the top


More information

This occurs with the Microsoft OLE DB Provider for:
  • Oracle
  • SQL Server
  • ODBC
This does not occur if the OLE DB Provider for Jet database engine is used. When Empty is stored to an ADODB Recordset column based on a Jet database engine DateTime field there is a date and time value entered into the field, however the Jet database engine suppresses the date portion. This is different behavior than with Integer and Character type columns. If Empty is stored to an Integer column, a Zero is stored to the Row regardless of the Provider and if it is a Character type field an Empty string ID stored.

Steps to reproduce the behavior

  1. Start Visual Basic.
  2. Create a Standard EXE Project and Reference Microsoft ActiveX Data Objects Library 2.x.
  3. Paste the following code into the default form, making sure to modify the connection string for your particular server name:
    Private Sub Form_Load()
    dim cn as new adodb.connection
    dim rs as new adodb.recordset
    
    'Select desired Connection, ConnectionString, and Data Source
    'Make necessary changes to the ConnectionString
    
    cn.Open "Provider=SQLOLEDB;User ID=<>;Password=<>;Initial Catalog=pubs;Data Source=<>"
    'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<>;"
    'cn.Open "Provider=MSDAORA;User ID=<>;PASSWORD=<>;Data Source=dseoracle8;"
    
    On Error Resume Next
    cn.Execute "drop table batchtest"
    On Error GoTo 0
    
    'If Jet or SQL use this statement
    cn.Execute "create table batchtest (id int primary key," & _
                 "fstring varchar(50) NOT NULL," & _
                 "fnumber int NOT NULL," & _
                 "fdate datetime NOT NULL)"
                 
    'If ORACLE use this statement
    '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
    'Error occurs here
        rs.Fields("fdate").Value = Empty 
        rs.Update
    End Sub
    					

↑ Back to the top


Keywords: KB247202, kbprb, kbdatabase

↑ Back to the top

Article Info
Article ID : 247202
Revision : 5
Created on : 11/22/2005
Published on : 11/22/2005
Exists online : False
Views : 435