Skip to main content
A
Anonymous
December 14, 2011
Question

cfspreadsheet in a query

  • December 14, 2011
  • 2 replies
  • 1595 views

Hi,

Maybe I am tired but I can't figure this out.  I am using cfspreadsheet to read data from a spreadsheet into a query.  That works great and I can dump out the data.  The problem is that I need to be able to use those results to compare to data on a database table.  What I need to do is do a query of queries to pull data based on a list of strings I get from the database.  The problem is that no matter what I do, the string from the database never gets formatted correctly.  This is the code I am working with:

  

          <cfspreadsheet 

    action="read"

          headerrow="true"

    src = "#thisDirectory#newdatafile.xls"    

    query = "newcodes"

          excludeHeaderRow = "true"> 

 

          <cfquery name="getcurrentcodes" datasource="#Request.maindatasource#">

                    SELECT code FROM currenttableindatabase

          </cfquery>

                    

          <cfset oldcodelist = ValueList(getcurrentcodes.code)>

 

          <cfset trimmedoldcodes = Replace(oldcodelist," ","","all")>

 

          <cfquery name="GetNewCodes" dbtype="query">

                    SELECT newcode FROM newcodes

                    WHERE newcode NOT IN <cfqueryparam value="#trimmedoldcodes#">

          </cfquery>

It seems like such a simple thing but I either get double quotes around every item or no quotes.  I have also tried PreserveSingleQuotes.  Does anybody have any ideas.

Red

    This topic has been closed for replies.

    2 replies

    Sean Coyne
    Sean Coyne
    Participating Frequently
    December 14, 2011

    In addition to cfSearching's response, I would also point out QuotedValueList.  Instead of using ValueLIst then trying to add the quotes yourself, try QuotedValueList and see if that does the trick for you.  You can then use that value in the cfqueryparam list.

    http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6d16.html

    EDIT: forgot link to docs Message was edited by: Sean Coyne

    -
    -__cfSearching__-
    Inspiring
    December 14, 2011

    Yep. Though the nice thing about cfqueryparam is you do not have to worry about quoting at all. Just pass in the raw values (no quotes) and you are good to go.

    A
    Anonymous
    December 15, 2011

    Thank you for the info!  That fixed the problem.  I sometimes forget to look for the simple solution.  I put them in a valuelist and pushed them into the query like -==cfSearching==- said. 

    -
    -__cfSearching__-
    Inspiring
    December 14, 2011
    WHERE newcode NOT IN <cfqueryparam value="#trimmedoldcodes#">

    First, the IN clause is malformed. It is missing the parenthesis around the cfqueryparam.  Second, you need to add the "list" attribute to tell CF you are passing in multiple values, rather than a single value.  Something like

    WHERE newcode NOT IN (

          <cfqueryparam value="#trimmedoldcodes#" list="true" cfsqltype="cf_sql_varchar">

    )

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices