Microsoft KB Archive/279888

= BUG: Error When You Use Client Cursor to Add Record to SQL Server Table That Has Default Value in Datetime Field =

Article ID: 279888

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q279888



SYMPTOMS
If you use ADO to insert a new record through a client-side recordset into a SQL Server table that has a non-nullable datetime field with a default value, you receive the following error message if you do not supply a value for the datetime field:

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

This error occurs whether you use the OLE DB Provider for SQL Server or the OLE DB Provider for ODBC Drivers. The error message may differ when you use Microsoft Data Access Components (MDAC) version 2.5 Service Pack 1 (SP1) or earlier. This error does not occur with a server-side cursor.



CAUSE
This error occurs in the Client Cursor Engine when it attempts to convert the value of type DBTYPE_DBTIMESTAMP to DBTYPE_VARIANT.



RESOLUTION
There are several ways to work around this problem:
 * Use a server-side cursor for the recordset.
 * Remove the default value that is specified for the field in the database.
 * Always specify a value for the field when you add a new record.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce Behavior
  Add a table to SQL Server that contains a datetime field that does not accept nulls and that has a default value. Include at least one additional field. For the sake of this article, the following SQL Server 2000 table is created: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DateTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[DateTest] GO

CREATE TABLE [dbo].[DateTest] (   [DateId] [int] IDENTITY (1, 1) NOT NULL,    [TestDate] [datetime] NOT NULL ,    [Nonsense] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO

ALTER TABLE [dbo].[DateTest] WITH NOCHECK ADD CONSTRAINT [DF_DateTest_TestDate] DEFAULT ('3/1/2001') FOR [TestDate], PRIMARY KEY CLUSTERED (       [DateId]    )  ON [PRIMARY] GO   To insert a row into the table, run code that is similar to the following code.

Note You must change User ID = and password = to the correct values before you run this code. Make sure that  has the appropriate permissions to perform this operation on the database. Also, make sure that you do not supply a value for the datetime field. Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Set cnn = New ADODB.Connection cnn.Open &quot;Provider=SQLOLEDB;Data Source=(local);&quot; & _ &quot;Initial Catalog=test;User Id=;Password= ;&quot; Set rst = New ADODB.Recordset With rst .CursorLocation = adUseClient .Open &quot;SELECT * FROM DateTest&quot;, cnn, adOpenStatic, adLockOptimistic .AddNew .Fields(&quot;Nonsense&quot;).Value = &quot;test data&quot; .Update End With Debug.Print rst(&quot;TestDate&quot;).Value You receive the above-mentioned error message when you examine the Value property of the datetime field. 

Additional query words: -2147217887 (80040e21)

Keywords: kbbug kbdatabase kbpending KB279888

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.