help with a valuelist

[proxim00]

Hi,

I posted yesterday asking a question about deleting some stuff from my DB and somone suggested I use a value list, so basicaly Im getting all the stock number and putting them in a value list, then running a delete statement like this

where NOT IN('#Valuelist(query)#')

Now my problem is it goes through inserts all my records then when I goto do the deletion it just deletes everything in the DB ? what could cause this? I have tried it both way with and without the single quotes but its the same results. The stock number look like this P434-133 so I don't know if thats messing it up or not as well. Anyhow I would really appreciate some help

Thanks

[Newsgroup_User]

> where NOT IN('#Valuelist(query)#')

Take a look at the SQL this generates, and then consider the SQL it SHOULD
be generating.

Hints:
1) SQL "IN clause" lists are not quite the same as CF lists.
2) http://livedocs.macromedia.com/coldfusion/7/htmldocs/00000602.htm

--
Adam

[insuractive]

proxim00,
Now that I'm looking at the code again, the problem could be the single quotes you've surrounded your value list with. You want the SQL to read:
WHERE myField NOT IN ('item1','item2','item3')

and it looks like the example you give above would read:
WHERE myField NOT IN ('item1,item2,item3')

If you need to surround each item in the list with single quotes, I would recommend the ListQualify() function.

Again, follow Adam's advice before you run your SQL and output the query SQL to the screen to make sure CF is building the query you really want.

[proxim00]

Hi everyone, well I started to look into it more and I ended up useing quotedvaluelist and it seemed to do the trick :)

Thanks for all your help it put me on the right track