[mossman65]

Hi Everyone,

I'm working on a drill down search for car parts. The search had been working just fine until i tried switching the order of the drop downs. Working order goes Year/Make/Model/Outlets/Length/Width i would like to change the order to Year/Make/Outlets/Model/Length/Width but it throws the error "Complex object types cannot be converted...".

I was able to switch the length and width without it throwing the error, so i'm confused that if i can switch those without error why this switch creates an error.

Thank you very much for any help on this!

Here is the code:

Original:

<form name="DropDown" method="post">
<table width="100%" cellpadding="0" cellspacing="0">
	<tr>
		<td>
			<cfoutput>
                    <cfquery name="get_Main_Group" datasource="DSN">
                        SELECT DISTINCT Year FROM MoparParts  
                        ORDER BY Year ASC
                    </cfquery>
                  <select style="width:100px; border-color:##666666;"  name="select_Main_Group" required="yes" onchange="this.form.submit()">
                     <option>Year</option>
                     <cfloop query="get_Main_Group">
                         <option value="#Year#" <cfif isDefined('form.select_Main_Group')>
                         <cfif form.select_Main_Group eq "#Year#">selected</cfif></cfif>>#Year#</option>
                     </cfloop>
                </select>
			</td>
            <td>
				<cfif isDefined('page.select_Main_Group')>
                   <cfquery name="get_Sub_Group" datasource="DSN">
                        SELECT DISTINCT Make
                        FROM MoparParts 
                        WHERE Year = '#page.select_Main_Group#' 	
                        ORDER BY Make ASC
                   </cfquery>
                   <select style="width:100px; border-color:##666666;" name="select_Sub_Group" required="yes" onchange="this.form.submit()">
                      <option>Make</option>
                      <cfloop query="get_Sub_Group">
                         <option value="#Make#" <cfif isDefined('form.select_Sub_Group')>
                         <cfif form.select_Sub_Group eq "#Make#">selected</cfif></cfif>>#Make#</option>
                      </cfloop>
                   </select>
                   <cfelse>
                   <img style="padding-left:8px" src="images/drop_dwn_mopar_make.jpg" />
                </cfif>
			</td>
            <td>					
				<cfif isDefined('page.select_Sub_Group')>
                   <cfquery name="get_Sub_Group_model" datasource="DSN">
                        SELECT DISTINCT Model
                        FROM MoparParts  
                        WHERE Year = '#page.select_Main_Group#'
                        AND Make = '#page.select_Sub_Group#' 
                        ORDER BY Model ASC
                   </cfquery>
                
                
                   <select style="width:100px; border-color:##666666;" name="select_Sub_Group_model" required="yes" onchange="this.form.submit()">
                      <option>Model</option>
                      <cfloop query="get_Sub_Group_model">
                         <option value="#Model#" <cfif isDefined('form.select_Sub_Group_model')>
                         <cfif form.select_Sub_Group_model eq "#Model#">selected</cfif></cfif>>#Model#</option>
                      </cfloop>
                   </select>
                   <cfelse>
                   <img style="padding-left:8px" src="images/drop_dwn_mopar_model.jpg" />
                </cfif>
				</td>
            	<td>					
					<cfif isDefined('page.select_Sub_Group_model')>
					   <cfquery name="get_Sub_Group_Outlets" datasource="DSN">
							SELECT DISTINCT Outlets
							FROM MoparParts  
                            WHERE Year = '#page.select_Main_Group#'
                            AND Make = '#page.select_Sub_Group#'
                            AND Model = '#page.select_Sub_Group_model#' 
                            ORDER BY Outlets ASC
					   </cfquery>
					
					
					   <select style="width:100px; border-color:##666666;" name="select_Sub_Group_Outlets" required="yes" onchange="this.form.submit()">
						  <option>Outlets</option>
						  <cfloop query="get_Sub_Group_Outlets">
							 <option value="#Outlets#" <cfif isDefined('form.select_Sub_Group_Outlets')>
                             <cfif form.select_Sub_Group_Outlets eq "#Outlets#">selected</cfif></cfif>>#Outlets#</option>
						  </cfloop>
					   </select>
                       <cfelse>
                       <img style="padding-left:8px" src="images/drop_dwn_mopar_outlets.jpg" />
					</cfif>
					
				</td>
            	<td>					
					<cfif isDefined('page.select_Sub_Group_Outlets')>
					   <cfquery name="get_Sub_Group_CoreLength" datasource="DSN">
							SELECT DISTINCT CoreLength
							FROM MoparParts  
                            WHERE Year = '#page.select_Main_Group#'
                            AND Make = '#page.select_Sub_Group#'
                            AND Model = '#page.select_Sub_Group_model#' 
                            AND Outlets = '#page.select_Sub_Group_Outlets#'
                            ORDER BY CoreLength ASC
					   </cfquery>
					
					
					   <select style="width:100px; border-color:##666666;" name="select_Sub_Group_CoreLength" required="yes" onchange="this.form.submit()">
						  <option>CoreLength</option>
						  <cfloop query="get_Sub_Group_CoreLength">
							 <option value="#CoreLength#" <cfif isDefined('form.select_Sub_Group_CoreLength')>
                             <cfif form.select_Sub_Group_CoreLength eq "#CoreLength#">selected</cfif></cfif>>#CoreLength#</option>
						  </cfloop>
					   </select>
                       <cfelse>
                       <img style="padding-left:8px" src="images/drop_dwn_mopar_length.jpg" />
					</cfif>
					
				</td>                
            	<td>					
					<cfif isDefined('page.select_Sub_Group_CoreLength')>

					   <cfquery name="get_Sub_Group_CoreWidth" datasource="DSN">
							SELECT DISTINCT CoreWidth
							FROM MoparParts  
                            WHERE Year = '#page.select_Main_Group#'
                            AND Make = '#page.select_Sub_Group#'
                            AND Model = '#page.select_Sub_Group_model#' 
                            AND Outlets = '#page.select_Sub_Group_Outlets#' 
                            AND CoreLength = '#page.select_Sub_Group_CoreLength#' 
                            ORDER BY CoreWidth ASC
					   </cfquery>

					   <select style="width:100px; border-color:##666666;" name="select_Sub_Group_CoreWidth" required="yes" onchange="this.form.submit()">
						  <option>CoreWidth</option>
						  <cfloop query="get_Sub_Group_CoreWidth">
							 <option value="#CoreWidth#" <cfif isDefined('form.select_Sub_Group_CoreWidth')>
                             <cfif form.select_Sub_Group_CoreWidth eq "#CoreWidth#">selected</cfif></cfif>>#CoreWidth# (tank to tank)</option>
						  </cfloop>
					   </select>
                       <cfelse>
                       <img style="padding-left:8px" src="images/drop_dwn_mopar_width.jpg" />


					</cfif>
					</td>



				</cfoutput>
                
            </tr>
        </table>
        

New Broken Code:

<form name="DropDown" method="post">
<table width="100%" cellpadding="0" cellspacing="0">
	<tr>
		<td>
			<cfoutput>
                    <cfquery name="get_Main_Group" datasource="DSN">
                        SELECT DISTINCT Year FROM MoparParts  
                        ORDER BY Year ASC
                    </cfquery>
                  <select style="width:100px; border-color:##666666;"  name="select_Main_Group" required="yes" onchange="this.form.submit()">
                     <option>Year</option>
                     <cfloop query="get_Main_Group">
                         <option value="#Year#" <cfif isDefined('form.select_Main_Group')>
                         <cfif form.select_Main_Group eq "#Year#">selected</cfif></cfif>>#Year#</option>
                     </cfloop>
                </select>
			</td>
            <td>
				<cfif isDefined('page.select_Main_Group')>
                   <cfquery name="get_Sub_Group" datasource="DSN">
                        SELECT DISTINCT Make
                        FROM MoparParts 
                        WHERE Year = '#page.select_Main_Group#' 	
                        ORDER BY Make ASC
                   </cfquery>
                   <select style="width:100px; border-color:##666666;" name="select_Sub_Group" required="yes" onchange="this.form.submit()">
                      <option>Make</option>
                      <cfloop query="get_Sub_Group">
                         <option value="#Make#" <cfif isDefined('form.select_Sub_Group')>
                         <cfif form.select_Sub_Group eq "#Make#">selected</cfif></cfif>>#Make#</option>
                      </cfloop>
                   </select>
                   <cfelse>
                   <img style="padding-left:8px" src="images/drop_dwn_mopar_make.jpg" />
                </cfif>
			</td>



            	<td>					
					<cfif isDefined('page.select_Sub_Group')>
					   <cfquery name="get_Sub_Group_Outlets" datasource="DSN">
							SELECT DISTINCT Outlets
							FROM MoparParts  
                            WHERE Year = '#page.select_Main_Group#'
                            AND Make = '#page.select_Sub_Group#'
                            ORDER BY Outlets ASC
					   </cfquery>
					
					
					   <select style="width:100px; border-color:##666666;" name="select_Sub_Group_Outlets" required="yes" onchange="this.form.submit()">
						  <option>Outlets</option>
						  <cfloop query="get_Sub_Group_Outlets">
							 <option value="#Outlets#" <cfif isDefined('form.select_Sub_Group_Outlets')>
							 <cfif form.select_Sub_Group_Outlets eq "#Outlets#">selected</cfif></cfif>>#Outlets#</option>
						  </cfloop>
					   </select>
                       <cfelse>
                       <img style="padding-left:8px" src="images/drop_dwn_mopar_outlets.jpg" />
					</cfif>
					
				</td>

            <td>					
				<cfif isDefined('page.select_Sub_Group_Outlets')>
                   <cfquery name="get_Sub_Group_model" datasource="DSN">
                        SELECT DISTINCT Model
                        FROM MoparParts  
                        WHERE Year = '#page.select_Main_Group#'
                        AND Make = '#page.select_Sub_Group#' 
						AND Outlets = '#get_Sub_Group_Outlets#'
                        ORDER BY Model ASC
                   </cfquery>
                
                
                   <select style="width:100px; border-color:##666666;" name="select_Sub_Group_model" required="yes" onchange="this.form.submit()">
                      <option>Model</option>
                      <cfloop query="get_Sub_Group_model">
                         <option value="#Model#" <cfif isDefined('form.select_Sub_Group_model')>
						 <cfif form.select_Sub_Group_model eq "#Model#">selected</cfif></cfif>>#Model#</option>
                      </cfloop>
                   </select>
                   <cfelse>
                   <img style="padding-left:8px" src="images/drop_dwn_mopar_model.jpg" />
                </cfif>
				</td>



            	<td>					
					<cfif isDefined('page.get_Sub_Group_model')>
					   <cfquery name="get_Sub_Group_CoreLength" datasource="DSN">
							SELECT DISTINCT CoreLength
							FROM MoparParts  
                            WHERE Year = '#page.select_Main_Group#'
                            AND Make = '#page.select_Sub_Group#'
                            AND Model = '#page.select_Sub_Group_model#' 
                            AND Outlets = '#page.select_Sub_Group_Outlets#'
                            ORDER BY CoreLength ASC
					   </cfquery>
					
					
					   <select style="width:100px; border-color:##666666;" name="select_Sub_Group_CoreLength" required="yes" onchange="this.form.submit()">
						  <option>CoreLength</option>
						  <cfloop query="get_Sub_Group_CoreLength">
							 <option value="#CoreLength#" <cfif isDefined('form.select_Sub_Group_CoreLength')>
                             <cfif form.select_Sub_Group_CoreLength eq "#CoreLength#">selected</cfif></cfif>>#CoreLength#</option>
						  </cfloop>
					   </select>
                       <cfelse>
                       <img style="padding-left:8px" src="images/drop_dwn_mopar_length.jpg" />
					</cfif>
					
				</td>                
            	<td>					
					<cfif isDefined('page.select_Sub_Group_CoreLength')>

					   <cfquery name="get_Sub_Group_CoreWidth" datasource="DSN">
							SELECT DISTINCT CoreWidth
							FROM MoparParts  
                            WHERE Year = '#page.select_Main_Group#'
                            AND Make = '#page.select_Sub_Group#'
                            AND Model = '#page.select_Sub_Group_model#' 
                            AND Outlets = '#page.select_Sub_Group_Outlets#' 
                            AND CoreLength = '#page.select_Sub_Group_CoreLength#' 
                            ORDER BY CoreWidth ASC
					   </cfquery>

					   <select style="width:100px; border-color:##666666;" name="select_Sub_Group_CoreWidth" required="yes" onchange="this.form.submit()">
						  <option>CoreWidth</option>
						  <cfloop query="get_Sub_Group_CoreWidth">
							 <option value="#CoreWidth#" <cfif isDefined('form.select_Sub_Group_CoreWidth')>
                             <cfif form.select_Sub_Group_CoreWidth eq "#CoreWidth#">selected</cfif></cfif>>#CoreWidth# (tank to tank)</option>
						  </cfloop>
					   </select>
                       <cfelse>
                       <img style="padding-left:8px" src="images/drop_dwn_mopar_width.jpg" />


					</cfif>
					</td>




				</cfoutput>
				                  
                
                
            </tr>
        </table>

