Professional Web Applications Themes

Whitespace Problems - Macromedia ColdFusion

I have a form with a multi select box, when you select multiple objects it puts them in a comma delmited list. For example if you select Sheep, Cat, Mouse from the multi select box form named animals #animals# = Sheep,Cat,Mouse I want to use the value of animals in the where clause of an sql statement. select * from table where table.animal in (#animals#) The problem is that table.animal is a varchar field so i need ticks ( ' ) around the values like this: 'Sheep','Cat','Mouse' The hack I wrote to do this works but when I put the ...

  1. #1

    Default Whitespace Problems

    I have a form with a multi select box, when you select multiple objects it puts
    them in a comma delmited list. For example if you select Sheep, Cat, Mouse from
    the multi select box form named animals #animals# = Sheep,Cat,Mouse I want to
    use the value of animals in the where clause of an sql statement.
    select * from table
    where table.animal in (#animals#)

    The problem is that table.animal is a varchar field so i need ticks ( ' )
    around the values like this: 'Sheep','Cat','Mouse'
    The hack I wrote to do this works but when I put the new value back into the
    query it has all sort of spaces and carriage returns in it. for example it will
    look similar to this:
    '
    Sheep


    ',' Cat


    ,' Mouse

    '

    I have tried all sorts of things such as replacing all spaces and carriage
    returns with nothing and using trim. Ill post my code here to see if anyone has
    any ideas to do this the correct way.

    <cfsilen>
    <cfsavecontent
    variable = "animal_list">
    <cfoutput>
    <CFSET an_list = "#form.animals#">
    ('
    <cfloop index="i" from="1" to="#ListLen(prod_list)#">
    <cfoutput>#ListGetAt(an_list, i, "," )#</cfoutput>
    <cfif #i# LT #ListLen(and_list)#>
    ', '
    </cfif>
    </cfloop>
    ')
    </cfoutput>
    </cfsavecontent>
    </cfsilent>

    JakeFlynn Guest

  2. #2

    Default Re: Whitespace Problems

    Yes #animals# is technicaly a _list_, but it is also just a _string_.
    Therefore, the following should work:

    where table.animal in ('#ReReplace(animals, ",", "','", "ALL")#')

    This will turn

    where table.animal in (Sheep,Cat,Mouse)

    into

    where table.animal in ('Sheep','Cat','Mouse')

    cf_menace Guest

  3. #3

    Default Re: Whitespace Problems

    cf_menace,
    that did it! thank you for your reply
    JakeFlynn Guest

  4. #4

    Default Re: Whitespace Problems

    Look into the PreserveSingleQuotes CF function :)
    moneybagsxp Guest

Similar Threads

  1. CFC getter and whitespace
    By BSterner in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: July 16th, 12:52 AM
  2. Preserve whitespace
    By Mike in forum ASP.NET Web Services
    Replies: 1
    Last Post: April 21st, 10:44 AM
  3. cannot get rid of whitespace
    By AkronNick in forum Macromedia ColdFusion
    Replies: 10
    Last Post: April 11th, 06:25 PM
  4. get rid of whitespace around pipes??
    By Jeff 'Japhy' Pinyan in forum PERL Beginners
    Replies: 3
    Last Post: December 17th, 04:26 PM
  5. Trimming Whitespace
    By Sean Levesley in forum Macromedia Director Basics
    Replies: 3
    Last Post: August 22nd, 10:17 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