OK. I got really stumpped the solution seems to be very simple, but anyway. in my survey form I need . answer question . and then click save. Then If the user go in the modyfy the question i want to delete the prevoius choices and insert the new ones.
I have to 2 tables to do that .() Survey_result: id,surveyidfk,empId, subjectID,completiondate SurveyResultsDetail: id, answer,surveyid,idfk(foreing key from the previous table). So what i am doing is everytime i go in I wanto delete all records from SurveyresultsDetail table where idfk is = id from the Survey_result. bUt it does not work <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";> <html xmlns="http://www.w3.org/1999/xhtml";> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Untitled Document</title> <script language="javascript" src="../../SunrisePortal/Includes/common.js"></script> </head> <cfquery name="qrySurveyReview" datasource="cfelsunrise" dbtype="odbc"> Select * from surveys where id=#url.survey# </cfquery> <cfquery name="qryQuestionReview" datasource="cfelsunrise" dbtype="odbc"> select * from questions where surveyidfk=#url.survey# order by sortOrder </cfquery> <!---get answers---> <body> <cfprocessingdirective suppresswhitespace="yes"> <form name="survey1" id="survey1" method="post" action=""> <div style="border:thin ##CC0000 solid; width:100%;"> <cfif isDefined("url.survey")> <!---Header of the survey---> <cfoutput query="qrySurveyReview"> <p align="center">#UCase(name)#</p> <div align="center"><img src="../images/#logo#"alt="#name#" /></div> <p align="center"><b style="font-size:18px;">Description:</b>#description#</p> </cfoutput> <cfoutput> <cfloop query="qryQuestionReview"> #header# <div style=" text-indent:5em;"> <br /><b>#qryQuestionReview.currentrow#.#question#</b> </div><br /> <cfif qryQuestionReview.recordcount gt 0> <cfquery name="qryQuestionOptionsReview1" datasource="cfelsunrise" dbtype="odbc"> select * from questionOptions where questionID=#qryQuestionReview.id# order by sortOrder </cfquery> <cfloop query="qryQuestionOptionsReview1"> <cfquery name="qryAnswers" datasource="cfelsunrise" dbtype="odbc" > select * from surveyResultsDetail where idfk=#results# </cfquery> <span style=" margin-left:5em;"> <cfset selectOptions=''> <cfswitch expression="#questiontypeidfk#"> <cfcase value="7"> <input type="checkbox" name="chk_#id#" id="chk" value="#options#_#id#" <cfif qryAnswers.answer eq #id#>checked</cfif> /> #options#<br /> </cfcase> <cfcase value="5"> <input type="text" name="txt#questionID#" id="txt" value="#options#_#id#" />#options# <br /> </cfcase> <cfcase value="8"> <input type="radio" name="rdn#questionID#" id="rdn" value="#options#_#id#" <cfif qryAnswers.answer eq #id#>checked</cfif>/> #Options#<br /> </cfcase> <cfcase value="6"> <textarea name="txtArea#questionID#" id="txtArea" cols="70" rows="5">#options#</textarea> <br /> </cfcase> <cfcase value="9"> <cfset selectOptions = selectOptions& '<option value="' & id & '">' & options & '</option>'> </cfcase> <cfcase value="10"> <input class="calendarButton" type="button" style=" background:url(../../SunrisePortal/Cal/Grid.GIF); width:23px;" onclick="displayDatePicker('shift1', this);"/> <input name="shift1#id#" id="shift1" class="inputDatePicker" disabled="disabled" ><br/> </cfcase> </cfswitch> </span> </cfloop> </cfif> #footer# </cfloop> <cfif len(trim(selectOptions)) gt 0> <select name="sel" id="sel"> <cfoutput>#selectOptions#</cfoutput> </select> <br /> </cfif> <br /> <input type="submit" name="btnSubmitSurvey" id="btnSubmitSurvey" value="Submit And Come Back" /> <input type="submit" name="btnSubmitSurvey2" id="btnSubmitSurvey2" value="Complete Survey" /> </cfoutput> <!---Actual questions and options---> </cfif> </div> </form> </cfprocessingdirective> </body> </html> <cfif isDefined("btnSubmitSurvey")> <cfoutput> <cfif isDefined("form.fieldnames")> <cfquery name="qryInsert" datasource="cfelsunrise" dbtype="odbc"> insert into survey_results (surveyidfk, empID) values (#url.survey#, #session.EmpID#) select @@Identity as newID from survey_results </cfquery> <cfset results = #qryInsert.newID#> <CFLOOP LIST="#form.fieldnames#" Index="field"> <cfif #field# neq "btnSubmitSurvey" and #field# neq "btnSubmitSurvey2"> <cfquery name="qryInsert1" datasource="cfelsunrise" dbtype="odbc"> delete from SurveyResultsDetail where idfk =#qryInsert.newID# insert into SurveyResultsDetail (answer,surveyID,idfk) values (#ListLast(Evaluate("Form.#field#"),"_")#,#url.survey#,#qryInsert.newID#) </cfquery> <cfelse> </cfif> </CFLOOP> </cfif> <!--- insert into answers (questionidfk,answer,empID,surveyID) values (#ListLast(Evaluate("Form.#field#"),"_")#,'#Evaluate(field)#',#session.empID#,#url.survey#) surveyID=#url.survey# and answer=#ListLast(Evaluate("Form.#field#"),"_")#---> <cflocation url="index.cfm" addtoken="no"> </cfoutput> </cfif> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304663 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
