Hi, I'm getting a MySQL error in a handler that is based on the newRecord handler from the Working with Databases sample stack.
This handler is updating a table named 'groups'. The columns are id, grp_id, member_id There are currently 60 rows, with id = 1 to 60 Whereas in the newRecord handler in the sample stack, the incoming parameter is just the pRecordID, I'm also passing to my handler a comma-delimited list of groups the pRecordID person is assigned to. As in the original newRecord script, the first INSERT command is supposed to create a new record in the table. Then, a LAST_INSERT_ID command is issued to get the ID of the new record. Then an UPDATE command is issued to post the values to the new row. In this example, there are two groups in the pGrpList parameter. As I iterate through them, the first one appears to get posted OK (but not really), but the second time through I'm getting the error: Duplicate entry '0' for key 1 When I check the database in phpMyAdmin, a new row with id = 0 has been created -- presumably by the first iteration. On that first iteration, the LAST_INSERT_ID is consistently returning 3084. Since there are only 60 rows, that seems a weird number. It is, however, a valid member_id for someone who appears in the table. So, maybe there's more than one problem? The failure on the second iteration makes me think there's a database cursor problem, but I don't know how to resolve that, if that's what it is. Here's the script... global gRTdbID # following assumes MySQL queries on updateGroups pRecordID,pGrpList # looking in the debugger, # pRecordID is valid; it's "1009" # pGrpList is valid; it's "8,12" startLoading set the cursor to busy # first, delete the existing rows if any revExecuteSQL gRTdbID, "DELETE FROM groups WHERE member_id = :1", "pRecordID" get the result -- just checking in the debugger: it's 0 # now add the new list (a comma-delimited list of groups to which this person is assigned) if pGrpList is empty then put 10 into pGrpList -- default group repeat for each item tGroup in pGrpList revExecuteSQL gRTdbID, "INSERT INTO groups() VALUES()" put the result into tRes if tRes is not a number or tRes < 0 then error tRes end if put revDataFromQuery(comma, return, gRTdbID, "SELECT LAST_INSERT_ID()") into tLastID # at this point, tLastID is 3084. But, there are only 60 rows in the table... ? if item 1 of tLastID is "revdberr" then error tLastID else revExecuteSQL gRTdbID, "UPDATE groups SET grp_ID = :1, member_ID = :2 WHERE id = :3", \ "tGroup", "pRecordID", "tLastID" end if end repeat stopLoading end updateGroups Any ideas? Thanks, -- Paul _______________________________________________ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution