Solved

what am i doing wrong in this code?

Posted on 2003-11-17
9
269 Views
Last Modified: 2013-12-24
I have a form fields as following:

<select name="Mkt_area">
<option value="" selected>Select a Marketing Area</option>
<option value="All">All Marketing Areas</option>
<option value="Arizona,02,0">Arizona</option>
<option value="Arkansas,03,0">Arkansas</option>
<option value="Central Georgia,10,2">Central Georgia</option>
<option value="East Kentucky,16,2">East Kentucky</option>
<option value="East North Carolina,32,3">East North       
</select>


<select name="Member_AgeRange" size="1" id="Member_AgeRange">
 <option value="" selected>Select an Age Range</option>
 <option value="all_age">All Age Groups</option>
 <option value="no_age">No Age Groups</option>
<option value="25 - 34">25 - 34</option>
    <option value="35 - 44">35 - 44</option>
   <option value="45 - 54">45 - 54</option>
  </select>


<select name="Member_Status" size="1" id="Member_Status">
 <option value="" selected>Select Member Status</option>
  <option value="all_mem">Both</option>
<option value="Member">Member</option>
  <option value="Non-Member">Non-Member</option>
  </select>

then I have this code to process the data..(ofcourse i hav a submit button)


<cfif IsDefined ('form.submit')>
        
        <!--- Mkt_Area Info collection --->
              <cfif #form.Mkt_Area# NEQ "ALL">      
                  <cfset states = #form.Mkt_Area#>
                  <cfset state = #ListGetAt(states,2)#>
                  <cfset div = #ListGetAt(states,3)#>
                  <cfquery name="getzip" datasource="woodmenzip">
                        select ZipCode from ZipCode
                        where State = '#state#' and Division = '#div#'
                  </cfquery>
                  
            <cfelseif #form.Mkt_Area# EQ "ALL">
                  <cfquery name="getzip" datasource="woodmenzip">
                        select ZipCode from ZipCode
                  </cfquery>
            </cfif>
            
            
            
             <!--- Mkt_Area Info collection --->
                  
      <!--- Age Range Info collection --->
            
            <cfif (#form.Member_AgeRange# NEQ "all_age")      OR (#form.Member_AgeRange# NEQ "no_age")>
                  <cfset age = #form.Member_AgeRange#>
                  <cfquery name="getmem" datasource="gchart">
                        select * from GC_members
                        where Member_AgeRange = '#form.Member_AgeRange#' and flag = 0 or flag = 1
                  </cfquery>
            <cfelseif #form.Member_AgeRange# EQ "all_age">
                  <cfquery name="getmem" datasource="gchart">
                        select * from GC_members
                        where flag = 0 or flag 1
                  </cfquery>
            <cfelseif #form.Member_AgeRange# EQ "no_age">
                  <cfquery name="getmem" datasource="gchart">
                        select * from GC_members
                        where Member_AgeRange = "" and flag = 1 or flag = 0
                        
                  </cfquery>
            </cfif>
            
            <!--- Age Range Info collection --->
            
            <cfif #form.Member_Status# NEQ "all_mem">
                  <cfset stat = #form.Member_Status#>
                  <cfquery name="getstat" datasource="gchart">
                        select * from GC_members
                        where Member_Status = '#form.Member_Status#' and flag = 0 or flag = 1
                  </cfquery>
            <cfelseif #form.Member_Status# EQ "all_mem">
                  <cfquery name="getstat" datasource="gchart">
                        select * from GC_members
                        where flag = 0 or flag = 1
                  </cfquery>
            </cfif>
            <cfquery name="getall" dbtype="query">
                  select
                        getmem.Member_FName as Member_FName,
                        getmem.Member_LName as Member_LName,
                        getmem.Member_Add1 as Member_Add1,
                        getmem.Member_City as Member_City,
                        getmem.Member_State      as Member_State,
                        getmem.Member_Zip as Member_Zip,
                        getmem.Member_phone as Member_phone
                  from getzip,getmem,getstat
                  where getzip.ZipCode = getmem.Member_Zip and
                          getmem.Member_Zip = getstat.Member_Zip and
                          getzip.ZipCode = getstat.Member_Zip
            </cfquery>

So what I'm trying to do is to have a bunch of zipcodes from the first query, and then a bunch of members from the second and third query. Compare the zip codes and then display the members who fall in the criteria. I'm not getting any error msgs. I dont know what to do here. Pls help...
0
Comment
Question by:adonis1976
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 11

Author Comment

by:adonis1976
ID: 9766843
i'm trying to output these

<cfoutput query="getall">
              <tr>
            <td>#Member_FName# #Member_LName#</td>
            <td>#Member_Add1#</td>
            <td>#Member_City#</td>
            <td>#Member_State# #Member_Zip#</td>
            <td>#Member_Phone#</td>
          </tr>
              </cfoutput>


thanks in advance
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9767292
try these changes

        <cfquery name="getzip" datasource="woodmenzip">
                    select ZipCode from ZipCode
                              <cfif #form.Mkt_Area# NEQ "ALL">    
                    where State = '#ListGetAt(states,2)#' and Division = '#ListGetAt(states,3)#'
                              </cfif>
               </cfquery>

       
               <cfquery name="getmem" datasource="gchart">
                    select * from GC_members
                             <cfif #form.Member_AgeRange# EQ "no_age">
                                    where Member_AgeRange = ""
                              <cfelseif #form.Member_AgeRange# NEQ "all_age">
                                     where Member_AgeRange = '#form.Member_AgeRange#'
                              </cfif>
               </cfquery>
         

         
             
               <cfquery name="getstat" datasource="gchart">
                    select * from GC_members
                              <cfif #form.Member_Status# NEQ "all_mem">
                    where Member_Status = '#form.Member_Status#'
                              </cfif>
               </cfquery>

          <cfquery name="getall" dbtype="query">
               select
                    getmem.Member_FName as Member_FName,
                    getmem.Member_LName as Member_LName,
                    getmem.Member_Add1 as Member_Add1,
                    getmem.Member_City as Member_City,
                    getmem.Member_State     as Member_State,
                    getmem.Member_Zip as Member_Zip,
                    getmem.Member_phone as Member_phone
                           from getzip left outer join getmem on getzip.ZipCode = getmem.Member_Zip left outer join getzip.ZipCode = getstat.Member_Zip
                           where getzip.ZipCode in (select ZipCode
                                                                   from ZipCode
                                                                   <cfif #form.Mkt_Area# NEQ "ALL">    
                                                         where State = '#ListGetAt(states,2)#' and Division = '#ListGetAt(states,3)#'
                                                                   </cfif>)
                     
          </cfquery>
0
 
LVL 11

Author Comment

by:adonis1976
ID: 9768073
thanks for your input jester..

the code you posted doesnt work...it doesnt work for some condition.. or else i get the error

unknown exception condition

PCodeRuntimeContextImp::executeSQLTagCFQuery::endTag

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (224:11) to (224:48).
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9768984
ok first tell me ...

do all the 3 tables being queried  "getzip,getmem,getstat" have relevant matching records ???

i mean do u want us to help u only with ur last query ??? just asking so we know - where to concentrate !
0
Free camera licenses with purchase of My Cloud NAS

Milestone Arcus software is compatible with thousands of industry-leading cameras for added flexibility. Upon installation on your My Cloud NAS, you will receive two (2) camera licenses already enabled in the software. And for a limited time, get additional camera licenses FREE.

 
LVL 11

Accepted Solution

by:
hart earned 500 total points
ID: 9769198
why are two datasources used woodmenzip and gchart..
The tables are not in a single database ??

if they are not in the same database then this should do the trick

<CFIF CompareNoCase(form.Mkt_Area,'All') NEQ 0>            
     <CFQUERY name="getzip" datasource="woodmenzip">
      select       ZipCode from ZipCode
      where       State       = '#ListGetAt(form.Mkt_Area,2)#'
            and Division = '#ListGetAt(form.Mkt_Area,3)#'
     </CFQUERY>
<CFELSEIF CompareNoCase(form.Mkt_Area,'All') EQ 0>
     <CFQUERY name="getzip" datasource="woodmenzip">
      select ZipCode from ZipCode
      </CFQUERY>
</CFIF>
<cfquery name="getall" datasource="gchart">
    select * from GC_members
    where (flag = 0 or flag = 1)
      and Member_Zip in (#ValueList(getzip.zipcode)#)
      <CFIF CompareNoCase(form.Member_AgeRange,'all_age') NEQ 0 And CompareNoCase(form.Member_AgeRange,'no_age') NEQ 0>
      and Member_AgeRange = '#form.Member_AgeRange#'
      <CFELSEIF CompareNoCase(form.Member_AgeRange,'no_age') EQ 0>
      and Member_AgeRange = ''
      </CFIF>
      <CFIF CompareNoCase(form.Member_Status,'all_mem') NEQ 0>
      and Member_Status = '#form.Member_Status#'
      </CFIF>      
</cfquery>
------------------------------------------------------------------------------------------------------
else if they are in a single database then a single query should work..

<cfquery name="getall" datasource="gchart">
select       GC.*,ZC.ZipCode
      from       GC_members GC,
                  ZipCode ZC                  
      where       (GC.flag = 0 or GC.flag = 1)                  
            <CFIF CompareNoCase(form.Mkt_Area,'All') NEQ 0>
            and ZC.State = '#ListGetAt(form.Mkt_Area,2)#'
            and ZC.Division = '#ListGetAt(form.Mkt_Area,3)#'      
            </CFIF>
            <CFIF CompareNoCase(form.Member_AgeRange,'all_age') NEQ 0 And CompareNoCase(form.Member_AgeRange,'no_age') NEQ 0>
            and Member_AgeRange = '#form.Member_AgeRange#'
            <CFELSEIF CompareNoCase(form.Member_AgeRange,'no_age') EQ 0>
            and Member_AgeRange = ''
            </CFIF>
            <CFIF CompareNoCase(form.Member_Status,'all_mem') NEQ 0>
            and Member_Status = '#form.Member_Status#'
            </CFIF>
            and GC.Member_Zip = ZC.ZipCode
</cfquery>

Regards
Hart
0
 
LVL 11

Expert Comment

by:hart
ID: 9769215
also i hope all ur fields are of type text/char/varchar etc..

because u had used ' in each and every comparison
and try to use <cfqueryparem, it is a better oding practice

for numeric datatype
<CFQUERYPARAM CFSQLTYPE="cf_sql_numeric" VALUE="#ur variable name#">

for text data type
<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#ur variable name#">

usage for eg:
and Member_Status = <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#form.Member_Status#">


Regards
Hart

0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9770541
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (224:11) to (224:48).


which query is on this line?

0
 
LVL 11

Author Comment

by:adonis1976
ID: 9771085
Hart:

Thanks a million for your help. The first code worked like a champ. Yeah they are in different databases. The DB is messed up 'cos I didn't create it..hehe.. one more question.. when the user selects "ALL"(all marketing areas), the query actually goes through something like 99000 records and the webserver times out... is there a way to optimize this?

thanks for your help again.
0
 
LVL 11

Expert Comment

by:hart
ID: 9776362
:-)

create appropriate indexes on columns so that the retrieval is faster.

also at atime get a max of 200 records.. i don't think u will be showing the complete 99,000 records  at a time to the users.

so keep a limit to show or create a paging functionality.
let the user click next and previous to view records.

also optimize ur database if possible so that the retrieval doesn't take so much time..
also just to let u know in an in statement the maximum number arguments allowed is 1000.
so if there are more than 1000 records[zip codes] then the in clause will give an error.
but then i can't optimize the query because they are in both different databases. :-(

Regards
Hart
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
URL redirect 4 51
retrieving files from old server once DNS has changed 10 70
Website URL redirection 10 61
cookies analysis tools 2 67
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 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

9 Experts available now in Live!

Get 1:1 Help Now