Highlighted

Query

New Here ,
Jun 19, 2020

Copy link to clipboard

Copied

OK.  Why isn't this working?  I have ben out of CF since about 2008.

 

SELECT *
FROM PURCHASEORDERTABLE
WHERE shipdate IS '#dateformat(Now(), "MM/DD/YYYY")#' AND Company IS CP

 

Having an issue with the AND statement.

Views

72

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

Query

New Here ,
Jun 19, 2020

Copy link to clipboard

Copied

OK.  Why isn't this working?  I have ben out of CF since about 2008.

 

SELECT *
FROM PURCHASEORDERTABLE
WHERE shipdate IS '#dateformat(Now(), "MM/DD/YYYY")#' AND Company IS CP

 

Having an issue with the AND statement.

Views

73

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
Jun 19, 2020 0
Adobe Community Professional ,
Jun 20, 2020

Copy link to clipboard

Copied

"IS" is not a valid comparison operator in SQL for what you're trying to do. Try:

WHERE shipdate = '...' AND company = '...'

 

I'm also not sure what you're doing with the company comparison - you can't just refer to variables without any sort of identifier so that SQL knows it's a variable. Where does CP come from? Are you trying to do something in CF to compare them?

 

Dave Watts, Eidolon LLC

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...
Jun 20, 2020 0
New Here ,
Jun 21, 2020

Copy link to clipboard

Copied

company is a database field and CP is a company.  There are 4 in the same DB.  So I am trying to get shippingdates for the company CP.  I have also tried your suggestion before posting. doesn't work.

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...
Jun 21, 2020 0
New Here ,
Jun 21, 2020

Copy link to clipboard

Copied

[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'CP'.

The error occurred in C:/ColdFusion2018/cfusion/wwwroot/TCN/cpindex.cfm: line 9

7 : SELECT *
8 : FROM PURCHASEORDERTABLE
9 : WHERE enterdate = '#dateformat(Now(), "MM/DD/YYYY")#' AND Company = "CP"
10 : </cfquery>

 

Not sure why it is looking at CP as a column.  It does this with both " " and ' '

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...
Jun 21, 2020 0
Adobe Community Professional ,
Jun 21, 2020

Copy link to clipboard

Copied

First, I'd try to run your test queries in a database console instead of in CF. That'll help you separate CF errors from SQL errors. If you're using SQL Server, you can use SQL Server Management Studio. You won't be able to use any CFML expressions when you're doing this, of course, but it'll force you to separate those out and see where you're introducing the error.

 

Second, you could try isolating each part of your WHERE clause. So, you'd try something like:

 

SELECT * FROM PURCHASEORDERTABLE

WHERE Company = 'CP'

 

and see if that works. If it does, there's probably some problem with the first part of the WHERE clause. I don't see anything obviously wrong with it from here, but there's a lot I can't see.

 

Dave Watts, Eidolon LLC

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...
Jun 21, 2020 0
New Here ,
Jun 21, 2020

Copy link to clipboard

Copied

So that query gets me: (when using quotes)

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'CP'.

 

and when using '

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]The data types text and varchar are incompatible in the equal to operator.
 

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...
Jun 21, 2020 0
New Here ,
Jun 21, 2020

Copy link to clipboard

Copied

FIXED.  Colum type was text.  I had to change to varchar.  thank you for pointing me there.

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...
Jun 21, 2020 0