[Craig328]

Mossman, you've posted about 300 lines of code, mentioned the error you're getting...but didn't mention which line the error is occurring on or point out between your two code samples where the difference between them is. We're happy to help but sifting through that much to get to the issue is time consuming.

Narrow it down a bit and we'll see what we can do to help.

[mossman65]

Thank you for replying! Sorry for the massive code post.

I can't see the line numbers until i post, i would guess its line 23. The error happens as soon as i choose the outlet configuration drop down, and it reloads to query the next set of choices.

<cfif isDefined('page.select_Sub_Group')>
<cfquery name="get_Sub_Group_Outlets" datasource="DSN">
SELECT DISTINCT Outlets
FROM MoparParts  
WHERE Year = '#page.select_Main_Group#'
AND Make = '#page.select_Sub_Group#'
ORDER BY Outlets ASC
</cfquery>


<select style="width:100px; border-color:##666666;" name="select_Sub_Group_Outlets" required="yes" onchange="this.form.submit()">
<option>Outlets</option>
<cfloop query="get_Sub_Group_Outlets">
<option value="#Outlets#" <cfif isDefined('form.select_Sub_Group_Outlets')>
<cfif form.select_Sub_Group_Outlets eq "#Outlets#">selected</cfif></cfif>>#Outlets#</option>
</cfloop>
</select>
<cfelse>
<img style="padding-left:8px" src="images/drop_dwn_mopar_outlets.jpg" />
</cfif>


<cfif isDefined('page.select_Sub_Group_Outlets')>
<cfquery name="get_Sub_Group_model" datasource="DSN">
SELECT DISTINCT Model
FROM MoparParts  
WHERE Year = '#page.select_Main_Group#'
AND Make = '#page.select_Sub_Group#' 
AND Outlets = '#get_Sub_Group_Outlets#'
ORDER BY Model ASC
</cfquery>


<select style="width:100px; border-color:##666666;" name="select_Sub_Group_model" required="yes" onchange="this.form.submit()">
<option>Model</option>
<cfloop query="get_Sub_Group_model">
<option value="#Model#" <cfif isDefined('form.select_Sub_Group_model')>
<cfif form.select_Sub_Group_model eq "#Model#">selected</cfif></cfif>>#Model#</option>
</cfloop>
</select>
<cfelse>
<img style="padding-left:8px" src="images/drop_dwn_mopar_model.jpg" />
</cfif>

If it helps her are the pages:

Working:
http://www.griffinrad.com/mopar.cfm

Broken:
http://www.griffinra...m/dev/mopar.cfm


Sadly i don't have the ability to turn the debug on for a better explanation of the error.

Thank you very much!

[Craig328]

Ah. Okay.

This is a good example of how sometimes CF's debug may not always get you to where you want to be. In this case, the error message gives you a hint of what's wrong but it kind of leaves it up to you to figure out exactly what's going on.

