adding an if / cfswitch statement to a query missing peramiters

Report · May 04, 2010

Hello;

I'm trying to add a feature into a page I made. It has a lot going on, so I'll try and give you the basic function I'm trying to add.. and as little of the other logic as possible.

I want to add a dropdown menu to this page, I have 2 querys running it. one, runs the dropdown.. here is the code for that:

<CFQUERY name="cata" datasource="#APPLICATION.dataSource#" cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#">
select subName, subID
FROM merchSubCat
ORDER BY subName
</CFQUERY>

<form Name="category" method="get" Action="wallProduct.cfm?id=#subID#">
<select name="CategoryID" size="1" class="selectstyle" onChange="category.submit();">
     <option value=""> --Select a Category-- </option>
         <CFOUTPUT query= "cata">
         <option value="#subID#">#subName#</option>
         </CFOUTPUT>
     </select></form>

ok, simple enough. It takes the info from my db and makes a little menu out of it. click on one of the selections, and it will pass a url.id to the same page. This query is the main query that runs the page. I need it to serve up the first set of records by default, and if you select from the drop down, the according records will be brough up. here is my query to add this feature.

<cfquery name="getMerch" datasource="#APPLICATION.dataSource#">
SELECT merchID, MerchName, MerchDescription, MerchPrice, MYFile, subID, CategoryID
FROM Merchandise
WHERE
<cfif isDefined ("#url.id#")>
<cfswitch expression="#url.id#">
<cfcase value="1">subID = 1</cfcase>
<cfcase value="2">subID = 2</cfcase>
<cfcase value="3">subID = 3</cfcase>
</cfswitch>
<cfelse>
subID = 1
</cfif>

</cfquery>

When my page loads, there is no ID in the url, and my code is not liking this and throwing an erro as such:

Element ID is undefined in URL.


The error occurred in C:\Websites\187914kg3\magWall\wallProduct.cfm: line 19
17 : FROM Merchandise 18 : WHERE 19 : <cfif isDefined ("#url.id#")> 20 : <cfswitch expression="#url.id#"> 21 : <cfcase value="1">subID = 1</cfcase>

This is my problem, and I'm sure it's something silly I'm forgetting ehre. I've done this b4. How do I make this work so that by default, subID = 1 is shown when the page loads, and the others are shown when you use the dropdown menu.

I also spoke of other logic on this page. there is a next n button running this query, as well as a cfswitch to switch the order of the records, using the ORDERBY in my query. But I want to get this dropdown menu working before I add in all this other logic.

can anyone help me? I'm having a mental block on this problem.

thank you.

Report · May 04, 2010

Why don't you just do

where subid = url.id

Report · May 04, 2010

Problem One: IsDefined() expects as string, not a variable. Lose the # characters.

IsDefined("url.id")

Problem Two: IsDefined() may not be the best option. Your description made me think of the <cfparam...> tag that would allow you automaticaly create the url.id variable if it does not exist and assign it a default value.

Then the isDefined() function would not be required.

Report · May 04, 2010

You don't have an id URL parameter, it is CategoryID.

Try something like this:

<cfparam name="URL.CategoryID" default="1" type="any">
<cfquery name="getMerch" datasource="#APPLICATION.dataSource#">
SELECT merchID, MerchName, MerchDescription, MerchPrice, MYFile, subID, CategoryID
FROM Merchandise
WHERE subID = 
<cfqueryparam cfsqltype="cf_sql_numeric" value="#URL.CategoryID">
</cfquery>

Ken Ford

Report · May 04, 2010

Ken, yours works nice. I thought of the cfparam afte

r I posted, but left the default blank, so it threw an error.

Now my problem is an error with my dropdown menu. It says sudID is undefined in the form variables for this select. here is the

error, and code.. I tried a few different approaches on this and right now, nothig is fixing it.

My error:

Variable SUBID is undefined.


The error occurred in C:\Websites\187914kg3\magWall\wallProduct.cfm: line 89
87 : <table width="100%" border="0" cellspacing="0" cellpadding="0"> 88 : <tr> 89 : <td colspan="3" align="center" valign="top"><form Name="category" method="get" Action="wallProduct.cfm?ID=#subID#"> 90 : <select name="subID" size="1" class="selectstyle" onChange="category.submit();"> 91 : <option value=""> --Select a Category-- </option>

My code:

<CFQUERY name="cata" datasource="#APPLICATION.dataSource#">
select subName, subID
FROM merchSubCat
ORDER BY subName
</CFQUERY>

<form Name="category" method="get" Action="wallProduct.cfm?ID=#subID#">
<select name="subID" size="1" class="selectstyle" onChange="category.submit();">
     <option value=""> --Select a Category-- </option>
         <CFOUTPUT query= "cata">
         <option value="#subID#">#subName#</option>
         </CFOUTPUT>
     </select></form>

am I missing something here?

Report · May 04, 2010

cfsetNewbie wrote:
am I missing something here?

It would help greatly if you scoped all your variables. Are you trying to reference the url.subID variable of the form or the cata.subID from the query recordset? Since you do not specify I am not sure, and maybe neither is ColdFusion.

Report · May 04, 2010

didn't think about that.. how's this? I tried it, but it's still throwing an error, same one..

<cfif url.ID GT 0>
<CFQUERY name="cata" datasource="#APPLICATION.dataSource#">
SELECT subName, subID
FROM merchSubCat
ORDER BY subName
</CFQUERY>

and then my form code, same as I last posted.

Report · May 04, 2010

Well that really doesn't change anything. You are just now mixing some scoped variables with unscoped variables. I would have just added the scopes to the original code.

Speaking of which you have in error on line 89 which is a table cell (<TD>...). How does the error line relate to the query and form code you posted?

Report · May 05, 2010

I got it to work. I changed my scoped variables, and changed the select to a cfselect and not it runs nic

e. Thank you.

Adobe Community

adding an if / cfswitch statement to a query missing peramiters

Element ID is undefined in URL.

1 Correct answer

Variable SUBID is undefined.