natG
asked on
Just a syntax question: building a query statement based on user's selections
Hello all,
I am trying to dynamically build an SQL query based on what my user selects on a web form. I'm using the following approach:
<CFSET strSQL = "Select attribute_a, attribute_b, from table_a where">
<CFIF user wants student_id info>
<CFSET strSQL = strSQL & "student_id = '#student_ID#'">
</CFIF>
<CFIF user wants something else -- (here's where my problem is)>
<CFSET strSQL = strSQL & "student_major = '" & #user's_selection_from_for m# & "'">
I've seen this done plenty of times in CF and ASP, but for some reason I'm getting an SQL syntax error where my #user's_selection_from_for m# is referenced. The CF error page that is returned displays the output of my query string as sent to SQL (MS-SQL 2000, btw) with the student major correctly displayed, but it appears to be inside two sets of single quotes. I know SQL wants those items to be enclosed in single quotes, so as you can see I entered them and closed the string before referencing the form variable, but it's not working. Am I missing a required trick for escaping the quotes?
Thank you...
I am trying to dynamically build an SQL query based on what my user selects on a web form. I'm using the following approach:
<CFSET strSQL = "Select attribute_a, attribute_b, from table_a where">
<CFIF user wants student_id info>
<CFSET strSQL = strSQL & "student_id = '#student_ID#'">
</CFIF>
<CFIF user wants something else -- (here's where my problem is)>
<CFSET strSQL = strSQL & "student_major = '" & #user's_selection_from_for
I've seen this done plenty of times in CF and ASP, but for some reason I'm getting an SQL syntax error where my #user's_selection_from_for
Thank you...
just make it single quotes, don't worry about escaping and all that. if there are possibly going to be single quotes in the form variable, then use #preservesinglequotes(form .variable) #
Beyond all that, creating a SQL string is rather awkward. You can do this instead:
<cfquery>
SELECT attribute_a, attribute_b
FROM table_a
WHERE
<CFIF user wants student_id info>student_id = '#student_ID#'</CFIF>
<CFIF user wants something else -- (here's where my problem is)>
student_major = '#preservesinglequotes(for m.variable )#'
</cfquery>
<cfquery>
SELECT attribute_a, attribute_b
FROM table_a
WHERE
<CFIF user wants student_id info>student_id = '#student_ID#'</CFIF>
<CFIF user wants something else -- (here's where my problem is)>
student_major = '#preservesinglequotes(for
</cfquery>
Tallermike is correct, but just a suggestion.
say if the user selects more than one options then you will have to form your query more apropriately.
that is say user wants both student_id info student_major then you will have to combine cfif's
<CFSET EXTRACONDITION = "">
<CFSET S_SQL== "">
<CFIF user wants student_id info>
<CFSET S_SQL = "student_id = '" & #student_ID# & "'">
<CFSET EXTRACONDITION = "AND">
</CFIF>
<CFIF user wants something else -- (here's where my problem is)>
<CFSET S_SQL = #S_SQL# & #EXTRACONDITION# & "student_major = '" & #PRESERVESINGLEQUOTES(form .variable) # & "'">
</CFIF>
<cfquery>
SELECT attribute_a, attribute_b
FROM table_a
WHERE #PRESERVESINGLEQUOTES(S_SQ L)#
</cfquery>
Regards
Hart(Harish)
say if the user selects more than one options then you will have to form your query more apropriately.
that is say user wants both student_id info student_major then you will have to combine cfif's
<CFSET EXTRACONDITION = "">
<CFSET S_SQL== "">
<CFIF user wants student_id info>
<CFSET S_SQL = "student_id = '" & #student_ID# & "'">
<CFSET EXTRACONDITION = "AND">
</CFIF>
<CFIF user wants something else -- (here's where my problem is)>
<CFSET S_SQL = #S_SQL# & #EXTRACONDITION# & "student_major = '" & #PRESERVESINGLEQUOTES(form
</CFIF>
<cfquery>
SELECT attribute_a, attribute_b
FROM table_a
WHERE #PRESERVESINGLEQUOTES(S_SQ
</cfquery>
Regards
Hart(Harish)
What if the user doesn't select anything? It should be like this:
<cfquery>
SELECT attribute_a, attribute_b
FROM table_a
WHERE 0=0
<CFIF user wants student_id info>AND student_id = '#student_ID#'</CFIF>
<CFIF user wants something else -- (here's where my problem is)>AND student_major = '#preservesinglequotes(for m.variable )#'
</cfquery>
Why use single quotes in the student_major form field at all? If it's not a multiple select, there's really no reason to do this. So if that's the case, get rid of the preservesinglequotes above.
<cfquery>
SELECT attribute_a, attribute_b
FROM table_a
WHERE 0=0
<CFIF user wants student_id info>AND student_id = '#student_ID#'</CFIF>
<CFIF user wants something else -- (here's where my problem is)>AND student_major = '#preservesinglequotes(for
</cfquery>
Why use single quotes in the student_major form field at all? If it's not a multiple select, there's really no reason to do this. So if that's the case, get rid of the preservesinglequotes above.
Forgot something... Isn't student_id an integer field? If not, it should be and the single quotes should be removed. Also, is there any form validation that prevents the user from NOT selecting anything? If so, the query I submitted above will work but wouldn't be necessary.
Simple :
1/ the way you build your query is OK. (don't forget a space at the end of each concatenation, to avoir sticking words together)
2/ to send the query, just do :
<cfquery name="..." datasource="...">
#PreserveSingleQuotes(strS QL)#
</cfquery>
This is due to the fact that CF will double the ' when expressing a variable :
IF variable "a" contains "it's cool"
THEN "#a#" will produce "it''s cool" (only inside the cfquery tag, of course).
This is very usefull : this is why you don't have to worry about double-quoting usualy...
E.
1/ the way you build your query is OK. (don't forget a space at the end of each concatenation, to avoir sticking words together)
2/ to send the query, just do :
<cfquery name="..." datasource="...">
#PreserveSingleQuotes(strS
</cfquery>
This is due to the fact that CF will double the ' when expressing a variable :
IF variable "a" contains "it's cool"
THEN "#a#" will produce "it''s cool" (only inside the cfquery tag, of course).
This is very usefull : this is why you don't have to worry about double-quoting usualy...
E.
Any luck on this question natG?
ASKER
TallerMike et al,
This question has become an issue again. I was doing it by embedding CFIFs inside my query tag like you suggested on 3/11, and it was working fine. However, now I realize I need to save this query string as text in a db field so that users can choose default querys (filtering options on a list of students) for their individual needs. And when they sort the list by clicking on the column heads the page reloads, so having the query string as a var that I can pass around seems like the way to go.
Anyway, I am still having some syntax problems-- There is a CFLOOP insided the select statement that dynamically adds columns to the query, and the output(column names) has the index of the loop appended to them to give them unique names. So, I have coldfusion tags and variables inside the string that shouldn't be evaluated until the query is actually run; I think I need to use the DE() and / or evaluate() functions, but I'm still getting various errors.
I don't have the code with me at the moment. I could post it tonight. In the meantime, if anyone has any examples they could post I'd appreciate it.
This question has become an issue again. I was doing it by embedding CFIFs inside my query tag like you suggested on 3/11, and it was working fine. However, now I realize I need to save this query string as text in a db field so that users can choose default querys (filtering options on a list of students) for their individual needs. And when they sort the list by clicking on the column heads the page reloads, so having the query string as a var that I can pass around seems like the way to go.
Anyway, I am still having some syntax problems-- There is a CFLOOP insided the select statement that dynamically adds columns to the query, and the output(column names) has the index of the loop appended to them to give them unique names. So, I have coldfusion tags and variables inside the string that shouldn't be evaluated until the query is actually run; I think I need to use the DE() and / or evaluate() functions, but I'm still getting various errors.
I don't have the code with me at the moment. I could post it tonight. In the meantime, if anyone has any examples they could post I'd appreciate it.
ASKER
OK, here is the latest version of the code. I've tried multiple variations and I get a different error every time. I have part of it commented out just to try to tackle one problem at a time. The commented-out part, inside the cfloop, was throwing a different error- CF was trying to evaluate the i at the time I set the var, and not later. DE() did not seem to work, so I commented it out and found myself with another problem. Here it is:
<cfset sql = DE('Select Student.Student_ID,
<cfloop index = i from = 1 to = #getprereqs.RecordCount#>
(Select Prereq_Status from PetitionPrereqState
where PetitionPrereqState.Studen t_ID = Student.Student_ID
and PetitionPrereqState.Prereq _ID = 1) as Prereq_Status1,
<!--- and PetitionPrereqState.Prereq _ID = '&chr(35)&'i'&chr(35)&') as Prereq_Status'&#chr(35)#&' i'&#chr(35 )#&', --->
<!--- and PetitionPrereqState.Prereq _ID = '&chr(35)&'i'&chr(35)&') as Prereq_Status'&#chr(35)#&' i'&#chr(35 )#&', --->
</cfloop>
Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
Student_Major, Student_GPA, Pref_Class_Section,
(Select TOP 1 Status_name from StatusHistory
where Petition.Petition_ID = StatusHistory.Petition_ID
order by Status_date desc) as Pet_Status
from Student, Petition
where Student.Student_ID = Petition.Student_ID
and Petition.Course_ID = #SESSION.DEFAULTCOURSE#')>
<!---
<cfif state is "2">
<cfif #form.firstchoice# is "1">
and DispCode = '#form.secondChoice#' change next line to else if!
<cfif #form.firstchoice# is "2">
<cfset sql = sql & ' and Student.Student_GPA = '#form.secondChoice#''>
<cfelseif #form.firstchoice# is "3">
<cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#'">
</cfif>
</cfif> --->
<cfset sql = sql & ' order by #sort# #sortorder#'>
<cfquery name="getstudents"
DATASOURCE="#ODBC_SOURCE#"
username="#ODBC_USERID#"
password= "#ODBC_PASSWD#">
#PreserveSingleQuotes(sql) #
</CFQUERY>
The error (this time) is:
"[Macromedia][SQLServer JDBC Driver][SQLServer]The identifier that starts with 'Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where' is too long. Maximum length is 128."
Anyone who could throw me a bone here would be much appreciated.
Thanks in advance.
NatG
Thanks in advance.
<cfset sql = DE('Select Student.Student_ID,
<cfloop index = i from = 1 to = #getprereqs.RecordCount#>
(Select Prereq_Status from PetitionPrereqState
where PetitionPrereqState.Studen
and PetitionPrereqState.Prereq
<!--- and PetitionPrereqState.Prereq
<!--- and PetitionPrereqState.Prereq
</cfloop>
Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
Student_Major, Student_GPA, Pref_Class_Section,
(Select TOP 1 Status_name from StatusHistory
where Petition.Petition_ID = StatusHistory.Petition_ID
order by Status_date desc) as Pet_Status
from Student, Petition
where Student.Student_ID = Petition.Student_ID
and Petition.Course_ID = #SESSION.DEFAULTCOURSE#')>
<!---
<cfif state is "2">
<cfif #form.firstchoice# is "1">
and DispCode = '#form.secondChoice#' change next line to else if!
<cfif #form.firstchoice# is "2">
<cfset sql = sql & ' and Student.Student_GPA = '#form.secondChoice#''>
<cfelseif #form.firstchoice# is "3">
<cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#'">
</cfif>
</cfif> --->
<cfset sql = sql & ' order by #sort# #sortorder#'>
<cfquery name="getstudents"
DATASOURCE="#ODBC_SOURCE#"
username="#ODBC_USERID#"
password= "#ODBC_PASSWD#">
#PreserveSingleQuotes(sql)
</CFQUERY>
The error (this time) is:
"[Macromedia][SQLServer JDBC Driver][SQLServer]The identifier that starts with 'Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where' is too long. Maximum length is 128."
Anyone who could throw me a bone here would be much appreciated.
Thanks in advance.
NatG
Thanks in advance.
you need to increase the size of your column in your db structure, or change the datatype to something bigger.
ASKER
I don't understand-- the size of what column? This is a query, I am not adding or updating to the db. Can you elaborate?
ASKER
I don't understand-- the size of what column? This is a query, I am not adding or updating to the db. Can you elaborate?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, the code has evolved a little as I am trying different things. Here it is as it looks now:
<cfset sql ='Select Student.Student_ID,
#chr(60)#cfloop index = i from = 1 to = #getprereqs.RecordCount##c hr(62)#
(Select Prereq_Status from PetitionPrereqState
where PetitionPrereqState.Studen t_ID = Student.Student_ID
and PetitionPrereqState.Prereq _ID = 1) as Prereq_Status1,
#chr(60)##chr(47)#cfloop#c hr(62)#
Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
Student_Major, Student_GPA, Pref_Class_Section,
(Select TOP 1 Status_name from StatusHistory
where Petition.Petition_ID = StatusHistory.Petition_ID
order by Status_date desc) as Pet_Status
from Student, Petition
where Student.Student_ID = Petition.Student_ID
and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>
<!---
<cfif state is "2">
<cfif #form.firstchoice# is "1">
and DispCode = '#form.secondChoice#' change next line to else if!
<cfif #form.firstchoice# is "2">
<cfset sql = sql & ' and Student.Student_GPA = '#form.secondChoice#''>
<cfelseif #form.firstchoice# is "3">
<cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#'">
</cfif>
</cfif> --->
<cfset sql = sql & ' order by #sort# #sortorder#'>
<CFOUTPUT> #PreserveSingleQuotes(sql) # </cfoutput>
<cfquery name="getstudents"
DATASOURCE="#ODBC_SOURCE#"
username="#ODBC_USERID#"
password= "#ODBC_PASSWD#">
#sql#
</CFQUERY>
Here is the cfoutput result:
Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Studen t_ID = Student.Student_ID and PetitionPrereqState.Prereq _ID = 1) as Prereq_Status1, Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID, Student_Major, Student_GPA, Pref_Class_Section, (Select TOP 1 Status_name from StatusHistory where Petition.Petition_ID = StatusHistory.Petition_ID order by Status_date desc) as Pet_Status from Student, Petition where Student.Student_ID = Petition.Student_ID and Petition.Course_ID = 1 order by Student_Lname
And, on the same page right below the output is the error that says:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '<'.
Then it shows the SQL string as it was sent to the DB, which reads:
Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Studen t_ID = Student.Student_ID and PetitionPrereqState.Prereq _ID = 1) as Prereq_Status1, Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID, Student_Major, Student_GPA, Pref_Class_Section, (Select TOP 1 Status_name from StatusHistory where Petition.Petition_ID = StatusHistory.Petition_ID order by Status_date desc) as Pet_Status from Student, Petition where Student.Student_ID = Petition.Student_ID and Petition.Course_ID = 1 order by Student_Lname
Now, how weird is it that there is no open anglebracket (<) anywhere in the output of the SQL (indeed I escaped them for the cfloop) but the error refers to one....
<cfset sql ='Select Student.Student_ID,
#chr(60)#cfloop index = i from = 1 to = #getprereqs.RecordCount##c
(Select Prereq_Status from PetitionPrereqState
where PetitionPrereqState.Studen
and PetitionPrereqState.Prereq
#chr(60)##chr(47)#cfloop#c
Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
Student_Major, Student_GPA, Pref_Class_Section,
(Select TOP 1 Status_name from StatusHistory
where Petition.Petition_ID = StatusHistory.Petition_ID
order by Status_date desc) as Pet_Status
from Student, Petition
where Student.Student_ID = Petition.Student_ID
and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>
<!---
<cfif state is "2">
<cfif #form.firstchoice# is "1">
and DispCode = '#form.secondChoice#' change next line to else if!
<cfif #form.firstchoice# is "2">
<cfset sql = sql & ' and Student.Student_GPA = '#form.secondChoice#''>
<cfelseif #form.firstchoice# is "3">
<cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#'">
</cfif>
</cfif> --->
<cfset sql = sql & ' order by #sort# #sortorder#'>
<CFOUTPUT> #PreserveSingleQuotes(sql)
<cfquery name="getstudents"
DATASOURCE="#ODBC_SOURCE#"
username="#ODBC_USERID#"
password= "#ODBC_PASSWD#">
#sql#
</CFQUERY>
Here is the cfoutput result:
Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Studen
And, on the same page right below the output is the error that says:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '<'.
Then it shows the SQL string as it was sent to the DB, which reads:
Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Studen
Now, how weird is it that there is no open anglebracket (<) anywhere in the output of the SQL (indeed I escaped them for the cfloop) but the error refers to one....
ASKER
Here's another bit of info: In the code I pasted above, I replaced the index i in the cfloop with just a 1. If I actually include the i like this:
<cfset sql ='Select Student.Student_ID,
#chr(60)#cfloop index = i from = 1 to = #getprereqs.RecordCount##c hr(62)#
(Select Prereq_Status from PetitionPrereqState
where PetitionPrereqState.Studen t_ID = Student.Student_ID
and PetitionPrereqState.Prereq _ID = #chr(35)#i#chr(35)#) as Prereq_Status#chr(35)#i#ch r(35)#,
#chr(60)##chr(47)#cfloop#c hr(62)#
Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
Student_Major, Student_GPA, Pref_Class_Section,
(Select TOP 1 Status_name from StatusHistory
where Petition.Petition_ID = StatusHistory.Petition_ID
order by Status_date desc) as Pet_Status
from Student, Petition
where Student.Student_ID = Petition.Student_ID
and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>
...then the output and error message on the screen includes the pound signs, even though the <cfloop> is NOT included (nor are the <'s).
....
<cfset sql ='Select Student.Student_ID,
#chr(60)#cfloop index = i from = 1 to = #getprereqs.RecordCount##c
(Select Prereq_Status from PetitionPrereqState
where PetitionPrereqState.Studen
and PetitionPrereqState.Prereq
#chr(60)##chr(47)#cfloop#c
Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
Student_Major, Student_GPA, Pref_Class_Section,
(Select TOP 1 Status_name from StatusHistory
where Petition.Petition_ID = StatusHistory.Petition_ID
order by Status_date desc) as Pet_Status
from Student, Petition
where Student.Student_ID = Petition.Student_ID
and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>
...then the output and error message on the screen includes the pound signs, even though the <cfloop> is NOT included (nor are the <'s).
....
ASKER
... and here is the output:
Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Studen t_ID = Student.Student_ID and PetitionPrereqState.Prereq _ID = #i#) as Prereq_Status#i#, Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID, Student_Major, Student_GPA, Pref_Class_Section, (Select TOP 1 Status_name from StatusHistory where Petition.Petition_ID = StatusHistory.Petition_ID order by Status_date desc) as Pet_Status from Student, Petition where Student.Student_ID = Petition.Student_ID and Petition.Course_ID = 1 order by Student_Lname
Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Studen
I don't think you can set a variable with a string that has line breaks in it. I don't think the following is valid:
<cfset thisVariable = "This is the first line.
And this is the second">
You'll either need to do this:
<cfscript>
thisVariable = "This is the first line.";
thisVariable = thisVariable & "And this is the second";
</cfscript>
Or you'll need to create a custom tag to do something like this:
<cf_SuperVariable variableName="thisVariable ">
<cfoutput>
This is the first line.
And this is the second
</cfoutput>
</cf_SuperVariable>
Where cf_SuperVariable is going to take thiTag.generatedContent and put it into the variableName attribute which you can then access later like so:
<cfoutput>#thisVariable#</ cfoutput>
************************** ********** ********** ********** ********** ********** ********** ********** ********** ********** ****
************************** ********** ********** ********** ********** ********** ********** ********** ********** ********** ****
The reason you're getting errors about a '>' is that you're missing a single quote somewhere, so it is interpreting some of your code as a string. You'll need to find this extra single quote.
<cfset thisVariable = "This is the first line.
And this is the second">
You'll either need to do this:
<cfscript>
thisVariable = "This is the first line.";
thisVariable = thisVariable & "And this is the second";
</cfscript>
Or you'll need to create a custom tag to do something like this:
<cf_SuperVariable variableName="thisVariable
<cfoutput>
This is the first line.
And this is the second
</cfoutput>
</cf_SuperVariable>
Where cf_SuperVariable is going to take thiTag.generatedContent and put it into the variableName attribute which you can then access later like so:
<cfoutput>#thisVariable#</
**************************
**************************
The reason you're getting errors about a '>' is that you're missing a single quote somewhere, so it is interpreting some of your code as a string. You'll need to find this extra single quote.
ASKER
OK, thanks-- I'll look for it and get back to you....
ASKER
... and here is the output:
Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Studen t_ID = Student.Student_ID and PetitionPrereqState.Prereq _ID = #i#) as Prereq_Status#i#, Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID, Student_Major, Student_GPA, Pref_Class_Section, (Select TOP 1 Status_name from StatusHistory where Petition.Petition_ID = StatusHistory.Petition_ID order by Status_date desc) as Pet_Status from Student, Petition where Student.Student_ID = Petition.Student_ID and Petition.Course_ID = 1 order by Student_Lname
Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Studen
ASKER
I think I have solved it. The problem was in the cfloop being inside a string and then evaluated along with the variables inside it. I changed the code to loop OUTSIDE the cfset, and put the cfset inside the loop, and it works like a charm:
<!--- Begin building Query string --->
<cfset sql ='Select Student.Student_ID, '>
<cfloop index = i from = 1 to = #getprereqs.RecordCount#>
<cfset sql = sql &
'(Select Prereq_Status from PetitionPrereqState
where PetitionPrereqState.Studen t_ID = Student.Student_ID
and PetitionPrereqState.Prereq _ID = #i#) as Prereq_Status#i#, '>
</cfloop>
<cfset sql = sql & ' Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
Student_Major, Student_GPA, Pref_Class_Section,
(Select TOP 1 Status_name from StatusHistory
where Petition.Petition_ID = StatusHistory.Petition_ID
order by Status_date desc) as Pet_Status
from Student, Petition
where Student.Student_ID = Petition.Student_ID
and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>
<cfif state is "2">
<cfif #form.firstchoice# is "1">
<cfset sql = sql & " and DispCode = '#form.secondChoice#' ">
<cfelseif #form.firstchoice# is "2">
<cfset sql = sql & " and Student.Student_GPA = '#form.secondChoice#' ">
<cfelseif #form.firstchoice# is "3">
<cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#' ">
</cfif>
</cfif>
<cfset sql = sql & ' order by #sort# #sortorder#'>
<!--- end building query string--->
<cfquery name="getstudents"
DATASOURCE="#ODBC_SOURCE#"
username="#ODBC_USERID#"
password= "#ODBC_PASSWD#">
#PreserveSingleQuotes(sql) #
</CFQUERY>
I hope somebody else comes along and benefits from my hours of frustration, because this seems like a common challenge. I imagine I will run into this kind of thing often.
Mike, thanks for your help and quick responses.
NatG
<!--- Begin building Query string --->
<cfset sql ='Select Student.Student_ID, '>
<cfloop index = i from = 1 to = #getprereqs.RecordCount#>
<cfset sql = sql &
'(Select Prereq_Status from PetitionPrereqState
where PetitionPrereqState.Studen
and PetitionPrereqState.Prereq
</cfloop>
<cfset sql = sql & ' Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
Student_Major, Student_GPA, Pref_Class_Section,
(Select TOP 1 Status_name from StatusHistory
where Petition.Petition_ID = StatusHistory.Petition_ID
order by Status_date desc) as Pet_Status
from Student, Petition
where Student.Student_ID = Petition.Student_ID
and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>
<cfif state is "2">
<cfif #form.firstchoice# is "1">
<cfset sql = sql & " and DispCode = '#form.secondChoice#' ">
<cfelseif #form.firstchoice# is "2">
<cfset sql = sql & " and Student.Student_GPA = '#form.secondChoice#' ">
<cfelseif #form.firstchoice# is "3">
<cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#' ">
</cfif>
</cfif>
<cfset sql = sql & ' order by #sort# #sortorder#'>
<!--- end building query string--->
<cfquery name="getstudents"
DATASOURCE="#ODBC_SOURCE#"
username="#ODBC_USERID#"
password= "#ODBC_PASSWD#">
#PreserveSingleQuotes(sql)
</CFQUERY>
I hope somebody else comes along and benefits from my hours of frustration, because this seems like a common challenge. I imagine I will run into this kind of thing often.
Mike, thanks for your help and quick responses.
NatG
ASKER
SOLUTION:
The problem was in the cfloop being inside a cfset command, and then evaluated along with the variables inside it. I changed the code to loop OUTSIDE the cfset, and put the cfset inside the loop, and it works like a charm:
<!--- Begin building Query string --->
<cfset sql ='Select Student.Student_ID, '>
<cfloop index = i from = 1 to = #getprereqs.RecordCount#>
<cfset sql = sql &
'(Select Prereq_Status from PetitionPrereqState
where PetitionPrereqState.Studen t_ID = Student.Student_ID
and PetitionPrereqState.Prereq _ID = #i#) as Prereq_Status#i#, '>
</cfloop>
<cfset sql = sql & ' Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
Student_Major, Student_GPA, Pref_Class_Section,
(Select TOP 1 Status_name from StatusHistory
where Petition.Petition_ID = StatusHistory.Petition_ID
order by Status_date desc) as Pet_Status
from Student, Petition
where Student.Student_ID = Petition.Student_ID
and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>
<cfif state is "2">
<cfif #form.firstchoice# is "1">
<cfset sql = sql & " and DispCode = '#form.secondChoice#' ">
<cfelseif #form.firstchoice# is "2">
<cfset sql = sql & " and Student.Student_GPA = '#form.secondChoice#' ">
<cfelseif #form.firstchoice# is "3">
<cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#' ">
</cfif>
</cfif>
<cfset sql = sql & ' order by #sort# #sortorder#'>
<!--- end building query string--->
<cfquery name="getstudents"
DATASOURCE="#ODBC_SOURCE#"
username="#ODBC_USERID#"
password= "#ODBC_PASSWD#">
#PreserveSingleQuotes(sql) #
</CFQUERY>
The problem was in the cfloop being inside a cfset command, and then evaluated along with the variables inside it. I changed the code to loop OUTSIDE the cfset, and put the cfset inside the loop, and it works like a charm:
<!--- Begin building Query string --->
<cfset sql ='Select Student.Student_ID, '>
<cfloop index = i from = 1 to = #getprereqs.RecordCount#>
<cfset sql = sql &
'(Select Prereq_Status from PetitionPrereqState
where PetitionPrereqState.Studen
and PetitionPrereqState.Prereq
</cfloop>
<cfset sql = sql & ' Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
Student_Major, Student_GPA, Pref_Class_Section,
(Select TOP 1 Status_name from StatusHistory
where Petition.Petition_ID = StatusHistory.Petition_ID
order by Status_date desc) as Pet_Status
from Student, Petition
where Student.Student_ID = Petition.Student_ID
and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>
<cfif state is "2">
<cfif #form.firstchoice# is "1">
<cfset sql = sql & " and DispCode = '#form.secondChoice#' ">
<cfelseif #form.firstchoice# is "2">
<cfset sql = sql & " and Student.Student_GPA = '#form.secondChoice#' ">
<cfelseif #form.firstchoice# is "3">
<cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#' ">
</cfif>
</cfif>
<cfset sql = sql & ' order by #sort# #sortorder#'>
<!--- end building query string--->
<cfquery name="getstudents"
DATASOURCE="#ODBC_SOURCE#"
username="#ODBC_USERID#"
password= "#ODBC_PASSWD#">
#PreserveSingleQuotes(sql)
</CFQUERY>
Well, glad you solved it at least. =) Wish we could have helped more!
change this variable to #users_selection_from_form