Microsoft KB Archive/304253

= BUG: Error When You Set the &quot;Server Data on Insert&quot; Property and Open a Recordset =

Article ID: 304253

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.6 Service Pack 1
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q304253



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.



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.



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



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
 In Microsoft Visual Basic, open a new Standard EXE project. Form1 is created by default. Set a reference to Microsoft ActiveX Data Objects. Add two Command buttons to Form1.  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 = &quot;Provider=SQLOLEDB;Initial Catalog=NorthWind;Data Source=witster;&quot; & _ &quot;UID=sa;PWD=password;&quot;

cn.Open Set cmd.ActiveConnection = cn cmd.CommandType = adCmdText cmd.CommandText = &quot;SELECT * FROM Table1&quot;

cmd.Properties(&quot;Server Data On Insert&quot;) = True  ' <--- This should not be allowed. cmd.Properties(&quot;Server Data On Insert&quot;).Attributes = adPropRequired

With rs .CursorLocation = adUseClient .LockType = adLockOptimistic .CursorType = adOpenKeyset Set rs.Source = cmd

.Open ' <--- SQLOLEDB Gives the error here. .AddNew &quot;Field1&quot;, &quot;test&quot; .Update

Debug.Print .Fields(&quot;FieldID&quot;).Value Debug.Print .Fields(&quot;Field2&quot;).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 = _ &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & &quot;e:\mdac27&quot; & &quot;\GUIDTest.mdb&quot;

cn.Open Set cmd.ActiveConnection = cn cmd.CommandType = adCmdText cmd.CommandText = &quot;SELECT * FROM Table1&quot; cmd.Properties(&quot;Server Data On Insert&quot;) = True '<--- This should not be allowed. cmd.Properties(&quot;Server Data On Insert&quot;).Attributes = adPropRequired With rs .CursorLocation = adUseClient .LockType = adLockOptimistic .CursorType = adOpenKeyset Set rs.Source = cmd

.Open ' <-- No error from Jet OLE DB. .AddNew &quot;Field1&quot;, &quot;test&quot; .Update Debug.Print .Fields(&quot;FieldID&quot;).Value '<--- No data is obtained even if the ' &quot;Server Data on Insert&quot; property is set ' because the CE does not support it. Debug.Print .Fields(&quot;Field2&quot;).Value  '<--- No data is obtained even if the ' &quot;Server Data on Insert&quot; 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  Change your connection strings to connect to your Microsoft SQL Server and your Microsoft Access database.</ol>

Keywords: kbbug kbpending KB304253

-

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

© Microsoft Corporation. All rights reserved.