Solved

SELECT menu does not display values from a query.

Posted on 2014-04-26
19
309 Views
Last Modified: 2014-05-01
ColdFusion 9
MS SQL Server 2012

Short version: SELECT menu does not display values from a query.

Details: I am moving away from CFFORM, CFINPUT, CFSELECT, and the scripts contained in CFIDE. That has been going along mostly well. I broke a SELECT menu, though.

I am trying to get this SELECT menu to display results of a query, as it used to do when it was a CFSELECT menu.

Here is the code:

<p>Choose parent page from the Select menu:</p>

<!--- this query gets grandchild pages, so we can OMIT grandchild pages from the SELECT MENU --->
<cfquery name="GetParents" datasource="#application.datasource#">
    SELECT        base.PageID  
                 ,base.PageTitle 
                 ,base.SortOrder 
                 ,base.ParentID
     
FROM #REQUEST.contentTable# base
            LEFT JOIN #REQUEST.contentTable# p ON p.pageID = base.parentID   <!--- find parent --->
            LEFT JOIN #REQUEST.contentTable# gc ON gc.pageID = p.parentID      <!--- find grandchild ---> 
WHERE gc.PageID IS NULL
AND     base.PageID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
AND     base.ParentID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="100000">
      
</cfquery>
    
  
<!--- query getSelectedParents gets SELECTED Parent page from content table --->
  <cfquery datasource="#application.datasource#" name="getSelectedParents">
        SELECT  PageTitle, ParentID
        FROM    #REQUEST.contentTable#
        WHERE ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.ParentID)#">
</cfquery>

<cfset selectedParents = valueList(getSelectedParents.ParentID)>
  
  <!--- use this select menu to display and assign parent page Titles --->
  <!--- user should see a human-readable list of parent page titles --->
	  	<cfoutput><select size="8" name="ParentID" value="PageID" display="PageTitle" multiple="no" query="GetParents" queryPosition="below" selected="#selectedParents#" id="ParentID" style="width:250px;" tabindex="5">    	</select></cfoutput>
  <!--- when form is processed, the correct ParentID is populated into content table --->
  <!--- ParentID value 0 means the record / web page is included in top-level, main navigation menu --->
  <!--- ParentID value 100000 means the record / web page is EXCLUDED from navigation menu, and is a floating page --->
  
  </cfif>

Open in new window


I think the queries "GetParents" and "getSelectedParents" are OK. I think I have not done right with the syntax of the SELECT menu.

What am I missing?

Thank you again for your help. =)

Eric
0
Comment
Question by:Eric Bourland
  • 13
  • 5
19 Comments
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40024657
So far I have this -- but I find that multiple options are selected, rather than a single parent page:

<p>Choose parent page from the Select menu:</p>

<!--- this query gets grandchild pages, so we can OMIT grandchild pages from the SELECT MENU --->
<cfquery name="GetParents" datasource="#application.datasource#">
    SELECT        base.PageID  
                 ,base.PageTitle 
                 ,base.SortOrder 
                 ,base.ParentID
     
FROM #REQUEST.contentTable# base
            LEFT JOIN #REQUEST.contentTable# p ON p.pageID = base.parentID   <!--- find parent --->
            LEFT JOIN #REQUEST.contentTable# gc ON gc.pageID = p.parentID      <!--- find grandchild ---> 
WHERE gc.PageID IS NULL
AND     base.PageID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
AND     base.ParentID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="100000">
      
</cfquery>
    
  
<!--- query getSelectedParents gets SELECTED Parent page from content table --->
  <cfquery datasource="#application.datasource#" name="getSelectedParents">
        SELECT  PageTitle, ParentID
        FROM    #REQUEST.contentTable#
        WHERE ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.ParentID)#">
</cfquery>

<cfset selectedParents = valueList(getSelectedParents.ParentID)>
  
<!--- use this select menu to display and assign parent page Titles --->

<!--- user should see a human-readable list of parent page titles --->

<!---	  	<cfoutput><select size="8" name="ParentID" value="PageID" display="PageTitle" multiple="no" query="GetParents" queryPosition="below" selected="#selectedParents#" id="ParentID" style="width:250px;" tabindex="5">    	</select></cfoutput>--->


  <select size="10"
    name="ParentID"
    multiple="no"
	tabindex="5"
    required="yes">
   <cfoutput query="GetParents">
    <option value="#GetParents.PageTitle#"
	<cfif getSelectedParents.ParentID EQ GetParents.ParentID>selected</cfif>>#GetParents.PageTitle#</option>
    </cfoutput>
</select>        
        
<!--- when the form is processed, the correct ParentID is populated into the content table --->

<!--- ParentID value 0 means the record / web page is included in top-level, main navigation menu --->

<!--- ParentID value 100000 means the record / web page is EXCLUDED from the navigation menu, and is a floating page --->
  

Open in new window


I am continuing to play with this....
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40024859
>  multiple="no"

I've gotta' run, but I think the HTML attribute is just "multiple". If it's selecting multiple values, perhaps its ignoring the "=no". Try removing it entirely
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40024920
Hi Eric - I am wondering if you are saying that the list of items on the select menu are duplicated?   At first, I was thinking you were saying that are getting multiple options to be selected, but looking at the code, I think you may have the same parent item appearing on the drop down more than once - is that what you are saying?

If so, this query needs adjusting... it is set up to be a list of unique parents, but you are also selecting the child, so you can easily get multiple parents on the list...

<cfquery name="GetParents" datasource="#application.datasource#">
    SELECT        base.PageID  
                 ,base.PageTitle 
                 ,base.SortOrder 
                 ,base.ParentID
     
FROM #REQUEST.contentTable# base
            LEFT JOIN #REQUEST.contentTable# p ON p.pageID = base.parentID   <!--- find parent --->
            LEFT JOIN #REQUEST.contentTable# gc ON gc.pageID = p.parentID      <!--- find grandchild ---> 
WHERE gc.PageID IS NULL
AND     base.PageID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
AND     base.ParentID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="100000">
      
</cfquery>
    

Open in new window



Should be something like this.. ??

<cfquery name="GetParents" datasource="#application.datasource#">
    SELECT   distinct base.ParentID
	                , p.pageTitle     
FROM #REQUEST.contentTable# base
            LEFT JOIN #REQUEST.contentTable# p ON p.pageID = base.parentID   <!--- find parent --->
            LEFT JOIN #REQUEST.contentTable# gc ON gc.pageID = p.parentID      <!--- find grandchild ---> 
WHERE gc.PageID IS NULL
AND     base.PageID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
AND     base.ParentID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="100000">
      
</cfquery>

Open in new window


That's a bit of a guess how to pull DISINTCT parent ID with that parent's TItle.. may need tweeking, but you cannot get a list of distinct parent IDs if you also include the page ID
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40024950
_agx_, gdemaria, good to hear from you both; also, sorry to bother you on a Saturday. I have been staring at this problem for most of the day, and trying different things; I am also trying to understand exactly what the queries do, and exactly what the SELECT form HTML is doing.

I tried gdemaria's new query, above. That seems to result in only four of the pages being displayed:

 <select size="10"
    name="ParentID"
	tabindex="5"
    selected="#selectedParents#">
   
    <option value="0"></option>
    
    <option value="2">Media</option>
    
    <option value="4">About Us</option>
    
    <option value="5">Core Goals</option>
    
    <option value="7">Research Initiatives</option>
    
</select>  

Open in new window


Not sure why I get this result. =)

I have tried different things -- sometimes I get multiple options selected -- by which I mean, all of the displayed options are unique, but more than one is selected, which should not be possible, since a page should have only one parent page.

Other times, I get no options selected at all.

So, here is what I have so far -- two queries, and some HTML for the SELECT menu:

<p>Choose parent page from the Select menu:</p>

<!--- query 1: get parent, child, and grandchild pages; also, OMIT grandchild pages from the SELECT MENU --->
<cfquery name="GetParents" datasource="#application.datasource#">
    SELECT   distinct base.ParentID
	                , p.pageTitle     
FROM #REQUEST.contentTable# base
            LEFT JOIN #REQUEST.contentTable# p ON p.pageID = base.parentID   <!--- find parent --->
            LEFT JOIN #REQUEST.contentTable# gc ON gc.pageID = p.parentID      <!--- find grandchild ---> 
WHERE gc.PageID IS NULL
AND     base.PageID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
AND     base.ParentID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="100000">
      
</cfquery>    
  
<!--- query 2: get SELECTED parent page from content table --->
  <cfquery datasource="#application.datasource#" name="getSelectedParents">
        SELECT  PageTitle, ParentID
        FROM    #REQUEST.contentTable#
        WHERE ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.ParentID)#">
</cfquery>

<cfset selectedParents = valueList(getSelectedParents.ParentID)>
  
<!--- use this select menu to display and assign parent page Titles --->

<!--- user should see a human-readable list of parent page titles --->

<!---

OLD CFSELECT (to be removed -- left here as an example of what was working before I decided to change things)

<cfoutput>
<cfselect size="8" name="ParentID" value="PageID" display="PageTitle" multiple="no" query="GetParents" queryPosition="below" selected="#selectedParents#" id="ParentID" style="width:250px;" tabindex="5"></cfselect>
</cfoutput>

--->

<!--- new SELECT FORM which might need to be adjusted --->

  <select size="10"
    name="ParentID"
	tabindex="5"
    selected="#selectedParents#">
   <cfoutput query="GetParents">
    <option value="#GetParents.ParentID#">#GetParents.PageTitle#</option>
    </cfoutput>
</select>       
        
<!--- when the form is processed, the correct ParentID is populated into the content table --->

<!--- ParentID value 0 means the record / web page is included in top-level, main navigation menu --->

<!--- ParentID value 100000 means the record / web page is EXCLUDED from the navigation menu, and is a floating page --->

Open in new window


I think I will try something else with the HTML in the SELECT menu.

I am not sure if the query GetParents selects the Parents and Children, but omits floating pages and grandchild pages? I am looking at that too. Am I on the right track? This is an interesting problem and I am learning a lot. (Also scrambling to get this done before girlfriend gets home and I gotta make dinner.)  =)

Thank you as always.

Eric
input1.gif
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40025084
I can explain the erratic results I reported previously.

When I use this query for GetParents, then the SELECT menu displays all of the Parent and Child pages, and none of the Grandchild or Floating page -- which is good. However, there are multiple selected OPTIONS -- I will try to explain this: all of the records that precede the record being edited are selected in the SELECT menu.

Example: I edited record "Core Goal 5"; the SELECT menu displays Core Goals 1 - 4 all selected, as in the attached GIF image.

I tried gdemaria's suggestion using SELECT DISTINCT -- but got unexpected results in the SELECT menu.

