Solved

List Issues

Posted on 2011-09-28
8
208 Views
Last Modified: 2012-05-12
Maybe i worked a bit i do not know

but last 1 hr, i am stuck @ this point

My IDs come as:

123456,123454,989789

Now i want to make them as:

NewID = '123456','123454','989789'

Me using listQualify function to add a single quotes to them and passing as:

'ListChangeDelim(NewID,"|")'

Either i remove qutes or apply quotes around listchangedelim it is throuwing an error when it interact with the database with some invalid list

0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 13

Accepted Solution

by:
ansudhindra earned 500 total points
ID: 36716496
try it like...
 
<cfset ids="123456,123454,989789" />
<cfset ids="'"&ids&"'" />
<cfset ids=replace(ids,",","','","all") />
<cfoutput>#ids#</cfoutput>

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36716739
hi,

you using ms sql or mysql.

if you using ms sql then i have better suggestion, because even after putting single quote, you need to create dynamic query which is too bit complex for debugging.

- bhavesh
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36716762


<cfset theList = "123456,123454,989789">


 
'#listChangeDelims(theList,"','")#'

Open in new window



But the bigger question is why?   Since all the values are numeric, you don't need to put them in quotes when used in your query...   it is ok to do this....    WHERE THE_ID in (1,2,3,4,5)

0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
LVL 39

Expert Comment

by:gdemaria
ID: 36716775
> Either i remove qutes or apply quotes around listchangedelim it is throuwing an error when it interact with the database with some invalid list


Please show the code..
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 36717001
using the above first solution, i am getting this in query which is throwing an error

WHERE (va.PID IN (238413,238414,238413a)
    OR va.ID IN (''238413'',''238414'',''238413a''))
0
 
LVL 16

Author Closing Comment

by:Gurpreet Singh Randhawa
ID: 36717083
Solved using replaceList
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36717091
Hi,

This post could be out of way to Question still I'm using this one....so sharing...

What we do....

create below function in database.

and calling this way.

va.PID IN (select items from dbo.FN_GENERATETABLE('238413,238414,238413a',','))
GO
CREATE FUNCTION [dbo].[FN_GENERATETABLE]( @STRING VARCHAR(8000),@DELIMITER CHAR(1) )
        RETURNS @RESULTS TABLE (ITEMS VARCHAR(8000))
AS


BEGIN

        --IF @STRING IS NULL OR @STRING='' RETURN
    SET @STRING =       LTRIM(RTRIM( @STRING ))
        
    DECLARE @INDEX INT
        DECLARE @SLICE VARCHAR(8000)
        DECLARE @SLICEPER VARCHAR(8000)
    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    --     ERO FIRST TIME IN LOOP
    SELECT @INDEX = 1 
    
        WHILE @INDEX !=0
        BEGIN   
                -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
                SELECT @INDEX = CHARINDEX(@DELIMITER,@STRING)
                -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
                IF @INDEX !=0
                        SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
                ELSE
                        SELECT @SLICE = @STRING
                                
                -- PUT THE ITEM INTO THE RESULTS SET
                INSERT INTO @RESULTS(ITEMS) VALUES(@SLICE)
                -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
                SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
                        
                -- BREAK OUT IF WE ARE DONE
                IF LEN(@STRING) = 0 BREAK
    END

    RETURN
END

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36717098

You code is showing duplicate single quotes, you only want one single quote

WHERE (va.PID IN (238413,238414,238413a) 
    OR va.ID IN (''238413'',''238414'',''238413a''))

Open in new window



You can remove ALL single quotes, as I said, these are numbers.

WHERE (va.PID IN (238413,238414,238413a)
          OR va.ID IN (238413,238414,238413a)
           )
0

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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