Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Sep 2005
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Update and ORDER BY

    I tried the following code:

    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":
    Code:
    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?
    Last edited by Gyte; 04-18-2006 at 10:26 AM.

  • #2
    New Coder
    Join Date
    Sep 2005
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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?

    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.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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •