BUG: Error When You Set the "Server Data on Insert" Property and Open a Recordset

If you try to set the Server Data on Insert dynamic property of the ADODB Command object and then open a recordset based on the command, you receive the following error message: Alternatively, you may not receive an error message, but accessing the recordset fields may return null.

The cursor service does not support the DBPROP_SERVERDATAONINSERT dynamic property, even if the underlying data provider supports it. You do not receive the above-mentioned error message with some providers because some cursor engines do not produce an error on the DBPROP_SERVERDATAONINSERT dynamic property.

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

The DBPROP_SERVERDATAONINSERT property allows the provider to update the local row cache as soon as the server commits the insert of the Identity field. This allows the client to immediately see new Identity fields.

Steps to Reproduce Behavior

1. In Microsoft Visual Basic, open a new Standard EXE project. Form1 is created by default.
2. Set a reference to Microsoft ActiveX Data Objects.
3. Add two Command buttons to Form1.
4. Add the following code to Form1:

   Private Sub Command1_Click()
   Dim cn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim cmd As New ADODB.Command
   
   cn.ConnectionString = "Provider=SQLOLEDB;Initial Catalog=NorthWind;Data Source=witster;" & _
                         "UID=sa;PWD=password;"
   
   cn.Open
   Set cmd.ActiveConnection = cn
   cmd.CommandType = adCmdText
   cmd.CommandText = "SELECT * FROM Table1"
   
   cmd.Properties("Server Data On Insert") = True   ' <--- This should not be allowed.
   cmd.Properties("Server Data On Insert").Attributes = adPropRequired
   
   With rs
   .CursorLocation = adUseClient
   .LockType = adLockOptimistic
   .CursorType = adOpenKeyset
   Set rs.Source = cmd
   
   .Open  ' <--- SQLOLEDB Gives the error here.
   .AddNew "Field1", "test"
   .Update
   
   Debug.Print .Fields("FieldID").Value
   Debug.Print .Fields("Field2").Value
   End With
   
   Set cmd = Nothing
   rs.Close
   Set rs = Nothing
   cn.Close
   Set cn = Nothing
   End Sub
   
   Private Sub Command2_Click()
   Dim cn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim cmd As New ADODB.Command
   
   cn.ConnectionString = _
   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "e:\mdac27" & "\GUIDTest.mdb"
   
   cn.Open
   Set cmd.ActiveConnection = cn
   cmd.CommandType = adCmdText
   cmd.CommandText = "SELECT * FROM Table1"
   cmd.Properties("Server Data On Insert") = True  '<--- This should not be allowed.
   cmd.Properties("Server Data On Insert").Attributes = adPropRequired
   With rs
   .CursorLocation = adUseClient
   .LockType = adLockOptimistic
   .CursorType = adOpenKeyset
   Set rs.Source = cmd
   
   .Open  ' <-- No error from Jet OLE DB.
   .AddNew "Field1", "test"
   .Update
   Debug.Print .Fields("FieldID").Value  '<--- No data is obtained even if the
                                         ' "Server Data on Insert" property is set
                                         ' because the CE does not support it.
   Debug.Print .Fields("Field2").Value   '<--- No data is obtained even if the
                                         ' "Server Data on Insert" property is set
                                         ' because the CE does not support it.
   End With
   
   Set cmd = Nothing
   rs.Close
   Set rs = Nothing
   cn.Close
   Set cn = Nothing
   End Sub
   					

5. Change your connection strings to connect to your Microsoft SQL Server and your Microsoft Access database.