Another cfloop dynamic insert question (after searching the others)

[djkhalif]

Good evening all,

My issue is:

I have products that have 1 to 4 parts. Tankcount tells me how many parts I have per product then dynamically produce the correct number of fields. On insert, I'm getting numbers that aren't what I want. CVR and CTR should equal parts count and my insert should be:

<cfif tankcount eq 3>

<cfqueryparam value=#SN#>, <cfqueryparam value=#CVR1#>,, <cfqueryparam value=#CTR1#>,,......

<cfelseif tankcount eq 2>

However, my table should have

SN, CVR1, CVR2, CVR3, CVR4, CTR1, CTR2, CTR3, CTR4, testduration, daterecorded

What i'm getting is 60 records or more per insert instead of one record per SN.

Form:

<cfoutput query="tankSearch" group="SN">
<li><cfinput name="SN#CurrentRow#" type="text" value="#tanksearch.SN#" readonly/></li>
<ul>
<cfset tankcount = tanksearch.cells>

<cfloop from="1" to="#tankcount#" index="c">
<li>
Voltage#c#:  <cfinput type="text" name="CVR#c#"/>Time:  <cfinput name="CTR#c#" type="text" /><br />
</li>
</cfloop>
</ul>
</cfoutput>
</ul>
<cfinput type="submit" name="ieeeInsert" value="submit"/>
<cfoutput>
<cfinput type="hidden" name="howmany" id="howmany" value="#tanksearch.recordCount#"/>
</cfoutput>

<cfinput type="hidden" name="countrows" id="countrows" value="#tankcount#"/>

<cfinput type="hidden" name="daterecorded" id="daterecorded" value="#today#"/>

Insert:

<cfparam name="form.howmany" default="0">
<cfparam name="form.countrows" default="0">
<cfquery name="InsertIEEE" datasource="#REQUEST.datasource#">
<cfloop index="Add" from="1" to="#FORM.howmany#" step="1">
<cfset SN = FORM["SN"& Add]>
<cfloop index="c" from="1" to="#form.countrows#" step="1">
<cfset CVR = FORM["CVR"& c]>
<cfset CTR = FORM["CTR"& c]>
<cfset testDuration = FORM.testDuration>
<cfset daterecorded = dateformat(Now(), 'mm/dd/yyyy')>
insert into resultsIEEE(SN, CVR1, CVR2, CVR3, CVR4, CTR1, CTR2, CTR3, CTR4, testDuration, daterecorded)
values(<cfqueryparam value="#SN#">, <cfqueryparam value="#CVR1#">,<cfqueryparam value="#CVR2#">,<cfqueryparam value="#CVR3#">,<cfqueryparam value="#CVR4#">, <cfqueryparam value="#CTR1#">,<cfqueryparam value="#CTR2#">,<cfqueryparam value="#CTR3#">, <cfqueryparam value="#CTR4#">, <cfqueryparam value="#testduration#">, <cfqueryparam value="#daterecorded#">)
</cfloop>
</cfloop>
</cfquery>

[-__cfSearching__-]

What i'm getting is 60 records or more per insert instead

of one record per SN.

I am heading off to sleep. But a quick glance at the code shows you have an INSERT nested inside two cfloops. On each iteration of those loops, you are inserting a new database record. That is probably why you are ending up more records than expected. The loops are misplaced.

Though on a side note, the table structure seems less than idea ...

-Leigh