SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Batch Update in Access?

    Hi everyone,

    I need to update two records in one query on an Access database (increment one value by 1, decrement the other by 1).

    Any idea how to do this? I'm on Access 97 using CF Express for this one and so far I haven't been able to get it to work...
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  2. #2
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    1. Select the 2
    2. Run your increment/decrement routine
    3. UPDATE with the new values

    ...

    Or am I thinking it's simpler then it is?
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  3. #3
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sunny, simpler:

    SQL = "UPDATE tblsBlah SET value1 = value1 + 1, value2 = value2 - 1 WHERER blah = 'hoho'"


  4. #4
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Possibly - though I'm not entirely sure what you actually meant so it could just be me being dumb

    OK...

    What happens is I have a bunch of records like so:

    Code:
    tblForgetExactName
    ---------------------
    ID | Text | ListOrder
    tblWhatverTheNameIs.ListOrder is the crucial value here. It's used to define the sort order for records from this table, when they're output to a page.

    Say I want to push a value up a list (thereby changing the output order). So I have a link (called "Move Up") that passes the record ID and ListOrder values to an action page.

    On the action page, I need to have something that will decrement the ListOrder value of that record (remember it's moving up the list) and increment the value of the record above it - so the records essentially swap places in the sort order. At the moment I'm trying a vanilla update statement but it's not going too well.

    Any ideas?

    I'm not sure I like this approach either. So if anyone has any alternatives, bring 'em out

    ColdFusion Express is preferable, but I may be able to swing ASP too....
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  5. #5
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    D's code is essentially waht you need then
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  6. #6
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by dhtmlgod
    Sunny, simpler:

    SQL = "UPDATE tblsBlah SET value1 = value1 + 1, value2 = value2 - 1 WHERER blah = 'hoho'"

    Ja, but won't that just update several fields in the same record - i.e. 'hoho'

    I need to update the same field in two different records

    <edit>
    Could always run two update queries one after the other. But Access doesn't seem to like that...
    </edit>
    Last edited by hillsy; Dec 6, 2001 at 08:41.
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  7. #7
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,123
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Ah, I see, I was wondering where the confusion was!
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  8. #8
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oi - I may be a bit dim, but really....

    I don't have the application right in front of me unfortunately, so might post some SQL tomorrow for you to kick round
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  9. #9
    SitePoint Addict kevin_tremblay's Avatar
    Join Date
    Jul 2001
    Location
    Boston Area (North shore)
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hillsy post your sql and lets give it a look at, sounds to me this is an easy solution . I will check back and see if you posted code and give it a look at.
    Kevin Tremblay-- Sys Admin
    work: kevin_tremblay@hsgmed.com
    "The object of the journey is not to arrive."

  10. #10
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK...

    What I have is table listing Product Groups like so:

    Code:
    tblProductGrp
    --------------------------
    GroupName | ListOrder | ID
    --------------------------
    Group1    | 1         | 1 
    Group2    | 2         | 2 
    Foobar    | 4         | 3 
    Splon     | 3         | 5
    These are output onto a page, ordered by ListOrder.

    As part of the web-based admin for this, I need to be able to reorder the output (i.e. change the ListOrder values). Currently this is done by using "Move this value up" and "Move this value down" URLs which pass the following parameters:

    Current ListOrder value = #URL.CurrentOrd#
    ID = #URL.ID#
    Action (move up or down) = #URL.Action#

    The code to process this is (in part):

    Code:
      <cfif URL.Action is "MoveUp">
      
        <!--- error catching --->
        <cfif URL.CurrentOrd is 1>
          <cfoutput>
            <p>You cannot move this value any further up the list</p>
            <p>Go <a href="sup_options.cfm">back</a></p>
          </cfoutput>
          <cfabort>
        </cfif>
    
        <!--- decrement the ListOrder by 1 --->
        <cfquery datasource="SuppInfo" dbtype="ODBC" name="MoveGrpUp">
         UPDATE tblProductGrp
         SET ListOrder = #URL.CurrentOrd#-1
         WHERE ID = #URL.ID#
        </cfquery>
      </cfif>
    There is no problem with incrementing/decrementing the ListOrder as shown above. But what I want to be able to do is increment one and decrement the value immediately above it (or vice versa). So I need to pass an UPDATE that updates two records at once. Access seems to have problems with that (and doesn't like it if I try passing two sequential statements either - really funny things happen then).

    Ideas?
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  11. #11
    SitePoint Addict ownersite's Avatar
    Join Date
    Jul 2001
    Posts
    222
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hillsy,

    Why not just run two queries? Is there a particular reason why it has to be done with one?
    F150online- 102,000 users strong and growing...
    Ownersite.com
    - tools to enhance vehicle ownership

  12. #12
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Access doesn't seem to like two. Not sure why. Will post the sympotms when I get a bit more time (just about to go home from work )
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  13. #13
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Hillsy,

    I've done sequential updates in access without any trouble...

    Just a thought, try sticking in a CFLOCATION after the first update, that points to a template that performs the second update. You shouldn't have to do this, but it sounds like the server is locking the table for some reason, and this might release it.

  14. #14
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmmm - now that's an idea

    I'll give it a go and let you know how I get on...
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  15. #15
    SitePoint Addict ownersite's Avatar
    Join Date
    Jul 2001
    Posts
    222
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe I misstated what I meant...I didn't mean to place two queries inside a <CFQUERY> tag, rather to use multiple <CFQUERY> tags...

    <CFQUERY>
    update record 1
    </CFQUERY>

    <CFQUERY>
    update record 2
    </CFQUERY>
    F150online- 102,000 users strong and growing...
    Ownersite.com
    - tools to enhance vehicle ownership

  16. #16
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey hillsy, what happened?
    We're curious.

  17. #17
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm @ work and it's on my list of things to do today

    Will let you know.... <drumroll>
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  18. #18
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    You should be able to execute one query after the other. I don't know much about CF, but cant you just set the query, execute it, and then do this again?
    Get ConMan and run your own web site!

    Want free programming eBooks? http://www.devarticles.com/ebooks.php

  19. #19
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For all those who were following this saga, it turned out to be embarrassingly simple

    I just needed to swap round my sequential updates and change #URL.CurrentOrd#-1 to #URL.CurrentOrd#

    I must have been tired not to pick that up sooner....

    Here's the code anyways:

    Code:
    <!--- move the entry below the selected value one up the list order --->
    <cfquery datasource="foo" dbtype="ODBC" name="MoveGrpUp">
        UPDATE tblProductGrp
        SET ListOrder = #URL.CurrentOrd#
        WHERE ListOrder = #URL.CurrentOrd#+1
    </cfquery>
    
    <!--- move the selected entry one down the list order --->
    <cfquery datasource="foo" dbtype="ODBC" name="MoveGrpDown">
        UPDATE tblProductGrp
        SET ListOrder = #URL.CurrentOrd#+1
        WHERE ID = #URL.ID#
    </cfquery>
    You just swap a few things round to move the group the other way....
    Last edited by hillsy; Jan 4, 2002 at 00:02.
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark


Bookmarks

Posting Permissions

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