Highlighted

sql/access erro question

Contributor ,
Aug 20, 2014

Copy link to clipboard

Copied

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.

error.png

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.

Views

158

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

sql/access erro question

Contributor ,
Aug 20, 2014

Copy link to clipboard

Copied

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.

error.png

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.

Views

159

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Aug 20, 2014 0
Community Beginner ,
Aug 20, 2014

Copy link to clipboard

Copied

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,

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 20, 2014 0
Contributor ,
Aug 21, 2014

Copy link to clipboard

Copied

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

error2.png

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 21, 2014 0
Community Beginner ,
Aug 21, 2014

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 21, 2014 1