In your code example you have [<cfif isDefined('page.select_Sub_Group_Outlets')>] and that's what you think is erroring with the "Complex object types cannot be converted" error. If it were me, my first action would be to do a CFDUMP of page.select_Sub_Group_Outlets and see what I have there. My guess is that it'll be an array or a struct...in other words, some kind of complex data object. From appearances you have a struct called "page" that contains a bunch of stuff you want to use. My guess is that "select_Sub_Group_Outlets" is probably a 2 dimensional array, a query result set or a struct. Indeed, you cannot run an IsDefined on those kind of things. You can run an IsArray, IsQuery or IsStruct though...depending on what you have there.

So yeah, do a CFDUMP of what you're checking against (followed immediately by a CFABORT) and see what you see on the screen.

[mossman65]

Thank you! I'll give it a shot.

[mossman65]

mossman65, on 29 May 2012 - 09:30 AM, said:

Thank you! I'll give it a shot.

Hi Again,

The cfdump leads me to believe the error is in this query. When i place the dump above it i get results, after and i get the error. The odd thing is i use the same query in my other searches with no issues.

I did wonder if maybe the "Driver Top / Passenger Bottom" results for outlet configuration could be it. If the / might be the problem. It seems to run fine with the / in my other searches.

If you like i have the dump here: http://www.griffinra...m/dev/mopar.cfm

Is there any way i can get more detailed information using the dump tag?

Thank you!

                   
<cfquery name="get_Sub_Group_model" datasource="DSN">
                        SELECT DISTINCT Model
                        FROM MoparParts  
                        WHERE Year = '#page.select_Main_Group#'
                        AND Make = '#page.select_Sub_Group#' 
			AND Outlets = '#get_Sub_Group_Outlets#'
                        ORDER BY Model ASC
                   </cfquery>

[mossman65]

I'm not sure what i did wrong, but i went back and started over and everything is working now. I'm sorry for wasting your time, but thank you very much!

Aaron

[Craig328]

N/P. Glad to hear you got it resolved.

[mossman65]

mossman65, on 29 May 2012 - 10:20 AM, said:

I'm not sure what i did wrong, but i went back and started over and everything is working now. I'm sorry for wasting your time, but thank you very much!

Aaron

Another question,

Maybe you can you point me in the right direction... Currently my form reloads the page to get the next set of results. This makes it slow and cumbersome when you want to change your selections. How would I get it to query on change instead of on submit?

Thanks!
Aaron

[Craig328]

Are you asking about how to sequentially populate the dropdowns per the selections of the previous ones?

[mossman65]

Craig328, on 29 May 2012 - 11:36 AM, said:

Are you asking about how to sequentially populate the dropdowns per the selections of the previous ones?

Yes please.

[Craig328]

Ah. Well, there are a couple of different ways to do that but the simplest is what you're doing now: re-running the page with the selected drop down items as components of a query where statement.

You could do much the same thing with an Ajax call but that really only keeps the page from recycling each time.

I could make one suggestion though: you could do a single mass query, cache it and then run subsequent query of queries against that. The benefit of running QofQ against a one time run of the main query is that the query result set is in server memory and so running the QofQ should be quick. You'll still be rebuilding the page each time but the actual data retrieval would be somewhat faster.

There are also ways to do it with Javascript but the amount of script you'd need to write and the complexity of it all would probably not be worth your time.

Looking at your test page, it doesn't look all that slow to me. Where are you seeing a speed issue?

[mossman65]

I'll see it lag from time to time, when i say lag i mean you can tell that its refreshing the page vs instant. I can live with that.

The larger problem is that i'm using

onchange="this.form.submit()"

. I'll choose my Year, Make, and model, but say i made a mistake on which model i wanted. If i change it to a different car it executes the form.submit again and makes the next drop down execute. Also say i have chosen all options and i have been supplied with my results. If i click my back button and try and change any of the drop downs it brings back 0 results because it didn't reset the other options and can't find a Cadillac pinto

The single mass query, is that like creating a view and querying against it? We have something disgusting like 58,000 individual applications for our radiators. Would that be to much?

How would i store the results so i could run other queries against it? I think that would be a great help!

Thanks Man

[Craig328]

mossman65, on 29 May 2012 - 03:20 PM, said:

The larger problem is that i'm using

onchange="this.form.submit()"

