Join Query works in access but not via ColdFusion

[albatross07]

I have a query I built with the query builder in Access and through access it
works fine.
here is the query:
SELECT
appUsers.AppId,
LAWSON_SHC_EMPLOYEE_V.FIRST_NAME,
LAWSON_SHC_EMPLOYEE_V.EMPLOYEE,
LAWSON_SHC_EMPLOYEE_V.LAST_NAME
FROM
appUsers
INNER JOIN
LAWSON_SHC_EMPLOYEE_V
ON appUsers.EmpId = LAWSON_SHC_EMPLOYEE_V.EMPLOYEE
WHERE (((appUsers.AppId)=111) AND ((LAWSON_SHC_EMPLOYEE_V.EMPLOYEE)=18796)
AND ((LAWSON_SHC_EMPLOYEE_V.LAST_NAME)="SMITH"));

again this works fine in access so then I make slight modifications to imbed
it into my ColdFusion template

SELECT
LAWSON_SHC_EMPLOYEE_V.FIRST_NAME,
LAWSON_SHC_EMPLOYEE_V.EMPLOYEE,
LAWSON_SHC_EMPLOYEE_V.LAST_NAME,
appUsers.AppId
FROM
appUsers
INNER JOIN
LAWSON_SHC_EMPLOYEE_V
ON
appUsers.EmpId = LAWSON_SHC_EMPLOYEE_V.EMPLOYEE
WHERE
LAWSON_SHC_EMPLOYEE_V.EMPLOYEE = <cfqueryparam value="#form.empID#"
cfsqltype="cf_sql_integer"> AND
LAWSON_SHC_EMPLOYEE_V.LAST_NAME= <cfqueryparam value="#ucase(form.Lname)#"
cfsqltype="cf_sql_char"> AND
appUsers.AppId = <cfqueryparam value="111" cfsqltype="cf_sql_integer">

This one doesnt work. before I added the CfqueryParams I got a 2 few
parameters expected 2. After adding the CfqueryParams the errors stopped but I
get an empty recordset when I should get 1 row back. To make matters even more
interesting when I change the INNER JOIN to a LEFT JOIN it seems to work. What
bothers me the most is that I cannot explain why it will work as left join but
not inner, and my boos wont le tthe app go live untill I can.

any insight will be appreciated.

Thanks Ron

[javabuzz]

albatross07 wrote:

> I have a query I built with the query builder in Access and through

access it

> works fine.
> here is the query:
> SELECT
> appUsers.AppId,
> LAWSON_SHC_EMPLOYEE_V.FIRST_NAME,
> LAWSON_SHC_EMPLOYEE_V.EMPLOYEE,
> LAWSON_SHC_EMPLOYEE_V.LAST_NAME
> FROM
> appUsers
> INNER JOIN
> LAWSON_SHC_EMPLOYEE_V
> ON appUsers.EmpId = LAWSON_SHC_EMPLOYEE_V.EMPLOYEE
> WHERE (((appUsers.AppId)=111) AND

((LAWSON_SHC_EMPLOYEE_V.EMPLOYEE)=18796)

> AND ((LAWSON_SHC_EMPLOYEE_V.LAST_NAME)="SMITH"));
>
> again this works fine in access so then I make slight modifications

to imbed

> it into my ColdFusion template
>
> SELECT
> LAWSON_SHC_EMPLOYEE_V.FIRST_NAME,
> LAWSON_SHC_EMPLOYEE_V.EMPLOYEE,
> LAWSON_SHC_EMPLOYEE_V.LAST_NAME,
> appUsers.AppId
> FROM
> appUsers
> INNER JOIN
> LAWSON_SHC_EMPLOYEE_V
> ON
> appUsers.EmpId = LAWSON_SHC_EMPLOYEE_V.EMPLOYEE
> WHERE
> LAWSON_SHC_EMPLOYEE_V.EMPLOYEE = <cfqueryparam value="#form.empID#"

> cfsqltype="cf_sql_integer"> AND
> LAWSON_SHC_EMPLOYEE_V.LAST_NAME= <cfqueryparam

value="#ucase(form.Lname)#"

> cfsqltype="cf_sql_char"> AND
> appUsers.AppId = <cfqueryparam value="111"

cfsqltype="cf_sql_integer">

>
> This one doesnt work. before I added the CfqueryParams I got a 2

few

> parameters expected 2. After adding the CfqueryParams the errors

stopped but I

> get an empty recordset when I should get 1 row back. To make matters

even more

> interesting when I change the INNER JOIN to a LEFT JOIN it seems to

work. What

> bothers me the most is that I cannot explain why it will work as left

join but

> not inner, and my boos wont le tthe app go live untill I can.
>
> any insight will be appreciated.
>
> Thanks Ron

Just a thought - When passing text in a SQL statement to an ODBC or
JDBC driver, you must use single quotation marks. If you use double
quotation marks, the parser treats the first double quotation mark as a
statement terminator, ignoring all the text after it.

[vkunirs]

Hi

you are saying the same query is working fine in the ms-access, then for
testing just execute the same query from the cfquery with the same values.
then check out is there any problem, if it works fine then replaces the values
with cf varaibles.

or else
just display the query before executing it in cf. copy that query executed
it in the ms-access thensee how many records it is returning.
:cool;