遍历动态创建的表并将数据插入 SQL Server 表 [英] Iterate through a dynamically created table and Insert data into SQL Server table

问题描述

我有一个动态创建的 .html 表格表格，我想在 .cfm 表格中插入.我需要遍历动态创建的表的行，并在 SQL Server 中的表中执行 INSERT.此外，该表是用 JavaScript 创建的.谢谢.

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>

这是我创建表格的 html 代码:

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.

要创建唯一的字段名称，请让您的 javascript 将计数器编号附加到每个 set 字段.所以字段将被命名:

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

然后将总数存储在隐藏字段中.在您的操作页面上，执行一个简单的 from/to 循环.如果需要，使用 cfparam 为任何可能不存在的字段设置默认值，例如复选框或单选按钮(文本字段 always 存在).然后你可以使用 cfqueryparam 的 null 属性，它比 isDefined 或 structKeyExists IMO 更简洁.

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>

更新:

根据您的更新，您现有的代码与我上面最初描述的非常接近.您只需要添加隐藏字段，并在每次添加行时更新它.那么上面的cfloop代码应该可以正常工作了.

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" />

<小时>

更新:

其他一些评论

1. 查询循环周围不需要 cfoutput.变量将被自动评估
2. 在处理用户提供的值时始终使用 cfqueryparam 以防止 sql 注入.尤其是循环的时候.cfqueryparam 使用可以 " 的绑定变量在多次执行 cfquery 语句时提高性能."
3. 不要在查询中使用 DateFormat.它是为演示而设计的，并返回一个字符串.根据您的数据库设置，日期字符串可能会被误解.为避免歧义，请始终插入日期/时间对象，例如now()，而不是字符串.请参阅 Matt 的示例 和他对 now() 的用法.

1. There is no need for cfoutput around the query loop. The variables will be evaluated automatically
2. Always use cfqueryparam 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."
3. Do not use 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().

这篇关于遍历动态创建的表并将数据插入 SQL Server 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！