Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2006
    Posts
    11

    Unanswered: Updating multiple records with conditions

    I'm trying to update a single field in a table according to the an age category as a condition.

    The meat of the code that executes looks like this (I created a record set, and loop through it until there are no records):

    Code:
    Do While Not rstRecSet.EOF 
      
        ' gets the difference between todays date and db player date
        intDifference = DateDiff("yyyy", CDate(rstRecSet("BIRTHDATE")), Date)
        
        If intDifference <= 8 Then
        	SQLUpdate = "UPDATE Player SET DIVISION = 'LEARN TO PLAY';"
        ElseIf intDifference <= 10 Then
    	SQLUpdate = "UPDATE Player SET DIVISION = 'MITES';"
        etc.....
        End If
    
       ' execute my sql statement here to update record
       oC.execute(SQLUpdate)
    	
       ' move to next record to update....
        rstRecSet.MoveNext
        	
    Loop
    I did a response.write for the different in age and it shows the difference between ages for all entries in the database (good), but it will only update the first entry and set every single entry in the database according to the first entry (so if the age difference is 17, it will set the division properly, but every single record is going to have that division name, regardless of their age category from the first to last record).

    Am I out to lunch here, on the right track? Is there possibly a better way of doing this that I'm unaware of? An SQL update statement with conditions?
    Any sort of help would be appreciated.

  2. #2
    Join Date
    Jun 2006
    Posts
    11
    Ok, figured out the problem. Sat down and thought about it, turns out I needed a WHERE clause in those updates. It would always take the last value in the record and whatever the criteria was, all values would be based on it, makes sense now. All working good with the WHERE clause.

Posting Permissions

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