Microsoft KB Archive/294218

= PRB: Requery Method Changes LockType Property of ADO Recordset to Read-Only =

Article ID: 294218

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Learning Edition

-



This article was previously published under Q294218



SYMPTOMS
Under certain circumstances, if you call the Requery method of an ActiveX Data Objects (ADO) Recordset object, the LockType property of the Recordset changes to read-only (adLockReadOnly).

This problem may occur under the following circumstances:
 * If you use DataEnvironment command objects to build hierarchical (shaped) recordsets. -or-


 * If you call Requery after you disconnect and reconnect the Recordset in both hierarchical and standard disconnected ADO recordsets.



RESOLUTION
To resolve this problem, use code that is similar to the following code (instead of calling Requery) to close and to reopen the Recordset: MyRecordset.Close MyRecordset.Open



MORE INFORMATION
Because this behavior is specific to the DataEnvironment object, the same result occurs with any OLE DB provider and with any CursorLocation or CursorType property setting. However, this problem does not occur if you use DataEnvironment to create a non-hierarchical (&quot;flat&quot;) Recordset.

Hierarchical Recordsets Using DataEnvironment Commands
 Follow these steps to set up the project and create the connection:  In Visual Basic, create a new Standard EXE project. Form1 is added to the project by default. On the Project menu, click Add DataEnvironment to add this component to your project. In the DataEnvironment window, right-click Connection1, and then click Properties. Change the properties to point to the Microsoft Access Northwind sample database and to test the connection, and then click OK. </li> Follow these steps to create the parent and child commands of the hierarchical Recordset and to set up the DataEnvironment to return a hierarchical Recordset:  In the DataEnvironment window, right-click Connection1, and then click Add Command. This adds Command1 to the DataEnvironment.</li> Right-click Command1, and then click Properties.</li>  In the Properties dialog box, click the General tab. Under Source of Data, click SQL Statement, and then paste the following code in the text box: SELECT CustomerID, EmployeeID, Freight, OrderDate, OrderID, RequiredDate FROM Orders </li> On the Advanced tab, set the LockType property to 3-Optimistic, and then click OK.</li> In the DataEnvironment window, right-click Command1, and then click Add Child Command. The Command2 child command is added directly to the list of fields under Command1.</li> Right-click Command2, and then click Properties.</li>  In the Properties dialog box, click the General tab. Under Source of Data, click SQL Statement, and then paste the following code in the text box: SELECT Discount, OrderID, ProductID, Quantity, UnitPrice FROM [Order Details] </li> Click Apply to accept the changes. You receive the following warning:

At least one relation must be defined.

Click OK.</li> To create this relation, click the Relation tab in the Properties dialog box, and then ensure that Relate to Parent Command Object is selected.</li> Confirm that the OrderID field appears in both the Parent Fields and the Child Fields of the Relation Definition section, and then click Add.</li> Click OK, and then close the DataEnvironment window.</li></ol> </li> Follow these steps to set up Form1:  Add a TextBox control to Form1. Set the DataSource property to DataEnvironment1, set the DataMember property to Command1, and then set the DataField property to CustomerID.</li> <li> Add a Button control to Form1, and then paste the following code in the Click event handler of the button: Debug.Print &quot;LockType Before Requery: &quot; & DataEnvironment1.rsCommand1.LockType DataEnvironment1.rsCommand1.Requery Debug.Print &quot;LockType After Requery: &quot; & DataEnvironment1.rsCommand1.LockType </li></ol> </li> <li>Run the project, and then click the command button. The following output appears in the immediate window:

<pre class="fixed_text">LockType Before Requery: 3  (Optimistic Locking) LockType After Requery: 1   (Read-Only Recordset)

</li> <li> To work around this behavior, replace the following code DataEnvironment1.rsCommand1.Requery with the following code: DataEnvironment1.rsCommand1.Close DataEnvironment1.rsCommand1.Open </li> <li>Run the project again. The following output appears in the immediate window:

<pre class="fixed_text">LockType Before Requery: 3 LockType After Requery: 3

</li></ol>

Hierarchical Recordsets Using ADO Objects
<ol> <li>In Visual Basic 6.0, create a new project of Data Project type.</li> <li>Add a Button control to the default form, frmDataEnv.</li> <li> Double-click the command button to open the Click event handler of the button, and then add the following code to the code window:

Note You must change User ID=<UID> to the correct value before you run this code. User ID <UID> must have the appropriate permissions to perform these operations on the database. Dim oCn As New ADODB.Connection Dim oRs As New ADODB.Recordset 'Connect to Microsoft SQL Server. 'oCn.Open &quot;provider=msdatashape;data provider=sqloledb;&quot; & _ &quot;user id=<UID>;data source=(local);initial catalog=northwind;&quot; 'Connect to Microsoft Jet 4.0. oCn.Open &quot;provider=msdatashape;data provider=microsoft.jet.oledb.4.0;&quot; & _ &quot;user id=admin;data source=c:\northwind.mdb;&quot; 'Connect to Microsoft Jet 3.51. 'oCn.Open &quot;provider=msdatashape;data provider=microsoft.jet.oledb.3.51;&quot; & _ &quot;user id=admin;data source=c:\nwind.mdb;&quot; oRs.CursorLocation = adUseClient oRs.CursorType = adOpenStatic oRs.LockType = adLockBatchOptimistic 'Non-shaped recordset 'oRs.Open &quot;Select * from Customers&quot;, oCn 'Shaped recordset oRs.Open &quot;SHAPE {Select * from Customers} AS Command1 APPEND ({Select * from Orders}  &quot; & _           &quot;AS Command2 RELATE 'CustomerID' TO 'CustomerID') AS Command2&quot;, oCn Set oRs.ActiveConnection = Nothing MsgBox &quot;LockType before Requery is &quot; & oRs.LockType Set oRs.ActiveConnection = oCn oRs.Requery

'oRs.Close 'oRs.Open MsgBox &quot;LockType after Requery is &quot; & oRs.LockType

oRs.Close oCn.Close Set oRs = Nothing Set oCn = Nothing </li> <li>Modify the connection strings as appropriate for your data source.</li> <li>Run the application, and then click the command button. You receive the following output:

<pre class="fixed_text">LockType before Requery is 4    (adLockBatchOptimistic) LockType after Requery is 1     (adLockReadOnly)

</li> <li> To work around this behavior, comment out the following line of code: oRs.Requery and uncomment the following two lines of code: 'oRs.Close 'oRs.Open </li> <li>Run the project again. You receive the following output:

<pre class="fixed_text">LockType before Requery is 4 LockType after Requery is 4

</li></ol>

Keywords: kbdataenv kbprb KB294218

-

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

© Microsoft Corporation. All rights reserved.