separting list items

Report · Mar 11, 2009

I'm trying to insert values from a list into a database (Access or SQL 2005).

<cfloop list="#form.txtEntry#" index="i">

<CFQUERY NAME="insRecords" DATASOURCE="MyDB">
insert into tblColors(txtEntry, txtName)
values('#i#', '#form.txtName#')
</CFQUERY>

Some of the values in the list have commas. Here are three list items, for example:
red, white and blue
orange
purple

I want the value 'red, white and blue' to insert as one record, but my query is actually inserting it as two records (presumably because of the comma). It's inserting 'red' as one record and 'white and blue' as another. Could anyone recommend a way for this to work the way I want? Thanks so much...

Report · Mar 11, 2009

that is expected bahaviour. where is your list coming from? you could
either specify a different list items delimiter (there is an optional
argument in almost all cf list functions to specify a list delimiter) or
change the list delimiter after your list is compiled using
listchangedelims() function...
set your delimiter to | (pipe) for example...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Report · Mar 11, 2009

> I want the value 'red, white and blue' to insert as one record

Why? That type of de-normalized design will only increase the complexity of your queries and decrease performance and accuracy.