Cannot update ordered recordset

[Niavlys]

Hi,
I'm using the following code to open a recorset to update it.

	strSQL = "SELECT * FROM data WHERE Folder='" & sFolder & "' ORDER BY Title ASC "
	objRS.Open strSQL, objConn, adOpenDynamic, adLockOptimistic

objRS.Fields("title") = "test"
objRS.Update

When using this code, I get a ADODB.Recordset error '800a0cb3'

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype

When I use the ORDER BY clause, the cursor type and lock type change to adForwardOnly and adLockReadOnly, so the recordset couldn't be opened for updating.

Is it a normal behaviour? I need the recordset to be sorted in order to update the good records. If I remove the ORDER BY, everything is ok.

Thanks for your help!

 

[pgferro]

Can you please explain why you need the recordset ordered ?

 

[FesterSXS]

If you are just updating the table then you dont need to open a recordset - you can do it all with SQL...

strSQL = "UPDATE data SET title='test' WHERE Folder='" & sFolder & "';"
objConn.Execute(strSQL)

Tony

http://www.SpiraxSarco.com

 

[Veep]

You should try using an UPDATE statement whenever possible. Then you can just use the connection object to execute it.

set conn=server.createobject("ADODB.CONNECTION")
conn.open YourConnectionString
strSql="Update data SET Title='test' WHERE Folder='" & sFolder & "'"
conn.execute(strSql)

 

[Niavlys]

I've just read to set conn.Cursorlocation = adUseClient. I set it and now it works... I just don't understand why.
If someone can tell me?

Thanks for the answers!