delimited input cfloop

[Sin0cide]

I am trying to use a list of delimited values and put it in a database. The list is setup as follows:

siteId|SiteName|siteURL|galleryURL|descript|dateAdded|type;

siteId|SiteName|siteURL|galleryURL|descript|dateAdded|type;

siteId|SiteName|siteURL|galleryURL|descript|dateAdded|type;

As you can see the values are delimited by pipe and each one has a semi-colon on the end. I have comprised the follow code that doesn't seem to work. What is wrong? (Assuming its gets passed the form.txtBox1 from a seperate script.

<cfloop index="gallery" list="#form.txtBox1#" delimiters="|">

    <cfquery name="importList">

         INSERT INTO gallery (siteId,siteName,siteUrl,galleryUrl,descript,dateAdded,type)

         VALUES

                  ('#listgetAt('#gallery#',1, '|')#',

                   '#listgetAt('#gallery#',2, '|')#',

                   '#listgetAt('#gallery#',3, '|')#',

   '#listgetAt('#gallery#',4, '|')#',

   '#listgetAt('#gallery#',5, '|')#',

   '#listgetAt('#gallery#',6, '|')#',

   '#listgetAt('#gallery#',7)#'

                  )

   </cfquery>

</cfloop>

[-__cfSearching__- · Accepted solution]

cfloop delimiters="|"

At a glance the cfloop delimiter (ie row) should probably be ";"

'#listgetAt('#gallery#',7)#'

... and you are missing the '|' delimiter for the last item.

If there is a chance the elements might be empty, consider using arrays instead. Unlike most list functions, listToArray() in CF8+ is capable of preserving empty elements. Also, queries executed within a loop are prime candidates for cfqueryparam.

-Leigh

[Dan_Bracuk]

In addition to cfSearching's answer, you appear to be storing DateAdded as a string instead of a date.  Bad idea.

[Sin0cide]

That is correct. I was able to do the task and got the error about the date string. I will have to re-think the date field. Thanks the help guys.