Single quotes problem!

[micjohnson]

From my CF query i am getting all distincted SSNs. From these SSN list i need to get all names from another table.

Get SSN:
-------

<cfquery name="getSSN" datasource="dsn">
SELECT distinct ssn
FROM tblSSN
WHERE originator='Wrker1'
</cfquery>


<cfset AllSSN = ValueList(getSSN.ssn, "," )>

<cfquery name="getClient" datasource="#request.session.ds#">
SELECT Name
FROM tblName
WHERE SSN IN (#PreserveSingleQuotes(AllNames)#)
</cfquery>

But i am getting an error with the query as

SELECT Name
FROM tblClient WHERE SSN IN (111111111,222222222,333333333,444444444)

My problem is i dont get single quotes around each SSN.

How to i put single quotes around each SSN.
like this

SELECT Name
FROM tblClient WHERE SSN IN ('111111111','222222222','333333333','444444444')

Appreciate your help.

micJ

 

[FALCONSEYE]

SELECT Name
FROM tblClient WHERE SSN IN (<cfqueryparam list="yes" value="#ValueList(getSSN.ssn)#">)

http://www.jetansolutions.com

 

[micjohnson]

Thanks FALCONSEYE! I used an alternate way and it works.

<cfset AllNames = "'"& ValueList(getSSN.ssn, "','" ) & "'">

micJ

 

[imstillatwork]

There really is no alternative to cfqueryparam. You simply should use it EVERY time you use a CFML variable in a query

So is SSN field a varchar? if you are stripping out the dashes or spaces, and always store only integers, then you should use a integer field type, ie, proper type for the data.