大网数据广告活动
数据库与应用安全

Error when you use client cursor to add record

· 52CE 我爱测

This article provides resolutions for the problem that occurs when you use client cursor to add record to SQL Server table that has default value in Datetime field.

This article helps you resolve the problem that occurs when you use client cursor to add record to SQL Server table that has default value in Datetime field.

Original product version:   SQL Server
Original KB number:   279888

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.

Steps to Reproduce Behavior

  1. 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
    
  2. To insert a row into the table, run code that is similar to the following code.

    [!NOTE] You must change User ID =<UID> and password =\ to the correct values before you run this code. Make sure that <UID> 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 "Provider=SQLOLEDB;Data Source=(local);" & _
    "Initial Catalog=test;User Id=<UID>;Password=<strong password>;"
    
    Set rst = New ADODB.Recordset
    With rst
    .CursorLocation = adUseClient
    .Open "SELECT * FROM DateTest", cnn, adOpenStatic, adLockOptimistic
    
    .AddNew
    .Fields("Nonsense").Value = "test data"
    .Update
    End With
    
    Debug.Print rst("TestDate").Value
    

    You receive the above-mentioned error message when you examine the Value property of the datetime field.

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 11/03/2020
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。