Re: insert /delet problem in coldfusion
The problem is that instead of posting these kinds of questions (which are usually just hundreds of lines of code with the request to fix it), Erik needs to read a book on ColdFusion and a book on SQL. I've asked (begged) him to do this so many times that my fingers are numb, but he just refuses to do it. So my proposal is that everyone just stop responding to his posts until he a) figures out how to ask for help properly, b) reads a SQL book, and c) reads a ColdFusion book. Everything he asks about is explained in full detail in any book on these subjects, so at this point all he's doing is wasting other people's time. Erik, you've got to do this, that's all there is to it. You'll thank me later. On Fri, May 2, 2008 at 12:34 PM, Tom McNeer [EMAIL PROTECTED] wrote: There's a lot in your code that should probably be changed -- first, I'm assuming that the form/database code at the bottom of your post is NOT part of the same page as the form -- but you don't need the cfoutput tags in the processing code, or most of the hash signs, and you could avoid the use of Evaluate. But most importantly, there are two big problems in the database code if this is supposed to be an update (you say the user is modifying the answers. cfif isDefined(form.fieldnames) cfquery name=qryInsert datasource=cfelsunrise dbtype=odbc insert into survey_results (surveyidfk, empID) values (#url.survey#, #session.EmpID#) Here you're always doing an insert -- creating a new survey result record. Is that what you're really trying to do? You said modify, but you're saving a new record. Then ... 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 . you are trying to delete records from the Detail table based on the ID of the new record, and there aren't any in the first iteration of the loop. You just created the Results record. Then you're immediately inserting a record into the Details table. But you are doing this within the loop of the fieldnames, so the next iteration of the loop deletes the record you just created. At the end you have one Detail record, from the last iteration of the loop, and it's related to the new Result record you just created. -- Thanks, Tom Tom McNeer MediumCool http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 ~| 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:304706 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
insert /delet problem in coldfusion
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 /
titleUntitled 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 ##CC solid; width:100%;
cfif isDefined(url.survey)
!---Header of the survey---
cfoutput query=qrySurveyReview
p align=center#UCase(name)#/p
div align=centerimg src=../images/#logo#alt=#name# //div
p align=centerb 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
/divbr /
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
Re: insert /delet problem in coldfusion
There's a lot in your code that should probably be changed -- first, I'm assuming that the form/database code at the bottom of your post is NOT part of the same page as the form -- but you don't need the cfoutput tags in the processing code, or most of the hash signs, and you could avoid the use of Evaluate. But most importantly, there are two big problems in the database code if this is supposed to be an update (you say the user is modifying the answers. cfif isDefined(form.fieldnames) cfquery name=qryInsert datasource=cfelsunrise dbtype=odbc insert into survey_results (surveyidfk, empID) values (#url.survey#, #session.EmpID#) Here you're always doing an insert -- creating a new survey result record. Is that what you're really trying to do? You said modify, but you're saving a new record. Then ... 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 . you are trying to delete records from the Detail table based on the ID of the new record, and there aren't any in the first iteration of the loop. You just created the Results record. Then you're immediately inserting a record into the Details table. But you are doing this within the loop of the fieldnames, so the next iteration of the loop deletes the record you just created. At the end you have one Detail record, from the last iteration of the loop, and it's related to the new Result record you just created. -- Thanks, Tom Tom McNeer MediumCool http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 ~| 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:304670 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
