...

View Full Version : Update and ORDER BY



Gyte
04-18-2006, 10:06 AM
I tried the following code:


Set Conn = Server.Createobject("ADODB.Connection")
Conn.Open Connstring
Set rs = server.createobject("ADODB.RecordSet")

SQL = "SELECT * " & _
"FROM tblTemp " & _
"WHERE FalseContactAgent IS NULL " & _
"ORDER BY CPclientnumber, V18000, V20000"

rs.Open SQL, Conn, adOpenDynamic, adLockOptimistic, adCmdtext

ContactAgentID = 1

Do While Not rs.EOF
rs("ContactAgentID") = ContactAgentID
ContactAgentID = ContactAgentID + 1
rs.Update
rs.MoveNext
Loop

rs.Close
Conn.Close

It generated the following error on the line of the code "rs("ContactAgentID") = ContactAgentID":

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 remove the the "ORDER BY" component of the SQL-query, the code works just fine. Can anyone help me out with this? Does this mean you cannot update a recordset when you use the "ORDER BY" in the SQL-query?

Gyte
04-18-2006, 11:35 AM
I made the following adjustment and now it works!!! But I still don't know what causes this problem. Can anyone give some comment on this?


Set Conn = Server.Createobject("ADODB.Connection")
Conn.Open Connstring
Set rs = server.createobject("ADODB.RecordSet")

SQL = "SELECT * " & _
"FROM tblTemp " & _
"WHERE FalseContactAgent IS NULL " & _
"ORDER BY CPclientnumber, V18000, V20000"

rs.CursorLocation = adUseClient
rs.Open SQL, Conn, adOpenDynamic, adLockOptimistic, adCmdtext

ContactAgentID = 1

Do While Not rs.EOF
rs("ContactAgentID") = ContactAgentID
ContactAgentID = ContactAgentID + 1
rs.Update
rs.MoveNext
Loop

rs.Close
Conn.Close



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum