Questions That Should Be Asked More Frequently

[Previous] [Next]

Q. I remember someone telling me once that ADO marshaled large Recordsets asynchronously. Is that true?

A. That was true for a brief period of time. In version 2.0, ADO marshaled large (8 KB or larger) client-side Recordsets asynchronously. You could turn off this feature by setting a key in the Registry. The ADO development team later decided that this feature's drawbacks outweighed its benefits. Starting with version 2.1, large Recordsets are once again marshaled synchronously by default. For more information on this feature, see Microsoft's Knowledge Base.

Q. I'm using client-side Recordsets with batch optimistic locking in a three-tiered application. I can modify a record in the Recordset object in my client application and successfully submit that change to the database by passing the Recordset object to my business object, reconnecting it to the database, and calling the UpdateBatch method. But if I try to modify that same record again in my client Recordset object, the update fails when I try to submit that change to the database. I get an error saying, "The specified row could not be located for updating." Why am I having this problem?

A. You're running into one of the minor drawbacks of passing your Recordset to the business object by value. Let's look at the changes you're making to the Recordset object in your client application. Suppose you change a customer's balance due from $75 to $125 in your client-side Recordset. When you send that Recordset to the business object to submit the update, ADO generates the following action query:

 UPDATE Customers SET BalanceDue = 125 WHERE CustomerID = 7 AND BalanceDue = 75 

Because you passed your Recordset object to the business object by value, the changes that occur to the record when you call UpdateBatch on the business object recordset are not returned to the Recordset object in your client application. That Recordset object is essentially unaware that you've submitted a pending change to the database. If you now change that same customer's balance due from $125 to $200 and pass the Recordset to the business object to submit the change, ADO will generate the following action query:

 UPDATE Customers SET BalanceDue = 200 WHERE CustomerID = 7 AND BalanceDue = 75 

Because the Recordset object in the client application is unaware that the business object submitted the initial change (from $75 to $125) to the database, it still sees the original value of the balance due as $75.

To solve this problem you could pass the Recordset object by reference instead of by value in order to keep the client's Recordset object aware of the changes it already submitted to the database. Another option is to reexecute the initial query and return this "fresher" Recordset each time you call the business object. This way, ADO will use the recently retrieved data in each action query it generates. A third option is to call the UpdateBatch method on the disconnected client Recordset once you've successfully submitted the pending changes. When you call UpdateBatch on a disconnected Recordset, ADO assumes you know what you're doing and marks the pending changes as successfully submitted to the database. While this option looks like a hack, it's pretty effective.

Q. As a follow-up to the previous question, I decided to pass Recordset objects by reference from the client to the business object in order to give my client the ability to repeatedly modify the same record. Then I decided to set the MarshalOptions property to adMarshalModifiedOnly to decrease the amount of data passed across the network. Now I've run into a new problem. The Recordset that the business object returns contains only the records that previously had pending changes. The Recordset initially had 20 records. I modified three records, deleted one, and added two. After the call to the business object, the Recordset contained 5 records (3 modified + 2 added) rather than the 21 I expected. What can I do?

A. Unfortunately, there's no way to make this work the way you want. When you set MarshalOptions to adMarshalModifiedOnly, this is exactly the behavior you should expect. The real question is, "Can I merge the Recordset that my business object returned with my original one?" The answer is no.

I'd love to see the ADO Cursor Engine add functionality that makes this scenario more palatable, but I don't expect that to happen for two main reasons:

• It would take some serious internal design changes and a lot of new code to make this possible. I won't go into detail about this; just trust me when I say it's easier said than done.
• Few programmers design their applications in such a way that they would need this functionality.

I'll never say never, but I won't hold my breath waiting for this feature. If you must make this scenario work, you'll need to write a lot of code to handle it yourself.