. I'll choose my Year, Make, and model, but say i made a mistake on which model i wanted. If i change it to a different car it executes the form.submit again and makes the next drop down execute. Also say i have chosen all options and i have been supplied with my results. If i click my back button and try and change any of the drop downs it brings back 0 results because it didn't reset the other options and can't find a Cadillac pinto

Okay, that can be handled a little more creatively. Right now, you're submitting the entirety of your form and yeah, if you have Cadillac and Pinto selected the subsequent query finds nothing because the onchange event is submitting the entire form. You can keep the event handler but instead of submitting the form you can call a Javascript function that allows you to apply a little logic to things before you submit them. Like if you had Ford and Pinto selected and then went back and selected Cadillac, the Javascript function that fires for that select element can automagically reset all subsequent select fields (basically, switch from Pinto to unselected) and then submit the form.

Quote

The single mass query, is that like creating a view and querying against it? We have something disgusting like 58,000 individual applications for our radiators. Would that be to much?

How would i store the results so i could run other queries against it? I think that would be a great help!

Thanks Man

It is indeed kind of like that. What you could try is to pull everything from your MoparParts table with no where statements...basically, "select [columns you actually need to retrieve because '*' is wasteful] from MoparParts". The CFQUERY for that though adds in the cachedWithin attribute. Depending on how often and how you change the content in that table you could pull a one time query and cache it for however long you like. The first time you run it, it will take some time to populate the query result set but every subsequent time that query is run, as long as it's within the time you specify in the cachedWithin attribute, the server doesn't bother hitting the database - it just returns the cached query result set it has kept in memory.

Then against that query result set you'd run subsequent queries where you apply your various WHERE statements. You're using the original query result set as an in-memory table and using QofQ to pull what you want from that. It should be somewhat quicker than hitting the database directly. You'll need to use the CreateTimeSpan function to go along with the cachedWithin but you should see a performance boost from this slightly different way of doing it.

One thing to know: if the MoparParts table is updated or changed, your cached query won't know that and those changes won't be reflected until the cached query expires or unless you manually kill/expire it (I believe you can use CFCACHE to manually flush a cached query). It's just something to be aware of is all.

[mossman65]

Quote

Okay, that can be handled a little more creatively. Right now, you're submitting the entirety of your form and yeah, if you have Cadillac and Pinto selected the subsequent query finds nothing because the onchange event is submitting the entire form. You can keep the event handler but instead of submitting the form you can call a Javascript function that allows you to apply a little logic to things before you submit them. Like if you had Ford and Pinto selected and then went back and selected Cadillac, the Javascript function that fires for that select element can automagically reset all subsequent select fields (basically, switch from Pinto to unselected) and then submit the form.

Cool! What would i Google to find this majiriffic script?

Quote

It is indeed kind of like that. What you could try is to pull everything from your MoparParts table with no where statements...basically, "select [columns you actually need to retrieve because '*' is wasteful] from MoparParts". The CFQUERY for that though adds in the cachedWithin attribute. Depending on how often and how you change the content in that table you could pull a one time query and cache it for however long you like. The first time you run it, it will take some time to populate the query result set but every subsequent time that query is run, as long as it's within the time you specify in the cachedWithin attribute, the server doesn't bother hitting the database - it just returns the cached query result set it has kept in memory.

Then against that query result set you'd run subsequent queries where you apply your various WHERE statements. You're using the original query result set as an in-memory table and using QofQ to pull what you want from that. It should be somewhat quicker than hitting the database directly. You'll need to use the CreateTimeSpan function to go along with the cachedWithin but you should see a performance boost from this slightly different way of doing it.

I have used the cachedWithin: CachedWithin="#CreateTimeSpan(7,0,0,0)#" but i did it per selection/query so that didn't work out to well

If i understand this correctly... The cached query will be available for to run queries against. Will i name the mass query something so that i can query against it once its cached?

Thanks Craig



One thing to know: if the MoparParts table is updated or changed, your cached query won't know that and those changes won't be reflected until the cached query expires or unless you manually kill/expire it (I believe you can use CFCACHE to manually flush a cached query). It's just something to be aware of is all.
[/quote]