I have a dynamically created table in an .html form and I want to do an insert in the .cfm form. I need to loop through the rows of the dynamically created table and perform an INSERT into a table in SQL Server. Also, the table was created in JavaScript. Thanks.
<cfoutput>
<cfloop from="1" to="#ArrayLen(tblSample)#" index="i">
<cfquery name="AppendForm" datasource="TestSource">
INSERT INTO tblGrand
(GrandNum,
GrandName,
County,
VersionType,
VersionNum,
SectCode,
Comments,
Provider,
TypeID,
SubmitDate)
Select
<cfif isdefined("form.GrandNum")>
'#GrandNum#',
<cfelse>
null,
</cfif>
<cfif isdefined("form.GrandNme")>
'#GrandNme#',
<cfelse>
null,
</cfif>
<cfif isdefined("form.selCnty")>
'#selCnty#',
<cfelse>
null,
</cfif>
<cfif isdefined("form.VersionType")>
'#VersionType#',
<cfelse>
null,
</cfif>
<cfif isdefined("form.VersionNum")>
'#VersionNum#',
<cfelse>
null,
</cfif>
<cfif isdefined("form.SectCode[i]")>
'#SectCode[i]#',
<cfelse>
null,
</cfif>
<cfif isdefined("form.txtComments[i]")>
'#textComments[i]#',
<cfelse>
null,
</cfif>
<cfif isdefined("form.txtProvider[i]")>
'#txtProvider[i]#',
<cfelse>
null,
</cfif>
<cfif isdefined("form.selType[i]")>
'#selType[i]#',
<cfelse>
null,
</cfif>
'#DateFormat(Now(),"yyyy-mm-dd") &" "& TimeFormat(Now(),"HH:mm:ss")#'
</cfquery>
</cfloop>
</cfoutput>
Here is my html code for creating the table:
<script language="javascript" type="text/javascript">
function addRow() {
var tbl = document.getElementById('tblSample');
var lastRow = tbl.rows.length;
var iteration = lastRow;
var row = tbl.insertRow(lastRow);
// left cell
var cellLeft = row.insertCell(0);
var textNode = document.createTextNode(iteration-3);
cellLeft.appendChild(textNode);
// select cell
var cellRightSel = row.insertCell(1);
var sel = document.createElement('select');
sel.name = 'sectCode' + iteration;
sel.id = 'sectCode' + iteration;
sel.options[0] = new Option('---Any---', '0');
sel.options[1] = new Option('Level 0.5: test1, '1');
sel.options[2] = new Option('Level I: test2', '2');
sel.options[3] = new Option('Level I.D: test3', '3');
sel.options[4] = new Option('Level II.1: test4', '4');
sel.options[5] = new Option('Level II.5: test5', '5');
cellRightSel.appendChild(sel);
var cellRights = row.insertCell(2);
var els = document.createElement('input');
els.type = 'text';
els.name = 'txtComments' + iteration;
els.id = 'txtComments' + iteration;
els.size = 20;
cellRights.appendChild(els);
var cellRight = row.insertCell(3);
var el = document.createElement('input');
el.type = 'text';
el.name = 'txtProvider' + iteration;
el.id = 'txtProvider' + iteration;
el.size = 20;
cellRight.appendChild(el);
var cell7 = row.insertCell(8);
var sel5 = document.createElement('select');
sel5.name = 'selType' + iteration;
sel5.id = 'selType' + iteration;
sel5.options[0] = new Option('---Any---', '---Any---');
sel5.options[1] = new Option('Fees, 'Fees);
sel5.options[2] = new Option('Reimbursement', 'Reimbursement');
cell7.appendChild(sel5);
}
First off, what is your question? Are you getting an error? What results you are getting now - and how do they differ from what you expected?
Without knowing more about your form/data structure, I usually recommend using unique form field names in this case. When you use the same name for multiple fields, the field values are submitted as a comma delimited list. That can cause problems when the value itself contains a comma, because there is no way to determine where one value ends and the other begins.
To create unique field names, have your javascript append a counter number to each set of fields. So the fields will be named:
Then store the total number in a hidden field. On your action page, do a simple from/to loop. If needed, use cfparam to set a default value for any fields that might not exist like checkboxes or radio buttons (text fields always exist). Then you can use null attribute of cfqueryparam which is a bit cleaner than isDefined or structKeyExists IMO.
<cfparam name="FORM.totalFields" default="0">
<cfloop from="1" to="#FORM.totalFields#" index="i">
<!--- extract current set of values --->
<cfset variables.txtComments = TRIM( FORM["txtComments"& i] )>
<cfset variables.sectCode = TRIM( FORM["sectCode"& i] )>
...
<cfquery name="AppendForm" datasource="TestSource">
INSERT INTO tblGrand (Comments, SectCode ....)
VALUES
(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.txtComments#" null="#not len(variables.txtComments)#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.sectCode#" null="#not len(variables.sectCode)#">
...
)
</cfquery>
</cfloop>
UPDATE:
Based on your updates, your existing code is very close to what I originally described above. You just need to add the hidden field, and update it each time you add a row. Then the cfloop code above should work just fine.
function addRow() {
// removed extra variables
var tbl = document.getElementById('tblSample');
var row = tbl.insertRow(tbl.rows.length);
// use hidden value to calculate total fields
var iteration = parseInt(document.getElementById('totalFields').value) + 1;
var cellLeft = row.insertCell(0);
var textNode = document.createTextNode(iteration);
... etcetera ...
// save new total
document.getElementById('totalFields').value = iteration;
}
...
<input type="hidden" id="totalFields" name="totalFields" value="0" />
UPDATE:
A few other comments
cfoutput around the query loop. The variables will be evaluated automaticallycfqueryparam when processing user supplied values to prevent sql injection. Especially when looping. cfqueryparam uses bind variables which can "enhance performance when executing a cfquery statement multiple times."DateFormat in queries. It is designed for presentation and returns a string. Date strings can be misinterpreted depending on your database settings. To avoid ambiguity always insert date/time objects ie like now() and not strings. See Matt's example and his usage of now().