>>>If so, this query needs adjusting... it is set up to be a list of unique parents, but you are also selecting the child, so you can easily get multiple parents on the list...

Here is what I am using currently. I'll keep working on this.

<p>Choose parent page from the Select menu:</p>

<!--- query 1: get parent, child, and grandchild pages; also, OMIT grandchild pages from the SELECT MENU --->
<cfquery name="GetParents" datasource="#application.datasource#">
    SELECT        base.PageID  
                 ,base.PageTitle 
                 ,base.SortOrder 
                 ,base.ParentID
                     
FROM #REQUEST.contentTable# base
            LEFT JOIN #REQUEST.contentTable# p ON p.pageID = base.parentID   <!--- find parent --->
            LEFT JOIN #REQUEST.contentTable# gc ON gc.pageID = p.parentID      <!--- find grandchild ---> 
WHERE gc.PageID IS NULL
AND     base.PageID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
AND     base.ParentID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="100000">
      
</cfquery>    
  
<!--- query 2: get SELECTED parent page from content table --->
  <cfquery datasource="#application.datasource#" name="getSelectedParents">
        SELECT  PageTitle, ParentID
        FROM    #REQUEST.contentTable#
        WHERE ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.ParentID)#">
</cfquery>

<cfset selectedParents = valueList(getSelectedParents.ParentID)>
  
<!--- use this select menu to display and assign parent page Titles --->

<!--- user should see a human-readable list of parent page titles --->

<!---

OLD CFSELECT (to be removed)

<cfoutput>
<cfselect size="8" name="ParentID" value="PageID" display="PageTitle" multiple="no" query="GetParents" queryPosition="below" selected="#selectedParents#" id="ParentID" style="width:250px;" tabindex="5"></cfselect>
</cfoutput>

--->

<!--- new SELECT FORM which might need to be adjusted --->

 <select size="10"
    name="ParentID"
    multiple="no"
	tabindex="5"
    required="yes">
   <cfoutput query="GetParents">
    <option value="#GetParents.PageTitle#"
	<cfif getSelectedParents.ParentID EQ GetParents.ParentID>selected</cfif>>#GetParents.PageTitle#</option>
    </cfoutput>
</select>       
        
<!--- when the form is processed, the correct ParentID is populated into the content table --->

Open in new window



p.s. this is not an urgent task. I really appreciate your help, as always. E
input1.gif
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40025091
... which actually makes sense, since, in the SELECT menu, I have:

<cfif getSelectedParents.ParentID EQ GetParents.ParentID>selected</cfif>>

So, if other pages have the same parent as the page being edited, then all of those pages are selected in the SELECT menu.

I think I need to adjust the SELECT menu CFIF.

Thanks for letting me think aloud in this space. =)

E
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40025106
Eric,
I think you need to remove the idea of the select and such and focus entirely on the query.   If you are able to build a query that shows all the pages you want to show, then your select will work.   The query from my example, selects the parent pages of a particular page.   But maybe you want to show ALL pages in the database and have any of them be parent pages if the user selects them.

So first, think about what you want the query to select (all pages, just parent pages, just parent pages of a particular page),  then write a query that will do that, then plug it into the select tag and it should work.

In short, I think the issue is the select statement, not the drop down menu.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40027031
Good morning! I worked on this over the weekend.

OK, here is my goal: I want a select menu that displays parent and child pages, but not grandparent pages; and displays -- as selected -- the selected ParentID page when I come back later and edit the record.

>>> then write a query that will do that, then plug it into the select tag and it should work.
In short, I think the issue is the select statement, not the drop down menu.

I thought about that a lot. I believe what I want this query to do is:

select all parent pages
select all child pages

... from a list of all records in table #REQUEST.contentTable#

and exclude:

* the current page being edited -- since a web page cannot be a child of itself

* grandparent records -- since I would like only parent and child pages to have child pages -- meaning, exclude child pages that have child pages of their own

* floating pages (ParentID = 100000) -- pages that are not in the navigation menu

Also, I will need column SortOrder from table #REQUEST.contentTable#, because I will need to assign a sort order value to parent, child, and grandparent pages.

I *think* this looks like the query I started out with -- but I have a feeling I am missing something, probably in the area of the LEFT JOINs.

I think I need to rethink the LEFT JOINs, and I am not sure that LEFT JOIN is the right solution since really we are working only with one table -- table #REQUEST.contentTable# ... I am not sure? Meaning I think there is only a left table and not a right table. (?)

Anyway, I go forward and start with this:

<!--- query 1: get parent, child pages; OMIT grandchild pages --->
<cfquery name="GetParents" datasource="#application.datasource#">
    SELECT        base.PageID  
                 ,base.PageTitle 
                 ,base.SortOrder 
                 ,base.ParentID
                     
FROM #REQUEST.contentTable# base

LEFT JOIN #REQUEST.contentTable# p ON p.pageID = base.parentID

<!--- notes: find matching values in left table (base) ParentID and right table (p) PageID; I think this means we have found ALL of parentIDs in #REQUEST.contentTable# that match the parentID of the record being edited --->

LEFT JOIN #REQUEST.contentTable# gc ON gc.pageID = p.parentID

<!--- notes: originally this statement found all grandchild pages that matched the parentID .... but having stared at this line for a while I am not sure just what it does ... I know that I want to find grandchild pages and the exclude them from SELECT menu, later ---> 

WHERE gc.PageID IS NULL <--- this part does make sense --->

AND base.PageID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#"> <!--- omit current record that is being edited ---> 

