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.

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


View products that this article applies to.

This article was previously published under Q304253

↑ Back to the top


Symptoms

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:
-2147217887 The requested properties cannot be supported
Alternatively, you may not receive an error message, but accessing the recordset fields may return null.

↑ Back to the top


Cause

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.

↑ Back to the top


Status

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

↑ Back to the top


More information

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.

↑ Back to the top


Keywords: KB304253, kbpending, kbbug

↑ Back to the top

Article Info
Article ID : 304253
Revision : 3
Created on : 5/8/2003
Published on : 5/8/2003
Exists online : False
Views : 478