5 Replies Latest reply on Mar 16, 2015 7:59 AM by rp966435

    How can I escape commas inside listQualify function?



      am a newbie at coldfusion. I need some help. My database has records that are company names as such: abc, Inc. For now I am using this query:

      SELECT DISTINCT COMPANY FROM ComapanyTable WHERE (Company IN (#ListQualify(form.cCompanyList2, "'", ",")#))

      This problem with this is that it separates the abc, Inc.

      SELECT DISTINCT Company FROM CompanyTableWHERE (Company IN ('abc',' Inc.','xyz','Inc.'))

      I need to get the list as it is, i.e. as: 'abc, Inc.', 'xyz, Inc' So that I can later insert these values into a new table.

      Insert code:

      <cfquery name="insertPair" datasource="#DSN#" dbtype="ODBC"> INSERT INTO tblChildCompanyToParent (Parent_Account_ID, Child_Account_ID) SELECT DISTINCT <cfqueryparam value = "#form.pCompanyList#" CFSQLType = "CF_SQL_VARCHAR">, Company FROM CompanyTable WHERE Company IN (<cfqueryparam value="#ValueList(insertSelect.Company,';' )#" CFSQLType = "CF_SQL_VARCHAR" list="true" separator=";">) </cfquery>

      Select list code

      <select multiple name="cCompanyList2" id="cCompanyList2" class="selectCCompany" data-attribute="selCCompany" size="10"> <cfloop query="childCompanyList"> <option value="#childCompanyList.Child_Account_ID#">#childCompanyList.Company#</option> </cfloop> </select>

      Is there anyway to fix this.