8 Replies Latest reply on May 5, 2010 5:56 AM by cfsetNewbie

    adding an if / cfswitch statement to a query missing peramiters

    cfsetNewbie Level 1

      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.

        • 1. Re: adding an if / cfswitch statement to a query missing peramiters
          Dan Bracuk Level 5

          Why don't you just do

          where subid = url.id

          • 2. Re: adding an if / cfswitch statement to a query missing peramiters
            ilssac Level 5

            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.


            <cfparam name="url.id" default="1">

             

            Then the isDefined() function would not be required.

            • 3. Re: adding an if / cfswitch statement to a query missing peramiters
              Ken Ford - Fordwebs, LLC

              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

              • 4. Re: adding an if / cfswitch statement to a query missing peramiters
                cfsetNewbie Level 1

                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?

                • 5. Re: adding an if / cfswitch statement to a query missing peramiters
                  ilssac Level 5

                  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.

                  • 6. Re: adding an if / cfswitch statement to a query missing peramiters
                    cfsetNewbie Level 1

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

                     

                    <cfparam name="url.id" default="1" />
                    <cfparam name="url.sort" default="1" />
                    <cfparam name="subName" default="">

                     

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


                    <cfif cata.RecordCount EQ 1>
                    <cfset subID = cata.subID>
                    <cfset subName = cata.subName>
                    </cfif>
                    </cfif>

                     

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

                    • 7. Re: adding an if / cfswitch statement to a query missing peramiters
                      ilssac Level 5

                      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?

                      • 8. Re: adding an if / cfswitch statement to a query missing peramiters
                        cfsetNewbie Level 1

                        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.