Skip to main content
August 21, 2014
Question

sql/access erro question

  • August 21, 2014
  • 1 reply
  • 275 views

Been a few years since I worked with tag based cf.

Was trying to create a search form to search db records that matched the user inputs. I basically copied old tutorials code from cfwack 9 and the OWS examples files. These files use a derby database and of course work fine.

I copied the search,cfm and results.cfm templates and just replaced all the values with my database vals. Im using an access database and i get an error when I run the query.

As you can see, it says Im missing an operator in the WHERE clause. Again, this same code seemed to work fine in the ows tutorial db. Im wondering if its because its an access db and the operators may be different?

Here is my query:

<cfquery name="plm" datasource="dawnFoods">

SELECT ProductDataSpec.ID, ProductDataSpec.projectName. ProductDataSpec.desc, ProductDataSpec.skuDetails

FROM ProductDataSpec

WHERE 0=0

<cfif FORM.projectName IS NOT "">

AND projectName = #FORM.projectName#

</cfif>

ORDER BY ID

</cfquery>

Not sure why the hmtl insert is adding a double 00 in the where clause, its not in my source code.

Any help would be greatly appreciated.

    This topic has been closed for replies.

    1 reply

    IndraPrastha
    Participant
    August 21, 2014

    Seems you're just missing the single quote around FORM.projectName :

    AND projectName = '#FORM.projectName#'

    I would recommend you to use <cfqueryparam> instead of directly outputting the #FORM.projectName#

    this is to prevent someone injecting malicious SQL to your query.

    Read it here : Adobe ColdFusion 9 * Enhancing security with cfqueryparam

    Cheers,

    August 21, 2014

    Yes, thats what I thought too. About using the single quotes. But when I do I get this error.

    IndraPrastha
    Participant
    August 21, 2014

    You should really read the error there, it is very easy to spot the mistake, just by reading the error and it also points out where the error is.

    SELECT ... ProductDataSpec.projectName "DOT" ProductDataSpec.desc

    It should be a "comma" , not a "DOT"

    Cheers