AND base.ParentID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="100000">   <!--- omit floating pages, which have pageID 100000 ---> 
</cfquery>

Open in new window



So that is my rudimentary query.

I need a query that gets me the parentID value of the record, and then matches that ParentID value <em>with</em> the PageID value of the page that is the parent of the record being edited. I hope that makes sense; it made sense to me when I typed it. =)

Am I on the right track? Are the LEFT JOINs what I need in query GetParents? I don't see how the second LEFT JOIN works. I want to find grandchild pages -- by which I mean, pages that are <em>children</em> of child pages. Then I want to omit them. But I am not sure how to do this.

This all worked like a champion when I was using CFSELECT -- now I would like to understand this better, and use plain SELECT. =)

Thank you for continuing to help me. I hope your weekend was good.

Eric
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40027108
Eric, the query should not have to change between CFSELECT and SELECT... why don't we start by looking at the CFSELECT and Query when it was working.

Btw, why are you making these changes if it was working before?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 3

Author Comment

by:Eric Bourland
ID: 40027172
>>>>Btw, why are you making these changes if it was working before?

I'll try to explain.

I am trying to not use CFFORM, CFSELECT, or CFINPUT. When I use CFFORM, I discover that ColdFusion automatically inserts extra input validation scripts in the page -- it just seems untidy to me. Also, I want to stop using the scripts that are located in the CFIDE folder, and I don't want to include a virtual directory /CFIDE/ in my web sites anymore. I am concerned about the security risk of using CFIDE scripts or even referring to CFIDE. Maybe I am misguided or paranoid in this regard. But, also, I want to try to do this without CFIDE and without CFFORM. So, far, with my other form inputs, it's working.

Finally, I am starting to use CSS3 / HTML 5 to client-validate form inputs. I like how it looks, and I like having full control of the scripts, CSS, and HTML. I feel like CFFORM takes this control away from me. And -- this was after a lot of trial and error -- I found that the CSS3 / HTML 5 validation I was using worked a lot better in INPUT rather than CFINPUT -- event though ColdFusion does render "CFINPUT" as "INPUT" in a final web page. This was pretty curious to me and I spent a bunch of time testing different CSS 3 validation solutions.

I would love to get SELECT to work. Here is the original CFSELECT:

<p>Choose parent page from the Select menu:</p>

<!--- query to get grandchild pages, so we can omit them from SELECT MENU --->
<cfquery name="GetParents" datasource="#application.datasource#">
    SELECT        base.PageID  
                 ,base.PageTitle 
                 ,base.SortOrder 
                 ,base.ParentID  
     
FROM #REQUEST.contentTable# base
            LEFT JOIN #REQUEST.contentTable# p ON p.pageID = base.parentID   <!--- find parent --->
            LEFT JOIN #REQUEST.contentTable# gc ON gc.pageID = p.parentID      <!--- find grandchild ---> 
WHERE gc.PageID IS NULL
AND     base.PageID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
AND     base.ParentID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="100000"> 
        
</cfquery>

Open in new window


Next, here is the query that requests the SELECTED parentID:

<!--- query getSelectedParents selects SELECTED Parent page from content table --->

  <cfquery datasource="#application.datasource#" name="getSelectedParents">
        SELECT  PageTitle, ParentID
        FROM    #REQUEST.contentTable#
        WHERE ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.ParentID)#">
</cfquery>

<cfset selectedParents = valueList(getSelectedParents.ParentID)>

These two queries are tested and reliable. I found, though, that when I used SELECT, rather than CFSELECT, that the correct parent page from the content table would not display as the selected page.

Here is the original SELECT menu. Combined with the two queries above, this worked very well when I used CFSELECT:
 <!-- use this select menu to display and assign parent page Titles -->
  <!-- user should see a human-readable list of parent page titles -->
  <!-- parent and child pages may be parents of pages -- but grandchild or floating pages may not -->
	  	<cfselect size="8" name="ParentID" value="PageID" display="PageTitle" multiple="no" query="GetParents" queryPosition="below" selected="#selectedParents#" id="ParentID" style="width:250px;">
      
    	</cfselect>                
  <!-- when form is processed, the correct ParentID is populated into content table -->
  <!-- ParentID value 0 means the record / web page is parent page and included in top-level, main navigation menu -->
  <!-- ParentID value 100000 means the record / web page is a floating page and EXCLUDED from navigation menu -->

Open in new window


Of course, to use CFSELECT, I had to use CFFORM too, which I am trying to not do.

I will try it again with SELECT and see what I get. I started this last week; maybe I will see something I missed this time.

I'll report back what I find. More soon.... E
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40027193
Here is what I find:

<select size="8" name="ParentID" value="PageID" display="PageTitle" multiple="no" query="GetParents" queryPosition="below" selected="#selectedParents#" id="ParentID" style="width:250px;">
     
          </select>

This results in an empty box where the SELECT menu used to be.

I'll guess that the original queries are correct and I have left them in place.

But is there a way to get this SELECT menu to display the output of the query GetParents, as the CFSELECT menu does?

That is really what I am after.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40028504
I am starting over, beginning with the original file and the original query and select menu. I worked on this for a while today and I seemed to get close -- but am getting some weird errors now and I think I had better go back to an earlier version. I will continue tomorrow. gdemaria and _agx_, thank you as always. I am making progress. Have a great evening.

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40031205
Sorry to get back here so late. Here's my update. I went back to the beginning, and worked from there. I am using CFFORM again, and CFSELECT. I can live with this, if I have to. I have removed all references to CFIDE and that makes me feel like my web site is more secure.

I am getting a strange error now and I wonder what you make of it.

When I process the update form, I get this error:

 Invalid data 7,100000 for CFSQLTYPE CF_SQL_INTEGER.
 
The error occurred in C:/websites/test2.ebwebwork.com/admin/editPages.cfm: line 125
Called from C:/websites/test2.ebwebwork.com/admin/editPages.cfm: line 90
Called from C:/websites/test2.ebwebwork.com/admin/editPages.cfm: line 64
Called from C:/websites/test2.ebwebwork.com/admin/editPages.cfm: line 55
Called from C:/websites/test2.ebwebwork.com/admin/editPages.cfm: line 1

123 :            SortOrder = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.SortOrder)#">,
124 :            safeURL = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.safeURL,255))#">
125 :            WHERE PageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
126 : 			</cfquery>

Open in new window


It seems like ColdFusion is submitting a list of values for the PageID on line 125, rather than just the PageID.

I know where the "7,100000" comes from.

"7" is the value of the PageID of the Parent page of the page being edited.

"100000" is the value assigned to a ParentID when a page is a floating page, and has no parent.

So, Coldfusion tries to assign two values to the PageID of the record being edited: the PageID of the parent, and the ParentID value of a floating page. Very strange!

Note: When I update a record that does NOT have a parent selected in the SELECT menu, this error does not occur. Error occurs only when I select a parent page from the SELECT menu.

The update query looks normal, and I have combed through this code over and over. What am I missing? Thank you again for your help.

Eric

<!-----
Name:        editPages.cfm
Author:      Eric Bourland / gdemaria / _agx_
Description: add, update web pages; create SEO-friendly URLs for web pages; make this code resuable and portable
Created: September 2009
Revised: April 2014
ColdFusion Version 9
MS SQL Server 2012
----->

<!--- set a default value "" for PageID in scope URL --->
<cfparam name="url.PageID" default="">

<!--- define the PageID in scope FORM, then set form.PageID equal to the PageID passed in the URL --->
<cfparam name="form.PageID" default="#url.PageID#">

<!--- set default values for other user-editable fields --->
<cfparam name="form.PageTitle" default="">
<cfparam name="form.PageContentLeft" default="">
<cfparam name="form.PageContentRight" default="">
<cfparam name="form.DateCreated" default="">
<cfparam name="form.safeURL" default="">
<cfparam name="form.ParentID" default="0">
<cfparam name="form.SortOrder" default="0">
<cfparam name="form.isProtected" default="0"> <!--- checkbox --->
<cfparam name="form.isHome" default="0"> <!--- checkbox --->


<!--- in user-editable fields, set up protection against XSS  --->
    <cfloop collection="#FORM#" item="field">
      <cfset FORM[ field ] = ReReplaceNoCase (FORM[ field ], "<script.*?>.*?</script>", "", "all")>
    </cfloop>


<!--- query editPage tells ColdFusion which page to edit--->
<cfquery datasource="#application.datasource#" name="editPage">
SELECT PageID
      ,PageTitle
      ,safeURL
      ,PageContentLeft
      ,PageContentRight
      ,SortOrder
      ,ParentID
      ,DateCreated
      ,DateModified
      ,isHome
      ,isProtected
    
FROM #request.contentTable#
WHERE PageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
</cfquery>

		   
<!---- begin CFTRY; catch errors ---->
<cftry>  
 
<!---- populate cftry with error message ---->
<cfset variables.error = ""> 
 
<!--- BEGIN: Save action --->

<!--- begin form.doSave --->

<cfif IsDefined("FORM.doSave")>


 <!--- in this query select NOTHING from the main table and simply check if PageTitle exists --->
 
 <cfquery datasource="#APPLICATION.dataSource#" name="CheckPageTitle">
  SELECT 'Nothing' FROM #REQUEST.contentTable#
  WHERE PageTitle = <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#Form.PageTitle#">
  AND pageID <> <CFQUERYPARAM CFSQLTYPE="cf_sql_integer" VALUE="#val(Form.pageID)#">
  </cfquery>
   
  
   <!--- if PageTitle exists, display error --->
   
<cfif CheckPageTitle.recordcount GT 0>
   <cfthrow message="Page title <em>'#PageTitle#'</em> is already taken; you must enter a unique page title.">
</cfif>


 <!--- set form.safeURL = form.PageTitle  --->
<cfset form.safeURL = replace(trim(form.PageTitle)," ","-","all")>
<cfset form.safeURL = rereplace(form.safeURL ,"[^[0-9a-zA-Z-]]*","","all")>


<!--- when a PageID Exists, the action is UPDATE --->
   
<cfif val(form.PageID)>

<!--- create SEO-Safe URL for this web page --->

<!--- set form.NEW_SafeURL equal to value of form.PageTitle --->
<cfset form.NEW_SafeURL = replace(trim(form.PageTitle)," ","-","all")>
<cfset form.NEW_SafeURL = rereplace(form.NEW_SafeURL ,"[^[0-9a-zA-Z-]]*","","all")>

<!--- Set the value of form.OLD_SafeURL equal to output of query editPage --->
<cfset form.OLD_SafeURL = editPage.safeURL>

<!--- delete the existing safeURL file from /pages/ --->
     <cffile action="delete"
             file = "c:\websites\#REQUEST.companyURL#\pages\#form.OLD_SafeURL#.cfm">

<!--- write the new safeURL file to /pages/ using value of form.NEW_SafeURL --->
     <cffile action = "write"
    	     nameconflict="overwrite"
             file = "c:\websites\#REQUEST.companyURL#\pages\#FORM.NEW_SafeURL#.cfm"
             output='<cfset url.pageID = #val(FORM.PageID)#><cfinclude template="/index.cfm">'>


<!--- Finished creating SEO-Safe URL for this web page --->
             
<!--- query UpdatePage updates a page record in content table --->
            <cfquery name="UpdatePage" datasource="#application.datasource#">
				  UPDATE #REQUEST.contentTable#
				  SET
           PageTitle = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.PageTitle,255))#">,   
           PageContentLeft = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.PageContentLeft)#">,
           PageContentRight = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.PageContentRight)#">,
           DateModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">,
           ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.parentID#" null="#form.parentID eq -1#">,
           SortOrder = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.SortOrder)#">,
           safeURL = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.safeURL,255))#">
           WHERE PageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
			</cfquery>
     

<!--- CFELSE: if PageID does not exist, then create new Page --->
				<cfelse> 

<!--- query to insert new user record into content table --->
			<cfquery name="InsertPage" datasource="#application.datasource#" result="newPage">
				 INSERT INTO #REQUEST.contentTable#
     					(
                        PageTitle,
                        PageContentLeft,
                        PageContentRight,
                        ParentID,
                        SortOrder,
                        DateCreated,
                        safeURL
                        )
			     VALUES(
	                <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.PageTitle,255))#">,   
                    <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.PageContentLeft)#">,
                    <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(form.PageContentRight)#">,
                    <cfqueryparam cfsqltype="cf_sql_integer" value="#form.parentID#" null="#form.parentID eq -1#">,
           			<cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.SortOrder)#">,
                    <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">,
                    <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.safeURL,255))#">				         )         
					</cfquery>
        
        
                   
<!--- use the result attribute value (newPage) to set form field value --->
      <cfset form.PageID = newPage.IDENTITYCOL>
      
         <!--- create SEO-Safe URL for this web page --->

<!--- set form.NEW_SafeURL equal to value of form.PageTitle --->
<cfset form.NEW_SafeURL = replace(trim(form.PageTitle)," ","-","all")>
<cfset form.NEW_SafeURL = rereplace(form.NEW_SafeURL ,"[^[0-9a-zA-Z-]]*","","all")>

  <!--- write the new safeURL file to /pages/ using value of form.NEW_SafeURL --->
     <cffile action = "write"
    	     nameconflict="overwrite"
             file = "c:\websites\#REQUEST.companyURL#\pages\#FORM.NEW_SafeURL#.cfm"
             output='<cfset url.pageID = #val(newPage.IDENTITYCOL)#><cfinclude template="/index.cfm">'>

<!--- Finished creating SEO-Safe URL for this web page --->
              
<!--- END queries to update or insert database records ---> 

<!--- END cfif val(form.PageID) -- if a topic needed to be updated or added, then it was done --->
					    </cfif>  


       <!--- done? relocate --->

<cfif val(url.PageID)>
<cflocation url="/admin/editPages.cfm?PageID=#val(url.PageID)#" addtoken="yes">

<cfelse>                     
<cflocation url="/admin/managePages.cfm" addtoken="no">
				     
</cfif>

             
<!--- END: Save action --->

<!--- END form.doSave --->
                    </cfif>
       
<!--- END queries to update or insert database records ---> 
        

<!--- this CFCATCH will trap errors -- the ones you threw or just regular database issues --->
            <cfcatch type="Any">
                 <cfset variables.error = cfcatch.message>
                 <cfrethrow>
            </cfcatch>

<!--- END CFTRY --->  
			</cftry>
       
       
     
<!--- fetch the data from the database only when there are no errors; let the form variables pass back from the data table into the form to display ---->
 
<cfif len(variables.error) eq 0>

  			<cfloop index="aCol" list="#editPage.columnList#">
			       <cfset "form.#aCol#" = editPage[aCol][editPage.currentRow]>
			  </cfloop>
    
</cfif>



<!----- if record already exists (it will have a PageID) then update record; otherwise, add new record ----->
				<cfif val(url.PageID)>
					  <cfset FormTitle="Update Page">
					  <cfset ButtonText="Update This Page">
				<cfelse>
						<cfset FormTitle="Create Page">
						<cfset ButtonText="Create Page">
				</cfif>

<cfinclude template="/admin/admin_header.cfm">


<cfinclude template="/admin/adminNav.cfm">

<cfinclude template="/admin/TinyMCE.cfm">

<!--- if there an error, display error in readable form --->

<cfif len(variables.error)> 
	  <cfoutput>
	    <div class="errorbox">#variables.error#</div>
	    </cfoutput>
   
   <br />

             <div class="center">
               <input type=button value="Go Back" onClick="history.go(-1)">
             </div>
             
             <cfabort>
</cfif>



<cfparam name="url.cftoken" default="">

<cfif len(url.cftoken)> 

<div class="center"><button class="medium green"><i class="icon-ok"></i> Update succeeded.</button></div>

</cfif>



<!--- Form begins here --->
	<cfform method="post" enctype="multipart/form-data" name="ebwebworkForm" class="ebwebworkForm">
                
 
 <!--- Embed pageID (PK) to assign a value to it --->
 <cfoutput>
<input type="hidden" name="PageID" value="#form.PageID#" />
   </cfoutput>

 
    <ul>
        <li>
<cfoutput>
<legend><h2>#FormTitle#</h2></legend>
</cfoutput>

     <img src="https://lh6.googleusercontent.com/-rXrwzErpu7Q/U06TdnsBKfI/AAAAAAAAAoA/5QepC-sHWpc/s800/red_asterisk.png" alt="Required Field" width="16" height="16" /> Required
      </li>
      
      

<p><strong>Page Title:</strong></p>


<li>
  <label for="PageTitle"><h3>Page Title:</h3></label>
  	 <cfoutput><input type="text" name="PageTitle" placeholder="Enter Page Title" value="#HTMLEditFormat(Trim(Left(form.PageTitle,255)))#" maxlength="255" tabindex="1" size="70" autofocus="true" required="yes" /></cfoutput>
        <span class="form_hint">Enter Page Title; 255 characters max.</span>         
</li>



<!--- if page exists, it will already have an SEO-Friendly web address (URL) --->
<cfif val(url.PageID)>

<p><strong>The SEO-friendly web address (URL) of this page is:</strong></p>
<div class="greensafebox">

<i class="icon-globe icon-4x pull-left green"></i>
   
<p style="padding-left:5em;"><span class="bold green"><cfoutput>http://#request.companyurl#/pages/#form.safeURL#.cfm</cfoutput></span></p>
</div>
</cfif>

   

<a name="tinymce"></a>
            <p class="center">Use TinyMCE to edit content:</p>

<li>
<label for="PageContentLeft"><h3>Left Column:</h3></label>

     
      <textarea name="PageContentLeft"
      		wrap="virtual"  
      		tabindex="2"
      		width="600"
	  		height="300"
      		style="width:600px;height:300px;">

           <cfoutput>#form.PageContentLeft#</cfoutput>
   
	  </textarea>
</li>
 
 
 
 <li>
<label for="PageContentRight"><h3>Right Column:</h3></label>

     
      <textarea name="PageContentRight"
      		wrap="virtual"  
      		tabindex="3"
      		width="600"
	  		height="300"
      		style="width:600px;height:300px;">

           <cfoutput>#form.PageContentRight#</cfoutput>
   
	  </textarea>
</li>
 
 </ul>

 
 <hr />           
         

<h2 class="center">How should this page fit in the web site navigation menu?</h2>

<p><strong>You have three options:</strong></p>

<h4>1. Make this page a parent page. <span class="tooltip question" title="A parent page is a menu item in main, top-level navigation.">?</span></h4>


<p><cfoutput><input type="radio" value="0" name="ParentID" checked="#editPage.ParentID eq 0#" tabindex="4" /></cfoutput>
Make this page a "Parent" page in the main navigation menu.</p>


<h4>2. Make this page a child page; choose a parent page under which this page should appear. <span class="tooltip question" title="A child page is a sub-menu item that appears beneath a parent page.">?</span></h4>

 
<p>Choose parent page from the Select menu:</p>

<!--- query to get grandchild pages, so we can omit them from SELECT MENU --->
<cfquery name="GetParents" datasource="#application.datasource#">
    SELECT        base.PageID  
                 ,base.PageTitle 
                 ,base.SortOrder 
                 ,base.ParentID
     
FROM #REQUEST.contentTable# base
            LEFT JOIN #REQUEST.contentTable# p ON p.pageID = base.parentID   <!--- find parent --->
            LEFT JOIN #REQUEST.contentTable# gc ON gc.pageID = p.parentID      <!--- find grandchild ---> 
WHERE gc.PageID IS NULL
AND     base.PageID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
AND     base.ParentID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="100000">
      
</cfquery>
    
  
<!--- query getSelectedParents selects SELECTED Parent page from content table --->
  <cfquery datasource="#application.datasource#" name="getSelectedParents">
        SELECT  PageTitle, ParentID
        FROM    #REQUEST.contentTable#
        WHERE ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.ParentID)#">
</cfquery>

<cfset selectedParents = valueList(getSelectedParents.ParentID)>
  
  <!--- use this select menu to display and assign parent page Titles --->
  <!--- user should see a human-readable list of parent page titles --->
	  	<cfoutput><cfselect size="8" name="ParentID" value="PageID" display="PageTitle" multiple="no" query="GetParents" queryPosition="below" selected="#selectedParents#" id="ParentID" style="width:250px;" tabindex="5">    	</cfselect></cfoutput>
  <!--- when form is processed, the correct ParentID is populated into content table --->
  <!--- ParentID value 0 means the record / web page is included in top-level, main navigation menu --->
  <!--- ParentID value 100000 means the record / web page is EXCLUDED from navigation menu --->
  
  
  <h4>Choose a "sort order" for this page.  
  <span class="tooltip question" title="For example, if you want this page to appear third in a series of menu items, enter a value of 3.">?</span><br />

    
    <cfoutput><input type="text" name="SortOrder" value="#editPage.SortOrder#" size="1" maxlength="2" tabindex="6" /></cfoutput>
    </h4>
  

<h4>3. Exclude this page from navigation. This page becomes a floating page.
<span class="tooltip question" title="You can exclude this page from navigation. Not all pages need to appear in the navigation menu.">?</span></h4>

<p><cfoutput><input type="radio" value="100000" name="ParentID" checked="#editPage.ParentID eq 100000#" tabindex="7" /></cfoutput>
Exclude this page from the navigation menu.</p>
      


<div class="rememberbox">

<h4>Stuff to remember:</strong></h4>

<p><span class="blue"><i class="icon-check icon-large"></i></span> A parent page is a menu item in main, top-level navigation.</p>

<p><span class="green"><i class="icon-check icon-large"></i></span> A child page is a sub-menu item that appears beneath a parent page.</p>
    
<p><span class="red"><i class="icon-check icon-large"></i></span> A page can be excluded from the navigation menu. This action does not delete the page. The page becomes a floating page.</p>
</div>
                    

   <!--- submit form to ColdFusion for processing; this is the DoSave function, which will add or edit a record --->

<div class="submitButton">
   <cfoutput>  
   <button name="doSave" type="submit" class="green" tabindex="8">#ButtonText#</button>
   </cfoutput>
</div>  

</cfform>



<!--- Page footer --->
<cfinclude template="/admin/admin_footer.cfm">

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40032113
I don't understand Eric.   How does this differ from your original file?   CFFORM uses /CFIDE so how did you remove references to it?

My recommendation was to go back to the original file and then just change the CFSELECT to a SELECT without any other changes.   Once that is working, you can change CFFORM to FORM.

I don't know of any security issues using /CFIDE/ it is just like using jquery or any other included set of scripts.    If you have decided to use a different set of scripts, that's cool... but don't do all this work for security.   If you're referring to the /ADMIN security issue, that is completely different from the /CFIDE directory.    You just need to move, rename or security the /ADMIN folder.
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 40032125
Eric,
Taking your CFSELECT from an earlier post, here is what the SELECT may look like.

I suggest taking your original code and replacing the CFSELECT with this and see how it goes.

<select size="8" name="ParentID" multiple="no" id="ParentID" style="width:250px;">
   <option value="" <cfif selectedParents eq "">selected</cfif> > - No Parent - </option>
  <cfoutput query="getParents">
   <option value="#getparents.pageID#" <cfif listFind(selectedParents,getParents.pageId)>selected</cfif> > #getparents.pageTitle# </option>
  </cfoutput>
</select>

Then step by step, change any other CFINPUT tags to INPUT tags and test, then do the CFFORM to FORM.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40032976
Yep, got it. I'm working on this and will let you know what I find. Thanks, gdemaria. I hope your day is going well. =) E
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40034925
gdemaria,

I've been doing a lot of testing.

The SELECT code you suggest above works really well. =)

Also, with the inclusion of your - No Parent Page - option, I am able to simplify the form. I assign a value of 100000 to the No Parent Page option, and then, when No Parent Page is selected, the page becomes a floating page that is not in navigation.

Something else is going on, and I have narrowed down the problem to a few lines of code with the SortOrder ... but I am trying a few things and will come back here later today.

It's making a lot more sense and is turning into a better application. Thank you again and I hope your day is going well. More soon.

Eric
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 40035108
It's working really well.

I was able to simply the form; the user can now choose Parent page, Child page, or floating page from the Select menu.

 <label for="ParentID"><h3>Choose from the Select menu:</h3>


<!--- query to get grandchild pages, so we can omit them from SELECT MENU --->
<cfquery name="GetParents" datasource="#application.datasource#">
    SELECT        base.PageID  
                 ,base.PageTitle
                 ,base.SortOrder
                 ,base.ParentID  
     
FROM #REQUEST.contentTable# base
            LEFT JOIN #REQUEST.contentTable# p ON p.pageID = base.parentID   <!--- find parent --->
            LEFT JOIN #REQUEST.contentTable# gc ON gc.pageID = p.parentID      <!--- find grandchild --->
WHERE gc.PageID IS NULL
AND     base.PageID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.PageID)#">
AND     base.ParentID <>  <cfqueryparam cfsqltype="cf_sql_integer" value="100000">
       
</cfquery>
   
 
<!--- query getSelectedParents selects SELECTED Parent page from content table --->
  <cfquery datasource="#application.datasource#" name="getSelectedParents">
        SELECT  PageTitle, ParentID
        FROM    #REQUEST.contentTable#
        WHERE ParentID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.ParentID)#">
</cfquery>

<cfset selectedParents = valueList(getSelectedParents.ParentID)>
 
  <!--- use this select menu to display and assign parent page Titles --->
  <!--- user should see a human-readable list of parent page titles --->
<select size="8" name="ParentID" multiple="no" id="ParentID" style="width:250px;">

   <option value="0" <cfif form.ParentID eq "0">selected</cfif> > - Parent Page in Main Navigation - </option>
   
   <option value="100000" <cfif selectedParents eq "">selected</cfif> > - Floating Page / No Parent - </option>
 
  <cfoutput query="getParents">
   <option value="#getparents.pageID#" <cfif listFind(selectedParents,getParents.pageId)>selected</cfif> > #getparents.pageTitle# </option>
  </cfoutput>
</select>                
  <!--- when form is processed, the correct ParentID is populated into content table --->
  <!--- ParentID value 0 means the record / web page is included in top-level, main navigation menu --->
  <!--- ParentID value 100000 means the record / web page is EXCLUDED from navigation menu and is a floating page --->

And the selected value stays selected the next time the page is edited. Also, I have been able to omit use of CFFORM elements. This solution makes sense to me and gave me the chance to simplify the application.

Thanks as always to gdemaria and _agx_.

I hope you all are having a great day. Take care.

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40035115
~simplify the form, not simply the form.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now