Skip to main content
J
jlig
Inspiring
April 12, 2011
Question

Help With WHERE Clause on my Detail page..

  • April 12, 2011
  • 3 replies
  • 1112 views

I have the a working detail page that displays multiple Address records based on URL Parameter ID's (1825, 1824,
1823, 1822, 1821): (This is working fine)
-----------------------------------------------
http://192.168.1.77/map_sel.cfm?wkRptID=1826&wkRptID_1=1825&wkRptID_2=1824&wkRpt
ID_3=1823&wkRptID_4=1822&wkRptID_5=1821 (this is the URL to display my "selected" records from a previous main page)


- But I cannot get my mapping query (on the same detail page) to properly "Reference" those URL ID values in my WHERE statement?


- How do I fix my WHERE clause below to correctly filter by these URL ID's only?


- My current mapping query is listed below, but it only outputs the First record (or only one ID)?
-----------------------------------------------------------
<cfparam name="URL.wkRptID" default="1">
<cfquery name="rsMapCust" datasource="salesPipeline">
SELECT wkRptID, wkRptCustName, wkRptBusAdd, wkRptCustCity, wkRptBusSt,
wkRptBusZip
FROM tblWeeklyRpt
WHERE wkRptID IN (<cfqueryparam value="#URL.wkRptID#" cfsqltype="cf_sql_numeric"> )
</cfquery>


-----------------------------------------------------------


I have a Google map below the detail records on my page, and I'm trying to map just those records only.

- Here is a picture of my detail page: http://cerberus.clearwave.com/jerry/MapSelected.jpg

    This topic has been closed for replies.

    3 replies

    D
    Dan_Bracuk
    Inspiring
    April 12, 2011

    cfqueryparam has a list attribute.

    J
    jligAuthor
    Inspiring
    April 12, 2011

    Dan, I'm using the List attribute as in:

    WHERE
    wkRptID IN (
    <cfqueryparam
    value="#URL.wkRptID#"
    cfsqltype="cf_sql_varchar"
    list="true"
    />
    )

    but still only getting the first ID..?

    How would i go about parsing the ID's in the URL first, then adding them to the WHERE..? (Note: the qty of ID's will vary)

    D
    Dan_Bracuk
    Inspiring
    April 12, 2011

    It is an Integer in the database, I have tried numeric, varchar, integer, etc, but no change..


    Use integer.

    When you look at your debugging info, what does it show for the value of the query param?

    A
    Amiya_Padhi
    Participating Frequently
    April 12, 2011

    Is there any specific reason that you are sending the selected values i

    n the URL scope? Can not we do form a list from the

    selected values and in the action page where we have the query we can write like

    <cfqueryparam value="#FORM.wkRptIDList#" cfsqltype="cf_sql_numeric" list="true">

    Please let me know exactly what you need to do, so that I may give you some better suggestion.

    Thanks

    J
    jligAuthor
    Inspiring
    April 12, 2011

    I can't change the "Sending Page" because it uses custom "Filtering & Sorting" to drill down to the addresses..

    Once the User has "Checked" the records to be mapped, they click the Map button, and it takes them to this Detail page (with the ID's in the URL)

    Now that you mention it, the Detail form does have all of the Form fields/wkRptID's (for each of the addresses) on the page.. but how would I iterate thru the form data of each record, and grab the wkRptID's and add them to the WHERE statement..?

    Either way, once I have the Query correct, it is then fed to my Google Map to plot the addresses..

    J
    JMF3
    Participating Frequently
    April 12, 2011

    You need to specify that the value is a 'list' in the CFQUERYPARAM:

    WHERE wkRptID IN ( )

    J
    jligAuthor
    Inspiring
    April 12, 2011

    BTW, I'm using CFMX7..

    I tried the following, but it still only gives me the first record?

    <cfparam name="URL.wkRptID" default="1">
    <cfquery name="rsMapCust" datasource="salesPipeline">
    SELECT wkRptID, wkRptCustName, wkRptBusAdd, wkRptCustCity, wkRptBusSt, wkRptBusZip
    FROM tblWeeklyRpt

    WHERE
    wkRptID IN (
    <cfqueryparam
    value="#URL.wkRptID#"
    cfsqltype="cf_sql_varchar"
    list="true"
    />
    )

    </cfquery>

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices