ColdFusion& MSSQL：如何在一个提交中插入多个具有一个唯一ID的行 [英] ColdFusion & MSSQL : how to insert multiple rows with one unique id in one submission

问题描述

任何人都可以帮助我如何在一次提交中提交多个行？

此调查表格将显示从表格技能派生的一组技能。
老师将必须在复选框中检查学生的技能，然后单击提交。

一个学生可以有多个技能。
如果他有3个技能，那么发送到数据库的数据将在教师单击提交按钮后的3行。 （table studentskill）

 < cfoutput query =skill> 
< tr> 
< td>＃skill.skillname＃< / td> 
< td align =center>：< / td> 
< td>< input type =checkboxname =skillidvalue =skillidchecked =checked>< / td> 
< / tr> 
< / cfoutput> 
  

表技能

  + --------- + ------------ + 
 | skillid |技能名称
 
 + --------- + ------------ + 
 
 | 1 |绘制| 
 
 + --------- + ------------ + 
 
 | 2 |读| 
 
 + --------- + ------------ + 
 
 | 3 |舞蹈| 
 
 + --------- + ------------ + 
  

table studentskill

  + ----- ----- + ---------- | 
 
 | studentid | skillid | 
 
 + ---------- + ---------- + 
 | 001 | 1 | 
 | 001 | 2 | 
 | 002 | 1 | 
 | 002 | 2 | 
 | 002 | 3 | 
 + ---------- + ---------- + 
  

<插入多个记录的另一种简单方法是 INSERT .. SELECT 。 （它也提到在链接banyr发布）。因为技能ID存储在另一个表中，所以可以使用 IN 子句 SELECT 。然后将这些值直接插入到其他表 studenSkill 中，使用简单的查询，不循环。

  INSERT INTO studenSkill（studenId，skillId）
 SELECT< cfqueryparam value =＃form.studentId＃cfsqltype =cf_sql_integer> 
，skillId 
 FROM skill 
 WHERE skillId IN 
（
< cfqueryparam value =＃form.skillId＃cfsqltype =cf_sql_integerlist =true > 
）
  

 < input type =checkboxname =skillidvalue =skillidchecked =checked > 
   

BTW，万一输入错误， ＃符号查询列名称即skillid

 < input type =checkboxname =skillid =＃skillid＃checked =checked> 
  

can anyone help me on how to submit multiple rows in one submission?

this survey form will display a set of skill that derived from table skill. the teacher will have to check the students skill's in checkboxes and click submit.

a student can have more than one skill. if he has 3 skills, then the data that is sent into database will be in 3 rows once the teacher click the submit button. (table studentskill)

<cfoutput query="skill">
<tr>
    <td>#skill.skillname#</td>
    <td align="center">:</td>
    <td><input type="checkbox" name="skillid" value="skillid" checked="checked"></td>
</tr>
</cfoutput>

table skill

+---------+------------+
| skillid | skillname  |

+---------+------------+

| 1       | draw       |

+---------+------------+

| 2       | read       |

+---------+------------+

| 3       | dance      |

+---------+------------+

table studentskill

+----------+----------|

|studentid | skillid  |

+----------+----------+
| 001      | 1        |
| 001      | 2        |
| 002      | 1        |
| 002      | 2        |
| 002      | 3        |
+----------+----------+

解决方案

Another simple approach for inserting multiple records is an INSERT .. SELECT. (It is also mentioned in the link banyr posted). Because the skill id's are stored in another table, you can use an IN clause to SELECT them. Then insert those values directly into your other table studenSkill with a simple query, no looping.

INSERT INTO studenSkill ( studenId, skillId )
SELECT <cfqueryparam value="#form.studentId#" cfsqltype="cf_sql_integer">
       , skillId
FROM   skill
WHERE  skillId IN 
       (
       <cfqueryparam value="#form.skillId#" cfsqltype="cf_sql_integer" list="true">
       )

    <input type="checkbox" name="skillid" value="skillid" checked="checked">

BTW, in case that is not a typo, do not forget the # signs around the query column name ie "skillid"

  <input type="checkbox" name="skillid" value="#skillid#" checked="checked">

这篇关于ColdFusion&amp; MSSQL：如何在一个提交中插入多个具有一个唯一ID的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！