Results 1 to 12 of 12
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    341
    Thanks
    2
    Thanked 1 Time in 1 Post

    Recordset Updates (2002)

    I
    You know it's time to diet when you push away from the table and the table moves.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Recordset Updates (2002)

    Update isn't normally required in ADO, since moving to the next record will trigger an update. Have you tried commenting out that line? You could then use an UpdateBatch command to update all records back to the underlying database once the editing has been done. To use batch updating, you need to change some lines.

    <code> .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockBatchOptimistic</code>

    And make sure you check for <code> If Not .BOF And Not .EOF Then </code> before you start manipulating the recordset.
    Charlotte

  3. #3
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    341
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Recordset Updates (2002)

    Charlotte, thanks for your reply. Yes I originally did not have the pRS.Update in the code and it terminated on the pRS.MoveNext which makes sense since that's when the update is applied.

    What really puzzles me is why is it doing a mass update? I would think it would only update the one record. The query itself is just a select statement not an update statement. It seems really strange to me.

    Another thing I just discovered is when I type pRS. the auto list members does not show a member called CommandText or CommandType. Are they no longer valid for the ADO libary I'm using (ActiveX Data Object 2.8 library)?

    I haven't used updatebatch before so I'm trying to stay away from that for now.
    You know it's time to diet when you push away from the table and the table moves.

  4. #4
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    341
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Recordset Updates (2002)

    After some desperate attempts with more failures, I tried your suggestion using pRS.UpdateBatch along with the additional changes you had listed. When it executed the UpdateBatch method I got the same error. I have other procedures I think similar code which works well. I even removed the while and if condition inside it so it updates one record and the same thing happens. Very strange.
    You know it's time to diet when you push away from the table and the table moves.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Recordset Updates (2002)

    CommandText and CommandType are properties of the command object, not of a recordset. Had you been using a command object rather than basing a recordset directly on the connection up to this attempt?
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Recordset Updates (2002)

    What same error? Are you getting an error message, and if so, what is it specifically?

    Have you tried using a different type of dataset? I don't normally use a forwardonly if I want to update it.

    And what does the SQL of your query look like? You may be having trouble with the code because the query isn't right for the situation. Anyhow, I'm not clear on why you're using a recordset if you're really trying to update all the records where the AssignedTo is blank and set them to the current user. Why not just run an update query? <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>That query *is* an updatable SELECT query, right?
    Charlotte

  7. #7
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    341
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Recordset Updates (2002)

    Hi Charlotte. I forgot, that is part of the command object. <img src=/S/bash.gif border=0 alt=bash width=35 height=39> No I haven't used the command object. Always the recordset and connection objects.

    I
    You know it's time to diet when you push away from the table and the table moves.

  8. #8
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    341
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Recordset Updates (2002)

    Yes, I could use an Action query (updatable) but I was using code to make the number of selections I update variable. I may assign a 1000 records to one user and 600 to another and 800 to another. So I call this one routine to handle that. The iMax is hard coded for now but I will eventually use it as some variable number I input to the procedure.
    You know it's time to diet when you push away from the table and the table moves.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Recordset Updates (2002)

    Try this:

    "SELECT DISTINCTROW AssignedTo FROM tblTest WHERE tblTest.AssignedTo Is Null AND (tblTest.CorrectTin = True OR tblTest.IRSReturnCode <> 0)"

    I can remember whether ADO supports the DISTINCTROW keyword or not, so you might have to remove that.
    Charlotte

  10. #10
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    341
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Recordset Updates (2002)

    Thanks Charlotte. I'll give it a try. I'll also try using the command object with the commandtype and commandtext properties and execute that into a recordset and see if the results are more reliable.
    You know it's time to diet when you push away from the table and the table moves.

  11. #11
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    341
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Recordset Updates (2002)

    Hi Charlotte. Here's an update.

    I tried using your select statement but I got the same error. I tried using just a simple
    You know it's time to diet when you push away from the table and the table moves.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Recordset Updates (2002)

    Not really. I based my select statement on what you had posted so far, so I would expect it to have the same problems if the problem was in the basic SQL to start with. Without knowing table structures, etc., my SQL was the equivalent of aircode. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Charlotte

Posting Permissions

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