On Fri, Sep 13, 2002 at 04:08:00PM -0500, Evan Simpson wrote:
Tim Wilson wrote:
When I attempt to update an event score, it works perfectly unless one of the scores is 0. The SQL update completely ignores the zero value and leaves it as NULL. If I go back and enter two non-zero numbers, things work fine. If I then go back and change one of the scores to a 0, that change is ignored and the old non-zero score remains.
My first reaction to these symptoms is that you've got a Z SQL Method with roughly the following in it somewhere:
<dtml-if score> UPDATE .... </dtml-if>
Hard to tell without more info, tho.
OK Evan. You asked for it. :-) I'm going to paste in the offending code. As always, any feedback is greatly appreciated. The formatting will likely get messed up in the pasting process. Here's the DTML method that contains the event edit form. Note that the scores are stored as integers. editEventForm (DTML Method) =========================== <dtml-var standard_html_header> <dtml-in expr="qry_getEventDetails(event_id=event_id)" size="1"> <form action="doUpdateEvent" method="post"> <table cellpadding="5"> <tr class="table-header"><th colspan="2" align="left">Edit an event</th></tr> <tr> <th align="right">ID</th> <td><dtml-var event_id><input type="hidden" name="event_id:int" value="&dtml-event_id;"></td> </tr> <tr> <th align="right">Name/Opp</th> <td><input type="text" name="event_name" value="&dtml-event_name;" size="30"></td> </tr> <tr> <th align="right">Location</th> <td> <dtml-call expr="REQUEST.set('loc_id', location_id)"> <select name="location_id:int"> <dtml-in expr="qry_getlocations(location_id='', location_name='', city='')"> <dtml-if expr="location_id == loc_id"> <option value="&dtml-location_id;" selected> <dtml-else> <option value="&dtml-location_id;"> </dtml-if> <dtml-if room><dtml-var room>, </dtml-if> <dtml-var location_name>, <dtml-var city> </option> </dtml-in> </select> </td> </tr> <tr> <th align="right">Participant(s)</th> <td> <select name="participant_ids:list:int" size="5" multiple> <dtml-in qry_getParticipantsByPermission> <dtml-call expr="REQUEST.set('part_id', participant_id)"> <dtml-in expr="qry_getParticipants(event_id=event_id)"> <dtml-if expr="participant_id == part_id"> <option value="&dtml-participant_id;" selected><dtml-var participant></option> </dtml-if> </dtml-in> <option value="&dtml-participant_id;"><dtml-var participant></option> </dtml-in> </select> </td> </tr> <tr> <th align="right">Start time</th> <td><input type="text" name="start_time:datetime" value="<dtml-var start_time fmt="%Y-%m-%d %H:%M" null="">" size="30"></td> </tr> <tr> <th align="right">End Time</th> <td><input type="text" name="end_time:datetime" value="<dtml-var end_time fmt="%Y-%m-%d %H:%M" null="">" size="30"></td> </tr> <tr> <th align="right">Description</th> <td><input type="text" name="description" value="<dtml-var description null="">" size="30"></td> </tr> <dtml-if expr="event_type == 'meeting'"> <!-- Options for meeting-type events --> <tr> <th align="right">Agenda URL</th> <td><input type="text" name="agenda_url" value="<dtml-var agenda_url null="">" size="40"></td> </tr> <tr> <th align="right">Minutes URL</th> <td><input type="text" name="minutes_url" value="<dtml-var minutes_url null="">" size="40"></td> </tr> <dtml-elif expr="event_type == 'game'"> <!-- Event is a game --> <tr> <th align="right">Our score</th> <td><input type="text" name="our_score:int" value="<dtml-var our_score null="">" size="5"></td> </tr> <tr> <th align="right">Their score</th> <td><input type="text" name="their_score:int" value="<dtml-var their_score null="">" size="5"></td> </tr> <dtml-elif expr="event_type == 'tournament'"> <!-- It's a tournament --> <tr> <th align="right">Finish</th> <td><input type="text" name="finish" value="<dtml-var finish null="">" size="20"></td> </tr> </dtml-if> <tr> <td> </td> <td><input type="submit" value=" Submit edits "><input type="reset" value=" Clear form "></td> </tr> </table> <input type="hidden" name="username" value="<dtml-var expr="AUTHENTICATED_USER.getUserName()">"> <input type="hidden" name="event_type" value="&dtml-event_type;"> </form> </dtml-in> <dtml-var standard_html_footer> doUpdateEvent (DTML Method) (Sorry about the formatting) =========================== <dtml-var standard_html_header> <dtml-if expr="event_type == 'game'"> <dtml-call expr="qry_updateEvent(event_id=event_id, event_name=event_name, location_id=location_id, start_time=start_time, end_time=end_time, description=description, our_score=our_score, their_score=their_score, username=username, participant_ids=participant_ids)"> <dtml-elif expr="event_type == 'tournament'"> <dtml-call expr="qry_updateEvent(event_id=event_id, event_name=event_name, location_id=location_id, start_time=start_time, end_time=end_time, description=description, finish=finish, username=username, participant_ids=participant_ids)"> <dtml-elif expr="event_type == 'meeting'"> <dtml-call expr="qry_updateEvent(event_id=event_id, event_name=event_name, location_id=location_id, start_time=start_time, end_time=end_time, description=description, agenda_url=agenda_url, minutes_url=minutes_url, username=username, participant_ids=participant_ids)"> </dtml-if> <p class="hilite-text"><dtml-var event_name> updated.</p> <dtml-var standard_html_footer> qry_updateEvent (Z SQL Method) ============================== UPDATE event SET event_name = <dtml-sqlvar event_name type="nb">, location_id = <dtml-sqlvar location_id type="int">, start_time = <dtml-sqlvar start_time type="nb">, end_time = <dtml-sqlvar end_time type="nb" optional="true">, description = <dtml-sqlvar description type="nb" optional="true">, <dtml-if our_score>our_score = <dtml-sqlvar our_score type="int" optional="true">,</dtml-if> <dtml-if their_score>their_score = <dtml-sqlvar their_score type="int" optional="true">,</dtml-if> <dtml-if finish>finish = <dtml-sqlvar finish type="nb" optional="true">,</dtml-if> <dtml-if agenda_url>agenda_url = <dtml-sqlvar agenda_url type="nb" optional="true">,</dtml-if> <dtml-if minutes_url>minutes_url = <dtml-sqlvar minutes_url type="nb" optional="true">,</dtml-if> username = <dtml-sqlvar username type="nb"> WHERE <dtml-sqltest event_id op="eq" type="int"> <dtml-var sql_delimiter> DELETE FROM event_participant WHERE <dtml-sqltest event_id op="eq" type="int"> /* Remove references to event */ <dtml-var sql_delimiter> <dtml-in participant_ids> /* Reinsert references to events */ INSERT INTO event_participant (participant_id, event_id) VALUES ( <dtml-sqlvar sequence-item type="int">, <dtml-sqlvar event_id type="int"> ) <dtml-var sql_delimiter> -Tim -- Tim Wilson | Visit Sibley online: | Check out: Henry Sibley HS | http://www.isd197.org | http://www.zope.com W. St. Paul, MN | | http://slashdot.org wilson@visi.com | <dtml-var pithy_quote> | http://linux.com