WHERE VALUENAME IN (#FORMVALUE#)

[gadjodilo77]

Hello,

I have a question.Imagine I have the following FORMvalue or Formvariable: BER-06-3005,BER-06-3008,BER-06-3008
What I want to do is to make a query using this formvalue. Each BER-06-xxxx number is a namenumber in an database table. What I want to do is make a Query and select the data for each BER-06-xxxx value (BER-06-xxxx separated by a comma in the formvalue/formvariable). I tried several things but I don't know how to do it.

I tried it like this:

<cfquery name="name" datasource="source">
SELECT * FROM dev WHERE dev_volgnr IN (#FORMvalue#)
</cfquery>

This gives the following error however:
Error Executing Database Query.
Column not found: Unknown column 'BER' in 'where clause'

So because of the error I tried it by putting ' at the front and the end of the FORMvalue variable, like this:

<cfquery name="name" datasource="source">
SELECT * FROM dev WHERE dev_volgnr IN ('#FORMvalue#')
</cfquery>

 

[philhege]

I'm not quite sure what the datatype "namenumber" is, but for character types you have to enclose each individual value in single quotes in the IN() function:

IN('BER-06-3005','BER-06-3008','BER-06-3008')

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[imstillatwork]

use <cfqueryparam cfsqltype="varchar" value="#FORMvalue#" list="yes">

instead of

#FORMvalue#

Kevin

http://www.lot-o-nothin.com/cfml/

 

[jalpino]

Or you can use the listQualify() function, like so:

 SELECT *
 FROM dev
 WHERE dev_volgnr IN (#listQualify(FORMvalue)#)

Hope this helps,

JAlpino

 

[imstillatwork]

technically, cfqueryparam should be used, this is in a query.

any other function will not create the query the same way EI, you get no data binding for more efficient, faster, safer queries.

http://mkruger.cfwebtools.com/index.cfm?mode=entry&entry=F1B29E12-9424-1650-BA023EEB6224B18C

http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/functb17.htm

Kevin

http://www.lot-o-nothin.com/cfml/

 

[gadjodilo77]

Hi,

Thank you all! I used the following now:

<cfif IsDefined ("FORM.value") AND #FORM.value# NEQ "">
<cfset lijstlengte= #ListLen(FORM.FORM.value)#>
<cfif IsDefined("lijstlengte") AND #lijstlengte# NEQ 0>
<cfset nieuwkopvar = ListQualify(FORM.value,"'",",","ALL")>
<cfquery name="" datasource="">
SELECT * FROM devices WHERE dev_volgnr IN (#preserveSingleQuotes(nieuwkopvar)#)
</cfquery>

But I'm curious if this will work also:

<cfqueryparam cfsqltype="varchar" value="#FORM.value#" list="yes">

Grt, Kabbi

 

[imstillatwork]

yeah, you should use cfqueryparam. I provided some links, there are many more too.

Kevin

http://www.lot-o-nothin.com/cfml/