Update Field in ADO Recordset and Re-sort values in Classic ASP

Question

Here's the current situation: I have a recordset of products. The price of these products depends upon other information elsewhere on the page. So, as I loop through the recordset for output, I calculate the price using a function, and display it. This all works wonderfully.

Now, for the new wrinkle. I need to be able to sort the products by the calculated price before I display them. My attempt was to simply add a new field to the recordset, loop through the recordset once to calculate the price and update that new field, and then resort the recordset on that field before looping again for the display.

Needless to say, this does not work. No matter what settings (lockType, cursorType, cursorLocation) I put on this recordset, I am presented with an error stating "Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype."

Is there any way to make this work as described, or is there another method of accomplishing this result? For reference, here is the code snippet that I am currently working with.

set rsProd = server.CreateObject("adodb.recordset")
rsProd.cursorType = adOpenKeyset
rsProd.lockType = adLockPessimistic
rsProd.cursorLocation = adUseServer
rsProd.Open sql, Conn1

'loop through the records and calculate prices'
Do until rsProd.EOF  
   laPrices = Split(GetParameterProductPrice(rsProd("Product_ID")), "|")
   'ERROR OCCURS ON NEXT LINE'  
   rsProd("Sale") = CDbl(laPrices(0))  
   rsProd("Sale_Special")= CDbl(laPrices(1))  
   rsProd.MoveNext
Loop
rsProd.MoveFirst
rsProd.Sort = "Sale_Special, Sale, Product_Code"

Filburt Filburt · Accepted Answer · 2010-06-27T13:08:06

One way to accomplish this would be a computed column in your database.

The main benefit would be that you wouldn't need to change your page(s). Additionally you could reuse the calculated price in other places in your application - I guess you actually calculate the price again on other parts of the site.

Update Field in ADO Recordset and Re-sort values in Classic ASP

2 Answers