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

MORE INFORMATION

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 =<strong 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.