迭代通过动态创建的表和插入数据到SQL Server表 [英] Iterate through a dynamically created table and Insert data into SQL Server table
本文介绍了迭代通过动态创建的表和插入数据到SQL Server表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个.html形式的动态创建的表，我想在.cfm形式插入。我需要循环遍历动态创建的表的行，并在SQL Server中的表中执行INSERT。此外，表是在JavaScript中创建的。感谢。
< cfoutput>
< cfloop from =1to =＃ArrayLen（tblSample）＃index =i>
< cfquery name =AppendFormdatasource =TestSource>
INSERT INTO tblGrand
（GrandNum，
GrandName，
County，
VersionType，
VersionNum，
SectCode，
注释，
Provider，
TypeID，
SubmitDate）
选择
< 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>
这是我创建表的html代码：
< script language =javascripttype =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）;
//选择单元格
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] =新期权（'报销'，'报销'）;
cell7.appendChild（sel5）;
}
解决方案
题？您收到错误？
在不知道更多关于表单/数据结构的情况下，我通常建议使用唯一的表单字段名称在这种情况下。对多个字段使用相同的名称时，字段值将以逗号分隔列表的形式提交。当值本身包含逗号时，这可能会导致问题，因为没有办法确定一个值结束和另一个开始的位置。
要创建唯一的字段名称，您的JavaScript会将计数器编号附加到每个集合字段。因此，字段将命名为：
- FORM.GrandNum1，FORM.GrandNme1，FORM.sectCode1，FORM.txtComments1，...
-
- FORM.GrandNum2，FORM.GrandNme2，FORM.sectCode2，FORM.txtComments2，...
- FORM.GrandNum3，FORM.GrandNme3，FORM.sectCode3 ，FORM.txtComments3，...`
然后将总数存储在隐藏字段中。在您的操作页面上，从/到循环执行一个简单的。如果需要，可以使用 cfparam 为可能不存在的任何字段（如复选框或单选按钮（文本字段总是存在））设置默认值。然后你可以使用属性 cfqueryparam ，这比 isDefined 或 structKeyExists IMO。
< cfparam name = FORM.totalFieldsdefault =0>
< cfloop from =1to =＃FORM.totalFields＃index =i>
<！---提取当前值集合--->
< cfset variables.txtComments = TRIM（FORM [txtComments& i]）>
< cfset variables.sectCode = TRIM（FORM [sectCode& i]）>
...
< cfquery name =AppendFormdatasource =TestSource>
INSERT INTO tblGrand（Comments，SectCode ....）
VALUES
（
< cfqueryparam cfsqltype =cf_sql_varcharvalue =＃variables.txtComments＃null = #not len（variables.txtComments）＃>
，< cfqueryparam cfsqltype =cf_sql_varcharvalue =＃variables.sectCode＃null =＃not len（variables.sectCode）＃>
...
）
< / cfquery>
< / cfloop>
更新： p>
根据您的更新，您现有的代码非常接近我最初描述的。您只需要添加隐藏字段，并在每次添加行时更新它。
function addRow（）{
//删除额外的变量
var tbl = document.getElementById（'tblSample'）;
var row = tbl.insertRow（tbl.rows.length）;
//使用隐藏值计算总字段
var iteration = parseInt（document.getElementById（'totalFields'）。value）+ 1;
var cellLeft = row.insertCell（0）;
var textNode = document.createTextNode（iteration）;
... etcetera ...
//保存新的总计
document.getElementById（'totalFields'）。value = iteration;
}
...
< input type =hiddenid =totalFieldsname =totalFieldsvalue =0 />
UPDATE： / p>
其他一些注释
- 不需要
cfoutput。 - 处理用户提供的值以防止sql注入时，始终使用
cfqueryparam特别是当循环时。cfqueryparam使用绑定变量，可以在多次执行cfquery语句时增强性能 - 不要在查询中使用
DateFormat。它是为演示而设计的，并返回一个字符串。根据数据库设置，日期字符串可能会被误解。为了避免歧义，总是插入日期/时间对象，例如now（），而不是字符串。请参见 Matt的示例及其对now（）的使用。
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:
- FORM.GrandNum1, FORM.GrandNme1, FORM.sectCode1, FORM.txtComments1, ...
- FORM.GrandNum2, FORM.GrandNme2, FORM.sectCode2, FORM.txtComments2, ...
- FORM.GrandNum3, FORM.GrandNme3, FORM.sectCode3, FORM.txtComments3,...`
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
- There is no need for
cfoutputaround the query loop. The variables will be evaluated automatically - Always use
cfqueryparamwhen processing user supplied values to prevent sql injection. Especially when looping.cfqueryparamuses bind variables which can "enhance performance when executing a cfquery statement multiple times." - Do not use
DateFormatin 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 likenow()and not strings. See Matt's example and his usage ofnow().
这篇关于迭代通过动态创建的表和插入数据到SQL Server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
