Professional Web Applications Themes

sql in operator with cfqueryPARAM - Coldfusion - Advanced Techniques

here is my query select distinct option_type, description, id from option_types where id NOT IN(<cfqueryPARAM value = "#mikeslist#" CFSQLType = "CF_SQL_varchar"> ) order by option_type #mikeslist# contains '12','13','23','5' I don't get an error but I don't get the right results either(I coded it statically and it works fine) thank you so much in advance L...

  1. #1

    Default sql in operator with cfqueryPARAM

    here is my query

    select distinct option_type, description, id
    from option_types
    where id NOT IN(<cfqueryPARAM value = "#mikeslist#"
    CFSQLType = "CF_SQL_varchar"> )
    order by option_type

    #mikeslist# contains '12','13','23','5'

    I don't get an error but I don't get the right results either(I coded it
    statically and it works fine)

    thank you so much in advance

    L

    laurus1 Guest

  2. #2

    Default Re: sql in operator with cfqueryPARAM

    Is id an integer or varchar data type. If it's an integer you need to remove the single quotes from each element of the list

    So that #mikeslist# contains 12,13,23,5

    Ken
    The ScareCrow Guest

  3. #3

    Default Re: sql in operator with cfqueryPARAM

    The contents of the unqualified (mylist) list are as follows: 12,13,23,5
    The contents of the mikeslist are as follows: '12','13','23','5'

    using #mylist# it finds the first value '12'.
    using #mikeslist# it finds none at all.

    so that didn't quite do it..

    anymore thoughts?

    Thanks




    laurus1 Guest

  4. #4

    Default Re: sql in operator with cfqueryPARAM

    by the way

    ID is an int(10) unsigned MySQL
    laurus1 Guest

  5. #5

    Default Re: sql in operator with cfqueryPARAM

    As the id is an int then #mikeslist# will not work, it has to be a list of
    numbers

    Can you turn debuggin on, so that you can see the actual query being sent ?

    I'm assuming the following is happening, when using cfqueryparam with a type
    of varchar it is automatically inserting a single quote. So it would be as
    follows

    select distinct option_type, description, id
    from option_types
    where id NOT IN('12,13,23,5')
    order by option_type

    Notice the single quotes at each end of the list, it should be like

    select distinct option_type, description, id
    from option_types
    where id NOT IN(12,13,23,5)
    order by option_type

    If this is the case, then you will not be able to use cfqueryparam, unless you
    do the following

    select distinct option_type, description, id
    from option_types
    where id NOT IN(
    <cfqueryparam value="12" cfsqltype="CF_SQL_INTEGER">
    ,
    <cfqueryparam value="13" cfsqltype="CF_SQL_INTEGER">
    ,
    <cfqueryparam value="23" cfsqltype="CF_SQL_INTEGER">
    ,
    <cfqueryparam value="5" cfsqltype="CF_SQL_INTEGER">
    )
    order by option_type

    Ken

    The ScareCrow Guest

  6. #6

    Default Re: sql in operator with cfqueryPARAM

    This helped tons!!! thanks!
    laurus1 Guest

Similar Threads

  1. Problem using cfqueryparam
    By Jason Lim in forum Coldfusion Database Access
    Replies: 2
    Last Post: October 18th, 05:55 PM
  2. cfqueryparam
    By SupDave in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: June 14th, 01:15 AM
  3. CFQUERYPARAM Error
    By wbartnick10 in forum Coldfusion Database Access
    Replies: 8
    Last Post: May 17th, 02:57 PM
  4. cfqueryparam question
    By Latino in forum Macromedia ColdFusion
    Replies: 3
    Last Post: March 16th, 02:29 AM
  5. cfqueryPARAM problem
    By man jackals in forum Macromedia ColdFusion
    Replies: 1
    Last Post: February 27th, 07:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139