Avatar of Ru1995
Ru1995
Flag for United States of America asked on

Need help inserting records using dynamic form fields using Coldfusion MX 7

I am having trouble inserting the results of a survey into a table.  I have to do an insert on 3 different tables and on the last table it somehow keeps inserting over a thousand records.

This question is related to another one that was previously answered by agx.  Anyway I'm attaching my code as well as my db schema.

I have commented on the insert statement that is causing all this trouble.  Let me know if you have any questions.  


database-schema.txt
ColdFusion Language

Avatar of undefined
Last Comment
_agx_

8/22/2022 - Mon
Ru1995

ASKER
Seems that the code didn't attach correctly here it is again
<cfparam name="form.numDeptTypes" default="0">
   <cfparam name="form.numScoreTypes" default="0">
   
   <cfset emplid = '1112232'>
   <cfset CurrentDate = DateFormat(Now(),"yyyymmdd")>
<cfset CurrentTime = TimeFormat(Now(),"HH:mm:ss")>
<cfset DateTimeString = CurrentDate & CurrentTime>

   
   <!---Insert values to the response table--->
   <cfquery name="qInsertResponse" datasource="#application.dsn#">
		Insert into responses
    	Values
    	('#emplid#'
    	,'#CurrentDate#'
    	)
	</cfquery> 

	<cfquery name="qSelectResponses" datasource="#application.dsn#">
        select *
        from responses
        where user_id = '#emplid#'
        and responseDate = '#currentDate#'
	</cfquery>

   
   <cfloop from="1" to="#form.numDeptTypes#" index="deptRow">
   		
   
      <!--- get department id and any comments --->
      <cfset deptTypeID = FORM["deptType_"& deptRow]>
      <cfset deptNotes  = FORM["notes_"& deptRow]>
	
      <!--- debugging display --->
      <cfdump var="DEPT [#deptTypeID#]">
      <cfdump var="NOTES[#deptNotes#]"><hr>

      <!--- get score type and rating --->
      <cfloop from="1" to="#form.numScoreTypes#" index="scoreRow">
         <!--- get score type and rating --->
         <cfset scoreTypeID = FORM["scoreType_"& deptRow &"_"& scoreRow]>
         <cfset ratingValue = FORM["rating_"& deptRow &"_"& scoreRow]>	
         
   	    <cfquery name="qResponseDepts" datasource="#application.dsn#">
            Insert into responseDepts
            Values
            ('#emplid#'
            ,#qSelectResponses.response_id#
            ,#deptTypeID#
            ,'#deptNotes#'
            ,#scoreTypeID#
            )
	    </cfquery>
	
    <cfquery name="qSelectResponseDept" datasource="#application.dsn#">
    	Select *
        from responseDepts
        where response_id = #qSelectResponses.response_id#
        
     </cfquery>   
   <cfloop query="qSelectResponseDept">      
   	 <!---this insert statement is whats creating over 1000 records--->
     <!---The qSelectResponseDept query that I'm outputting only returns 48 records so therefore 
	 the insert on the responseDeptScores table should only insert 48 records--->
     <!---For some reason I can't get the correct rating value with the department id and score type id--->
     <cfquery name="qInsertResponseScores" datasource="#application.dsn#">
     	insert into responseDeptScores
     	Values
        (
        	#qSelectResponseDept.respDept_Id#
            ,#qSelectResponseDept.scoreType_Id#
            ,#ratingValue#
        )    
      </cfquery>
	</cfloop>
         
         <!--- debugging display --->
         <cfdump var="[scoreType=#scoreTypeID# / rating=#ratingValue#]">
      </cfloop>
      <br><br>
   </cfloop>
<!---<cfdump var="#form#">--->
<cfabort>

Open in new window

_agx_

>> <cfloop query="qSelectResponseDept">

It's almost certainly the extra query loop. Your loops are going to loop almost exactly like the example in the other thread.  Just with a few INSERT queries added in strategic places.

>> I have to do an insert on 3 different tables

IIRC, it involves 3 steps
1) insert a single record into "responses"
2) insert 1 record per dept into "responseDepts"
3) insert 1 record per dept/score type into "responseDeptScores".

Take it one step at a time.  First create your survey record AND retrieve the record ID. You'll need it for your other queries. Do you see the new ID? If yes, we'll go on to the next INSERT.

<cfparam name="form.numDeptTypes" default="0">
<cfparam name="form.numScoreTypes" default="0">
   
<cfquery name="qInsertResponse" datasource="#application.dsn#" result="responseResult">
	INSERT INTO responses ( user_id, esponseDate )
    	VALUES 
    	( <cfqueryparam value="#emplid#" cfsqltype="cf_sql_varchar">
    	,  <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">
    	)
</cfquery> 

<cfset newSurveyID = responseResult.IDENTITYCOL>
<cfdump var="#responseResult#">  <== do you see the new record ID here? 

Open in new window


ASKER CERTIFIED SOLUTION
_agx_

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
_agx_

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ru1995

ASKER
<cfset newSurveyID = responseResult.IDENTITYCOL>
<cfdump var="#responseResult#">  <== do you see the new record ID here?

I actually got an error with this saying Element IdentityCol is undefined in responseResult.

So I went with my original method which involved doing a select query of the record that was inserted into the responses table

 <!--- save new record id for later --->
     <cfset newResponseDeptID = deptResult.IDENTITYCOL>
     <cfdump var="newResponseDeptID = #newResponseDeptID#">  <=== verify you see new record ID

Same thing with this error it reads Element IDENTITYCOL is undefined in DEPTRESULT.

Should I use a select statement with this as well or is there a work around?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Ru1995

ASKER
ok I apologize I didn't see the result attribute on the query.  My mistake
_agx_

Ok. Let me know if you have any problems plugging in each step.
Ru1995

ASKER
actually the IDENTITYCOL doesn't work either way I do it I'm still getting the error.  It could be that we are using a sql server 2000 db in our test environment.  Should I go with using a select statement instead?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
_agx_

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ru1995

ASKER
ok well also on the insert statement for the responseDepts you have the insert query inside the deptRow indexed loop.

However the table also holds a surveyID, which doesn't get set until the scoreRow indexed loop.  When I placed the loop inside the scoreRow loop it inserted 48 records into the responseDepts table

Is that correct? I'm attaching the code so you can compare.
<!---Insert values to the response table--->
   <cfquery name="qInsertResponse" datasource="#application.dsn#">
	INSERT INTO responses ( user_id, responseDate )
    	VALUES 
    	( <cfqueryparam value="#emplid#" cfsqltype="cf_sql_varchar">
    	,  <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">
    	)
        SELECT SCOPE_IDENTITY() AS ResponseID;
	</cfquery> 


    <cfset newSurveyID = qInsertResponse.ResponseID>
    <cfdump var="#newSurveyID#"> 


	  
   <cfloop from="1" to="#form.numDeptTypes#" index="deptRow">
   		
   
       <!---get department id and any comments --->
      <cfset deptTypeID = FORM["deptType_"& deptRow]>
      <cfset deptNotes  = FORM["notes_"& deptRow]>
	
      <!--- debugging display --->
      <cfdump var="DEPT [#deptTypeID#]">
      <cfdump var="NOTES[#deptNotes#]"><hr>
      
      
       

     

      <!--- get score type and rating --->
      <cfloop from="1" to="#form.numScoreTypes#" index="scoreRow">
          <!---get score type and rating---> 
         <cfset scoreTypeID = FORM["scoreType_"& deptRow &"_"& scoreRow]>
         <cfset ratingValue = FORM["rating_"& deptRow &"_"& scoreRow]>	
         <cfquery name="qResponseDepts" datasource="#application.dsn#" result="deptResult">
             INSERT INTO responseDepts (  
                user_id
                ,response_id
                ,deptType_id
                ,comment
                ,scoreType_id
                )
              VALUES (
              <cfqueryparam value="#emplid#" cfsqltype="cf_sql_varchar">
              , <cfqueryparam value="#newSurveyID#" cfsqltype="cf_sql_integer">
              , <cfqueryparam value="#deptTypeID#" cfsqltype="cf_sql_integer">
              , <cfqueryparam value="#deptNotes#" cfsqltype="cf_sql_varchar">
              ,<cfqueryparam value="#scoreTypeID#" cfsqltype="cf_sql_integer">
                )
              SELECT SCOPE_IDENTITY() AS NewResponseID;
     	 </cfquery>
   	    
 		<!--- save new record id for later --->
     <cfset newResponseDeptID = qResponseDepts.NewResponseID>
     <cfdump var="newResponseDeptID = #newResponseDeptID#">  <=== verify you see new record ID 

          <!---debugging display---> 
         <cfdump var="[scoreType=#scoreTypeID# / rating=#ratingValue#]">
      </cfloop>
      <br><br>
   </cfloop>

Open in new window

_agx_

>> However the table also holds a surveyID
You mean "scoreType_id"?  I deliberately left it out because I thought it was a typo. That column isn't in db schema you and gdemaria discussed and it doesn't really make sense in that table. "responseDepts" is supposed to contain 1 record per survey + dept.  You don't need to add scoreType_id because it's already stored in the 3rd table.

>> Is that correct?
No, go back to the original loop code.


Ru1995

ASKER
I'm kind of wondering if the scoreType_Id needs to be in the responseDepts table because all its holding it seems like is the comments of the user. Whenever it does its insert it inserts 4 comments for the same department but gives them different scoreType_id's  

Which leads me to believe that it might not need to be there.  What do you think?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Ru1995

ASKER
ok I deleted it
_agx_

The overall inserts look more like this. Make sense?

<!--- create survey --->
INSERT INTO responses ( user_id, responseDate ) ...
<cfloop from="1" to="#form.numDeptTypes#" index="deptRow">
      <!--- save department comments --->
      INSERT INTO responseDepts (user_id, response_id, deptType_id) ....

      <cfloop from="1" to="#form.numScoreTypes#" index="scoreRow">
          <!--- save scores by category --->
          INSERT INTO responseDeptScores ( respDept_id, scoreType_id, score )    ...
      </cfloop>
</cfloop>
_agx_

oops, our posts clashed. I think we're on the same page now.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ru1995

ASKER
Awesome it worked thank you so much.  I still have a lot to do for this project but it's nice to know I can count on you
_agx_

Glad I could help :)