Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.
My CFQuery doesn't work, could you please help with the Where statement.
I need your help.
Could you please view attached images and help build this CFQuery.
<CFQUERY NAME="DisplayUsertool" datasource="aies" username="#application.use
SELECT CMTEE_PREF
FROM cvitool
WHERE
<!--- (I want id_number ino aies.users = to id into dbo.cvitool - could you please advice on the colde or this query below) -->
</CFQUERY>
Could you please view attached images and help build this CFQuery.
<CFQUERY NAME="DisplayUsertool" datasource="aies" username="#application.use
SELECT CMTEE_PREF
FROM cvitool
WHERE
<!--- (I want id_number ino aies.users = to id into dbo.cvitool - could you please advice on the colde or this query below) -->
</CFQUERY>
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
I want id_number ino aies.users = to id into dbo.cvitool
Are those the right columns? "id_number" is an integer and cvitool.id is a varchar ..
What's the relationship between aies.users and dbo.cvitool ?
Are those the right columns? "id_number" is an integer and cvitool.id is a varchar ..
What's the relationship between aies.users and dbo.cvitool ?
The ID_number into aies.users is always a six number ID.913057
The ID indo dbo.cvitool is the same a number where i'm having a nightly procedure copying all information from my IMIS to a SQL Database. so is always a six number ID:
so that ID 913057always show into that field ID into dbo.cvitool.
FYI, Right now i'm able to retreive the first name and the last name and the ID_number from DisplayUser from USERS tables.
<CFQUERY NAME="DisplayUser" datasource="Actuaries" username="#application.use
SELECT *
FROM users
WHERE users.user_id = #client.user_id#
</CFQUERY>
So i've added a cfquerry to my code to retreive/show additional data into dbo.cvitool as CMTEE_PREF where ID Field into dbo.cvitool is the same as the id_number field into aies.users.
did u get it. Please see tables images.
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT *
FROM cvitool
WHERE
<!--- My where didn't work users.user_id = #client.user_id# --->
</CFQUERY>
do you want me to copy all the code for you?
The ID indo dbo.cvitool is the same a number where i'm having a nightly procedure copying all information from my IMIS to a SQL Database. so is always a six number ID:
so that ID 913057always show into that field ID into dbo.cvitool.
FYI, Right now i'm able to retreive the first name and the last name and the ID_number from DisplayUser from USERS tables.
<CFQUERY NAME="DisplayUser" datasource="Actuaries" username="#application.use
SELECT *
FROM users
WHERE users.user_id = #client.user_id#
</CFQUERY>
So i've added a cfquerry to my code to retreive/show additional data into dbo.cvitool as CMTEE_PREF where ID Field into dbo.cvitool is the same as the id_number field into aies.users.
did u get it. Please see tables images.
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT *
FROM cvitool
WHERE
<!--- My where didn't work users.user_id = #client.user_id# --->
</CFQUERY>
do you want me to copy all the code for you?
_agx_ is right, the columns on both tables have different data_types, you're going to have data_type mismatches across your application.
A word of advice, when you're coding foreign IDs between tables, give them the same name on both tables and the same datatype(length). It makes your life (and your fellow devs) a lot easier.
If I'm reading your question correctly you seem to be asking how to do a join query between to tables?
That's easy,
Select A.ID, A.name, B.report <!--- and the other columns you want to grab --->
FROM TableA as A
,TableB as B
WHERE A.ID=B.ID
If you're trying to get all columns on TableA and any matching columns on TableB, you have to do an OUTER join
Select <!--- Columns here --->
FROM TableA as A
LEFT OUTER JOIN TableB as B
ON A.ID=B.ID
You can do multiple OUTER table joins like so:
SELECT <!--- columns here --->
FROM TableA as A
LEFT OUTER JOIN TableB as B ON A.ID=B.ID
LEFT OUTER JOIN TableC as C ON B.bID=C.bID
The words LEFT and RIGHT tell SQL which side of the query you want to contain the other.
in the example above, you will get ALL records from table A, with all matching records from table B, and all matches from tableB will show a match from tableC and if there's no matching record then the column value for that record will have empty strings.
This is what's called a one-to-many table join. (I think)
EDIT: the first type of join, is called an INNER join, but SQL implicitly makes any joins inner if no other type is specified.
Think about 2 circles intersecting each other. The area where both circles intersect is the INNER join query results, when you do an OUTER join, you get the set of records in the intersect area and also all the other records in the side you specified. (LEFT or RIGHT)
A word of advice, when you're coding foreign IDs between tables, give them the same name on both tables and the same datatype(length). It makes your life (and your fellow devs) a lot easier.
If I'm reading your question correctly you seem to be asking how to do a join query between to tables?
That's easy,
Select A.ID, A.name, B.report <!--- and the other columns you want to grab --->
FROM TableA as A
,TableB as B
WHERE A.ID=B.ID
If you're trying to get all columns on TableA and any matching columns on TableB, you have to do an OUTER join
Select <!--- Columns here --->
FROM TableA as A
LEFT OUTER JOIN TableB as B
ON A.ID=B.ID
You can do multiple OUTER table joins like so:
SELECT <!--- columns here --->
FROM TableA as A
LEFT OUTER JOIN TableB as B ON A.ID=B.ID
LEFT OUTER JOIN TableC as C ON B.bID=C.bID
The words LEFT and RIGHT tell SQL which side of the query you want to contain the other.
in the example above, you will get ALL records from table A, with all matching records from table B, and all matches from tableB will show a match from tableC and if there's no matching record then the column value for that record will have empty strings.
This is what's called a one-to-many table join. (I think)
EDIT: the first type of join, is called an INNER join, but SQL implicitly makes any joins inner if no other type is specified.
Think about 2 circles intersecting each other. The area where both circles intersect is the INNER join query results, when you do an OUTER join, you get the set of records in the intersect area and also all the other records in the side you specified. (LEFT or RIGHT)
ok sound goods. here my cfquery i would like to show the data in the field columns
So for your information my output is currenlty
Name (from users) it's working fine
Last (from users) it's working fine
ID_number (from user) it's displaying fine.
CMTEE_FREE (from cvitool) doesn't work for me, it's not displaying. I want to build a query for this one to display.
How I can fix that cfquery.
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT *
FROM cvitool
WHERE users.user_id = #client.user_id#
</CFQUERY>
<CFOUTPUT>
<td ALIGN="LEFT" width="50%"><font size="3"><input type="text" name="CMTEE_PREF" value="#DisplayCVITOOL.CMT
</CFOUTPUT>
thank you for your help.
So for your information my output is currenlty
Name (from users) it's working fine
Last (from users) it's working fine
ID_number (from user) it's displaying fine.
CMTEE_FREE (from cvitool) doesn't work for me, it's not displaying. I want to build a query for this one to display.
How I can fix that cfquery.
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT *
FROM cvitool
WHERE users.user_id = #client.user_id#
</CFQUERY>
<CFOUTPUT>
<td ALIGN="LEFT" width="50%"><font size="3"><input type="text" name="CMTEE_PREF" value="#DisplayCVITOOL.CMT
</CFOUTPUT>
thank you for your help.
When you say "the code didn't work" do you mean, you're getting the wrong set of data that you were expecting? or are you actually getting a cold fusion / database error when you run the page?
It would help if you posted a screen capture of the cfdump of the data you are getting, or a capture screen of the cfcatch of the error you're getting
e.g.
<cftry>
<!--- Your code here --->
<cfquery name="myQuery">
</cfquery>
<cfdump var="#myQuery#">
<cfcatch type="any">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>
It would help if you posted a screen capture of the cfdump of the data you are getting, or a capture screen of the cfcatch of the error you're getting
e.g.
<cftry>
<!--- Your code here --->
<cfquery name="myQuery">
</cfquery>
<cfdump var="#myQuery#">
<cfcatch type="any">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>
<!--- My where didn't work users.user_id = #client.user_id# --->
Assuming you meant the "id" column, you also have to put the value in single quotes because the column type is varchar.
WHERE id = '#client.user_id#'
Even better, use cfqueryparam
WHERE id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_varchar"
If the db's are on the same server you could also combine the 2 queries into one via a JOIN
Assuming you meant the "id" column, you also have to put the value in single quotes because the column type is varchar.
WHERE id = '#client.user_id#'
Even better, use cfqueryparam
WHERE id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_varchar"
If the db's are on the same server you could also combine the 2 queries into one via a JOIN
okay i will try to use this one.
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT *
FROM cvitool
WHERE id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_varchar"
</CFQUERY>
<CFOUTPUT>
<td ALIGN="LEFT" width="50%"><font size="3"><input type="text" name="CMTEE_PREF" value="#DisplayCVITOOL.CMT
</CFOUTPUT>
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT *
FROM cvitool
WHERE id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_varchar"
</CFQUERY>
<CFOUTPUT>
<td ALIGN="LEFT" width="50%"><font size="3"><input type="text" name="CMTEE_PREF" value="#DisplayCVITOOL.CMT
</CFOUTPUT>
AH! as I suspected you're not joining the tables.
Your SQL Should be:
SELECT users.Name
,users.last
,users.user_id
,cvitool.CMTEE_PREF
FROM users,
cvitool
WHERE users.user_id=cvitool.user
AND users.user_id = #client.user_id#
PS, I suggest never putting an open ## in your SQL, it makes your application to SQL injection which is very bad. I would suggest changing the last line with the following:
AND users.user_id = <cfqueryparam cfsqltype="CF_SQL_INT" value="#client.user_id#">
Your SQL Should be:
SELECT users.Name
,users.last
,users.user_id
,cvitool.CMTEE_PREF
FROM users,
cvitool
WHERE users.user_id=cvitool.user
AND users.user_id = #client.user_id#
PS, I suggest never putting an open ## in your SQL, it makes your application to SQL injection which is very bad. I would suggest changing the last line with the following:
AND users.user_id = <cfqueryparam cfsqltype="CF_SQL_INT" value="#client.user_id#">
Yeah if the 2 db's are on the same server you can use a join like I suggested above. (Though the correct type is "CF_SQL_INTEGER" not "CF_SQL_INT")
1) I'd recommend using the newer ansi JOIN syntax, which will give you support for OUTER joins too.
2) You probably should CAST one of the columns since they're not the same type. Either
FROM users INNER JOIN cvitool ON cast(users.user_id as varchar) = cvitool.id
WHERE users.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer">
... or
FROM users INNER JOIN cvitool ON users.user_id = cast(cvitool.id as INT)
WHERE users.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer">
3) The fact that the columns are 2 different types is going to make the query a little slower. Because the db has to perform conversions every time. If possible, modify the second table to make the column an INT instead of a varchar
1) I'd recommend using the newer ansi JOIN syntax, which will give you support for OUTER joins too.
2) You probably should CAST one of the columns since they're not the same type. Either
FROM users INNER JOIN cvitool ON cast(users.user_id as varchar) = cvitool.id
WHERE users.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer">
... or
FROM users INNER JOIN cvitool ON users.user_id = cast(cvitool.id as INT)
WHERE users.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer">
3) The fact that the columns are 2 different types is going to make the query a little slower. Because the db has to perform conversions every time. If possible, modify the second table to make the column an INT instead of a varchar
Ah! thx _agx_ I wasn't sure about the cfsqltype, Yeah, I think they are, LelloLello has another thread with the whole text of the code and from there it seems like he's using the same datasource for both cfqueries.
Didn't know about the newer ANSI join syntax, do you know of a good resource where I can read more about it?
Didn't know about the newer ANSI join syntax, do you know of a good resource where I can read more about it?
So my code was working before the first name and last name was displaying fine.... So i've just add that CFQUERY and this CFOUTPUT to display additional data from cvitool and it gave me an error. So I've attached the code for your review.
Here is my cfquerry added to my code.
--------------------------
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.CMTEE_PREF
FROM users,
cvitool
WHERE users.user_id=cvitool.user
AND users.user_id = <cfqueryparam cfsqltype="CF_SQL_INT" value="#client.user_id#">
</CFQUERY>
--------------------------
and here is my cfoutput added to my code
--------------------------
<tr>
<td align="right" bgcolor="78A8D2"><font color="white" size="2"><font face="arial" size="2"><b>CMTEE_PREF: </b></font></td>
<CFOUTPUT>
<td ALIGN="LEFT"><font size="3"><input type="text" name="CMTEE_PREF" value="#Trim(DisplayCVIToo
</CFOUTPUT>
</tr>
--------------------------
Here is the error message:
--------------------------
CFSQLTYPE<P> 'CF_SQL_INT' is not a valid CFSQLTYPE setting for CFQUERYPARAM. When specified, the CFSQLTYPE attribute must be set to one of the following: CF_SQL_BIGINT CF_SQL_BINARY CF_SQL_BIT CF_SQL_CHAR CF_SQL_CLOB CF_SQL_DATE CF_SQL_DECIMAL CF_SQL_DOUBLE CF_SQL_FLOAT CF_SQL_IDSTAMP CF_SQL_INTEGER CF_SQL_LONGVARBINARY CF_SQL_LONGVARCHAR CF_SQL_MONEY CF_SQL_MONEY4 CF_SQL_NUMERIC CF_SQL_REAL CF_SQL_REFCURSOR CF_SQL_SMALLINT CF_SQL_TIME CF_SQL_TIMESTAMP CF_SQL_TINYINT CF_SQL_VARBINARY CF_SQL_VARCHAR <p>The error occurred while processing an element with a general identifier of (CFQUERYPARAM), occupying document position (23:29) to (23:90) in the template file D:\USERS\ACTCA\HTML\MEMBER
Please advice. thank you.
update-volunteer-info.txt
Here is my cfquerry added to my code.
--------------------------
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.CMTEE_PREF
FROM users,
cvitool
WHERE users.user_id=cvitool.user
AND users.user_id = <cfqueryparam cfsqltype="CF_SQL_INT" value="#client.user_id#">
</CFQUERY>
--------------------------
and here is my cfoutput added to my code
--------------------------
<tr>
<td align="right" bgcolor="78A8D2"><font color="white" size="2"><font face="arial" size="2"><b>CMTEE_PREF: </b></font></td>
<CFOUTPUT>
<td ALIGN="LEFT"><font size="3"><input type="text" name="CMTEE_PREF" value="#Trim(DisplayCVIToo
</CFOUTPUT>
</tr>
--------------------------
Here is the error message:
--------------------------
CFSQLTYPE<P> 'CF_SQL_INT' is not a valid CFSQLTYPE setting for CFQUERYPARAM. When specified, the CFSQLTYPE attribute must be set to one of the following: CF_SQL_BIGINT CF_SQL_BINARY CF_SQL_BIT CF_SQL_CHAR CF_SQL_CLOB CF_SQL_DATE CF_SQL_DECIMAL CF_SQL_DOUBLE CF_SQL_FLOAT CF_SQL_IDSTAMP CF_SQL_INTEGER CF_SQL_LONGVARBINARY CF_SQL_LONGVARCHAR CF_SQL_MONEY CF_SQL_MONEY4 CF_SQL_NUMERIC CF_SQL_REAL CF_SQL_REFCURSOR CF_SQL_SMALLINT CF_SQL_TIME CF_SQL_TIMESTAMP CF_SQL_TINYINT CF_SQL_VARBINARY CF_SQL_VARCHAR <p>The error occurred while processing an element with a general identifier of (CFQUERYPARAM), occupying document position (23:29) to (23:90) in the template file D:\USERS\ACTCA\HTML\MEMBER
Please advice. thank you.
update-volunteer-info.txt
FYI - Please note that i don't have a user_id into cvitool. I have only a cvitool.ID which is equal to users.id_number.
WHERE users.user_id=cvitool.user
WHERE users.user_id=cvitool.user
Please see the tables.jpg images. it's very easy but i'm not sure where is the trick :( thank you for your help !!!
i have change the cfquerry to this one. What do you think... and it didn't work.
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.CMTEE_PREF
FROM users,
cvitool
WHERE users.id_number=cvitool.id
AND users.user_id = <cfqueryparam cfsqltype="CF_SQL_INT" value="#client.user_id#">
</CFQUERY>
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.CMTEE_PREF
FROM users,
cvitool
WHERE users.id_number=cvitool.id
AND users.user_id = <cfqueryparam cfsqltype="CF_SQL_INT" value="#client.user_id#">
</CFQUERY>
My apologies,
_agx_ pointed out my value for cfsql was wrong:
Here's the correct code:
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.CMTEE_PREF
FROM users,
cvitool
WHERE users.user_id=cvitool.id
AND users.user_id = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
</CFQUERY>
_agx_ pointed out my value for cfsql was wrong:
Here's the correct code:
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.CMTEE_PREF
FROM users,
cvitool
WHERE users.user_id=cvitool.id
AND users.user_id = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
</CFQUERY>
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
EDIT: Oops... the posts keep overlapping
> <cfqueryparam cfsqltype="CF_SQL_INT" value="#client.user_id#">
See the code in my last response. It needs to be "CF_SQL_INTEGER" . You may also need to CAST one of the columns. But try fixing the sql type first.
ie
FROM users INNER JOIN cvitool ON users.user_id = cast(cvitool.id as INT)
WHERE users.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer"
> <cfqueryparam cfsqltype="CF_SQL_INT" value="#client.user_id#">
See the code in my last response. It needs to be "CF_SQL_INTEGER" . You may also need to CAST one of the columns. But try fixing the sql type first.
ie
FROM users INNER JOIN cvitool ON users.user_id = cast(cvitool.id as INT)
WHERE users.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer"
_agx_ is right, you're probably going to get type mismatch errors if you don't use CAST() like he suggested in his comment from 8:30
Didn't know about the newer ANSI join syntax, do you know of a good resource where I can read more about it?
Well I shouldn't say "new", just newer ;-) IIRC it's part of the ANSI-92 specs. Though I didn't kick the old syntax habit till a few years ago. I don't know of any resources off hand. I mostly googled and read about it in the MS SQL BOL.
Well I shouldn't say "new", just newer ;-) IIRC it's part of the ANSI-92 specs. Though I didn't kick the old syntax habit till a few years ago. I don't know of any resources off hand. I mostly googled and read about it in the MS SQL BOL.
Here is my current code now. There is no error with this code below but NO DATE into the CFOUTPUT are displaying. Empty fields... but there is data.
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.ID
,cvitool.AF_YR
,cvitool.CR_YR
,cvitool.Profile_updated
,cvitool.CMTEE_PREF
,cvitool.ISSUES
,cvitool.PRACTICE_AREA_SPE
,cvitool.YEARS_EXP
,cvitool.PRACTICE_AREA_PAS
,cvitool.OTHER_ACT_VOL
,cvitool.OTHER_NONACT_VOL
,cvitool.SPEC_INT
,cvitool.SPEC_SKILLS
FROM users,
cvitool
WHERE users.user_id=cvitool.id
AND users.user_id = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
</CFQUERY>
As per you request, i've changed my cfquery to
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.ID
,cvitool.AF_YR
,cvitool.CR_YR
,cvitool.Profile_updated
,cvitool.CMTEE_PREF
,cvitool.ISSUES
,cvitool.PRACTICE_AREA_SPE
,cvitool.YEARS_EXP
,cvitool.PRACTICE_AREA_PAS
,cvitool.OTHER_ACT_VOL
,cvitool.OTHER_NONACT_VOL
,cvitool.SPEC_INT
,cvitool.SPEC_SKILLS
FROM users,
cvitool
FROM users INNER JOIN cvitool ON users.user_id = cast(cvitool.id as INT)
WHERE users.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer"
</CFQUERY>
--------------------------
and I got that the following error:
--------------------------
ODBC Error Code = 37000 (Syntax error or access violation)<P><P> [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'FROM'.<P><P>SQL = "SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.ID
,cvitool.AF_YR
,cvitool.CR_YR
,cvitool.Profile_updated
,cvitool.CMTEE_PREF
,cvitool.ISSUES
,cvitool.PRACTICE_AREA_SPE
,cvitool.YEARS_EXP
,cvitool.PRACTICE_AREA_PAS
,cvitool.OTHER_ACT_VOL
,cvitool.OTHER_NONACT_VOL
,cvitool.SPEC_INT
,cvitool.SPEC_SKILLS
FROM users,
cvitool
FROM users INNER JOIN cvitool ON users.user_id = cast(cvitool.id as INT)
WHERE users.user_id = ?"<P>
Query Parameter Value(s) - <P>Parameter #1 = 10777
<P>Data Source = "ACTUARIES"<P><p>The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (15:2) to (15:123) in the template file D:\USERS\ACTCA\HTML\MEMBER
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.ID
,cvitool.AF_YR
,cvitool.CR_YR
,cvitool.Profile_updated
,cvitool.CMTEE_PREF
,cvitool.ISSUES
,cvitool.PRACTICE_AREA_SPE
,cvitool.YEARS_EXP
,cvitool.PRACTICE_AREA_PAS
,cvitool.OTHER_ACT_VOL
,cvitool.OTHER_NONACT_VOL
,cvitool.SPEC_INT
,cvitool.SPEC_SKILLS
FROM users,
cvitool
WHERE users.user_id=cvitool.id
AND users.user_id = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
</CFQUERY>
As per you request, i've changed my cfquery to
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.ID
,cvitool.AF_YR
,cvitool.CR_YR
,cvitool.Profile_updated
,cvitool.CMTEE_PREF
,cvitool.ISSUES
,cvitool.PRACTICE_AREA_SPE
,cvitool.YEARS_EXP
,cvitool.PRACTICE_AREA_PAS
,cvitool.OTHER_ACT_VOL
,cvitool.OTHER_NONACT_VOL
,cvitool.SPEC_INT
,cvitool.SPEC_SKILLS
FROM users,
cvitool
FROM users INNER JOIN cvitool ON users.user_id = cast(cvitool.id as INT)
WHERE users.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer"
</CFQUERY>
--------------------------
and I got that the following error:
--------------------------
ODBC Error Code = 37000 (Syntax error or access violation)<P><P> [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'FROM'.<P><P>SQL = "SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.ID
,cvitool.AF_YR
,cvitool.CR_YR
,cvitool.Profile_updated
,cvitool.CMTEE_PREF
,cvitool.ISSUES
,cvitool.PRACTICE_AREA_SPE
,cvitool.YEARS_EXP
,cvitool.PRACTICE_AREA_PAS
,cvitool.OTHER_ACT_VOL
,cvitool.OTHER_NONACT_VOL
,cvitool.SPEC_INT
,cvitool.SPEC_SKILLS
FROM users,
cvitool
FROM users INNER JOIN cvitool ON users.user_id = cast(cvitool.id as INT)
WHERE users.user_id = ?"<P>
Query Parameter Value(s) - <P>Parameter #1 = 10777
<P>Data Source = "ACTUARIES"<P><p>The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (15:2) to (15:123) in the template file D:\USERS\ACTCA\HTML\MEMBER
Ops, thank you. Okay there is no compilations errors. But my data doesn't show into my fields.
Please see image. FYI, for id 885519 into dbo.cvitool there is data but it didn't display on the form. why ?
Only data into users where id_number 885519 was displayed.
NoData.jpg
Please see image. FYI, for id 885519 into dbo.cvitool there is data but it didn't display on the form. why ?
Only data into users where id_number 885519 was displayed.
NoData.jpg
i found out the error. FROM users INNER JOIN cvitool ON users.id_number = cast(cvitool.id as INT)
so this is the correct cfquery. Thank you for your help.
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.ID
,cvitool.AF_YR
,cvitool.CR_YR
,cvitool.Profile_updated
,cvitool.CMTEE_PREF
,cvitool.ISSUES
,cvitool.PRACTICE_AREA_SPE
,cvitool.YEARS_EXP
,cvitool.PRACTICE_AREA_PAS
,cvitool.OTHER_ACT_VOL
,cvitool.OTHER_NONACT_VOL
,cvitool.SPEC_INT
,cvitool.SPEC_SKILLS
FROM users INNER JOIN cvitool ON users.id_number = cast(cvitool.id as INT)
WHERE users.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer"
</CFQUERY>
<!--- FROM users INNER JOIN cvitool ON users.user_id = cast(cvitool.id as INT)
WHERE users.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer"
---->
so this is the correct cfquerry. Could you please stay tune, I have another request with this code that i'm trying to do.
so this is the correct cfquery. Thank you for your help.
<CFQUERY NAME="DisplayCVITool" datasource="Actuaries" username="#application.use
SELECT users.firstName
,users.lastname
,users.id_number
,cvitool.ID
,cvitool.AF_YR
,cvitool.CR_YR
,cvitool.Profile_updated
,cvitool.CMTEE_PREF
,cvitool.ISSUES
,cvitool.PRACTICE_AREA_SPE
,cvitool.YEARS_EXP
,cvitool.PRACTICE_AREA_PAS
,cvitool.OTHER_ACT_VOL
,cvitool.OTHER_NONACT_VOL
,cvitool.SPEC_INT
,cvitool.SPEC_SKILLS
FROM users INNER JOIN cvitool ON users.id_number = cast(cvitool.id as INT)
WHERE users.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer"
</CFQUERY>
<!--- FROM users INNER JOIN cvitool ON users.user_id = cast(cvitool.id as INT)
WHERE users.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer"
---->
so this is the correct cfquerry. Could you please stay tune, I have another request with this code that i'm trying to do.
could u please advice on my others questions http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_27634162.html
Could you please advise on my quesitons:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_27692516.html
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_27692516.html
could you please advise on my questions
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_27693824.html#a37903063
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_27693824.html#a37903063
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2013 Part … 146 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Word 2016 Part … 134 lessons
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
tables.jpg