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

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>

Select allOpen in new window

>> <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? 

Select allOpen in new window

<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?

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

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

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?

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>

Select allOpen in new window

>> 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.

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?

ok I deleted it

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>

oops, our posts clashed. I think we're on the same page now.

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

Glad I could help :)