How to insert multiple list item as separate records

[Guest]

I have a form with a list field in which user can select multiple item <select multiple="multiple"><option value></option></select>. My problem is that I don't know how to do an insert sql operation that puts every selected item on the list field into a separate record in a database table. I think that first I should get it to know how many item was selected from the list, than write the insert into statement between loop tags which loops the operation until every item has inserted. I'm not sure about it and even if I'm right, I don't know how to code it. Could someone show me a sample code for such a "problem"?

[The_ScareCrow]

Let's assume the select list is named "mySelect"

Then

<cfloop index="listElement" list="#form.mySelect#">
<cfquery name="qry_insert" datasource="dsn">
Insert Into myTable(myColumn)
Values('#listElement#')
</cfquery>
</cfloop>

Note: This assume that the value being inserted is text.

Ken

[Newsgroup_User]

The ScareCrow wrote:
> Let's assume the select list is named "mySelect"
>
> Then
>
> <cfloop index="listElement" list="#form.mySelect#">
> <cfquery name="qry_insert" datasource="dsn">
> Insert Into myTable(myColumn)
> Values('#listElement#')
> </cfquery>
> </cfloop>
>
> Note: This assume that the value being inserted is text.

uh, depending on the db (say it's sql server) you might want to swap the loop &
cfquery to make that one big insert rather than a bunch of cfqueries.

[BJ_-_CF]

PaulH, would you put some code out on what you mean. If I want multiple DB records, how would I code it if I only have one big insert statement?

[Dan_Bracuk]

quote:

---

Originally posted by: Newsgroup User
The ScareCrow wrote:
uh, depending on the db (say it's sql server) you might want to swap the loop &
cfquery to make that one big insert rather than a bunch of cfqueries.

---

The following approach will work with just about any db and only uses once connection

<cfquery>
insert into sometable
(field1, field2, etc)
<cfloop list = "#somelist#" item = "thisitem">
select distinct #thisitem#, 'something else', etc
from some_small_table
<cfif listfind(somelist, thisitem) lt listlen(somelist)>
union
</cfif>
</cfloop>
</cfquery>

[drforbin1970]

This should give you one, large, multiple insert query.

<cfquery name="qry_insert" datasource="dsn">
<cfloop index="listElement" list="#form.mySelect#">
Insert Into myTable(myColumn)
Values('#listElement#')

</cfloop>
</cfquery>