Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

valuelist issue 2

Status
Not open for further replies.
GUJUm0deL

GUJUm0deL

Programmer
Jan 16, 2001
3,676
US
I am using valuelist to put all values in a query into a list, but for some reason I the list returns:

one ,two ,three ,four ,five ,etc...

Why is there a space then a comma? The space shouldn't be there...


____________________________________
Just Imagine.
 
Sort by date Sort by votes
that would be my guess.

if you're not going to have any other commas in your values you can do this

<cfset myList = valueList(query.column)>
<cfset myList = replaceNoCase(myList, " ,", ",")>

you could also loop over the query and make the list in the loop. All the while trimming the value.

<cfset myList = "">
<cfloop query = "myQuery">
<cfset myList = listAppend(trim(myQuery.Column))>
</cfloop>

good luck

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
Upvote 0 Downvote
r937, that was my guess also. I checked the dB (btw I am using SQL server) and the only fields I have there are badwords_id (PK) and badwords. BTW, the badwords is set as 'nchar' with 10 chars space. Do you think the extra space is caused by that?

bombboy, I already tried that idea be4 I posted here. Didn't work. The space still shows up. I even tried trim() and still nothing.

Oddly, if I use valuelist() with another query on the same page all is ok. Its only this query. I even recreated the whole table.

Thanks.


____________________________________
Just Imagine.
 
Upvote 0 Downvote
nchar is a fixed length field and will include spaces

use SELECT RTRIM(fieldname) in your query

or use nvarchar



rudy
SQL Consulting
 
Upvote 0 Downvote
rudy's solution will proably work. but, you tried my second suggestion and it still didn't work?

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
Upvote 0 Downvote
Hey guys, yup. I tried ALL known possible solutions. Everything from trim(), rtrim(), replace(), etc. I even recreated the table, changed the field type from nchar to char to varchar, and still nothing.

But I know the issue lies with the default charatcer space SQL Server uses. Like I said I use a value of 10 to indicate the length of the field be at least 10 chars. Interestingly enough I outputted the value onto the screen and it returned:
Code: 
[COLOR=red]
THIS IS USING VALUELIST().  THE CODE IS: <cfset badwordslist = valuelist(QUERY.COLUMNNAME)>
badword1, badword2[/color]
[COLOR=green]
THIS IS USING REPLACE().  THE CODE IS:<cfset badwordslist = replace(badwordslist," ",",","ALL")>
badword1,,,,,,,badword2[/color]

if you count the comma's in the second output you'll see that it uses 10 commas b/w each word.





____________________________________
Just Imagine.
 
Upvote 0 Downvote
Alright, here's the thing..

When you see

one ,two ,three, four

you're actually getting (in the source code)

Code: 
one     ,two     ,three   ,four

So what you need to do is use some sort of regex to crunch the spaces..... maybe something like

REReplace(string,"([ ,]*)","","ALL")

ALFII.com
---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.
 
Upvote 0 Downvote
r937, I tried your suggestion of placing the RTRIM in the query call, but I get this error:

Code: 
Error Occurred While Processing Request  
The ValueList() function has an invalid parameter: badwordscheck.BadWords.  
Parameter 1 of function ValueList which is now (badwordscheck.BadWords) must be pointing to a valid query name.  
  
The error occurred in C:\test2.cfm: line 83
 
81 : 	<cfset badwordslist = "">
82 : 
83 : 	  <cfset badwordslist = valuelist(badwordscheck.BadWords)>
84 : 	
85 :     <cfset badWordsstatus = 0>

--------------------------------------------------------------------------------
 
Please try the following: 
Check the ColdFusion documentation to verify that you are using the correct syntax. 
Search the Knowledge Base to find a solution to your problem.

And the query looks like:
Code: 
<cfquery name="badwordscheck" datasource="#default_ds#">
SELECT RTRIM(BadWords)
FROM BadWordsTable
</cfquery>


____________________________________
Just Imagine.
 
Upvote 0 Downvote
this works for me i tested it.

<cfset myList = "bob ,joe ,Steve ,Alex ,Roger ,travis ,mike">
<cfset myOtherList = "">
<cfloop list = "#mylist#" index = "i">
<cfset myOtherList = listAppend(myOtherList, trim(i))>
</cfloop>
<cfoutput>
#myOtherList#
</cfoutput>

output is: bob,joe,Steve,Alex,Roger,travis,mike

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
Upvote 0 Downvote
you need to give the column an alias

SELECT RTRIM(BadWords) as whoops

and then use

<cfset badwordslist = valuelist(badwordscheck.whoops)>


rudy
SQL Consulting
 
Upvote 0 Downvote
Your best bet, for filtering is to filter the actual spellings and let people report bad threads...

I mean... its too easy..

Code: 
A = /\, @   B = |3, l3  C = (
D - |)      e...?       F = |=
g...?       H = |-|  
l (ell) and 1 (one) and I (eye) and | (pipe) look enough alike
j...?       k...|<      l...(see I)...|_
m...|\/|    n...|\|

And the list goes on.. and its funny how far people will go and that by the point they're passing the filters they've got a jumple of slashes and pipes and dollar and @ signs..

ALFII.com
---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.
 
Upvote 0 Downvote
so did you get it fixed?

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
Upvote 0 Downvote
Well I used r937's suggestion and the valuelist() now displays the words in a correct way, HOWEVER the scrubbing doesnt work right anymore.

Its scrubs everything! But if I place those same words in a self-inflicted comma delimited list the scrubbing works perfect.

I don't know why the valuelist() is givig me such a hard time.



____________________________________
Just Imagine.
 
Upvote 0 Downvote
This is the general markup of the code:
Code: 
QUERY:
<cfquery name="badwordscheck" datasource="#default_ds#">
SELECT RTRIM(BadWords) as BD
FROM BadWordsTable
</cfquery>
Code: 
<cfset badwordslist = valuelist(badwordscheck.BD)>
<cfset badWordsstatus = 0>

<cfoutput>
  <cfloop index="FAList" from="1340" to="1343">
    <cfset FAList = listappend(FAList,trim(FormAnswer))>
      <cfset FAList = Replace(FAList," ",",","ALL")>
	<cfloop list="#FAList#" index="i" delimiters="|,">
	  <cfif findnocase(i,#badwordslist#)>
	    <cfset badWordsstatus = badWordsstatus + 1>
	  </cfif>			
	</cfloop>
      <cfset FAList = "">
  </cfloop>	
</cfoutput>
...
,,,
<cfif badWordsstatus EQ 0>
...
...
</cfif>

Now that works just great only when I hardcode the list, when I pull the values from the dB and use valuelist to create a list the code scrubs everything.


____________________________________
Just Imagine.
 
Upvote 0 Downvote
i don't understand what you're doing here --

<cfoutput>
<cfloop index="FAList" from="1340" to="1343">
<cfset FAList = listappend(FAList, ...

you start a loop with FAList as an index

then inside that loop you set FAList to a list

???




rudy
SQL Consulting
 
Upvote 0 Downvote
if "FAList" is your index to at some point contain the values of 1340, 1341, 1342, and 1344, why are you also using FAList to contain the value of a list in the same loop?

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
Upvote 0 Downvote
HA rudy

jinx!

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
Upvote 0 Downvote
What I am doing is:
Code: 
LOOP FROM 1340-1343 WITH AN INDEX OF FALIST
<cfloop index="FAList" from="1340" to="1343">
APPEND VALUES TO FALIST TO FORM A LIST
  <cfset FAList = listappend(FAList,trim(FormAnswer))>
REPLACE ALL SPACES IN THE LIST WITH COMMA'S (SO IF ONE RECORD HAD 'ONE TWO THREE' THEN THE REPLACE WOULD MAKE ITS ONE,TWO,THREE)
  <cfset FAList = Replace(FAList," ",",","ALL")>
LOOP THROUGH FALIST
    <cfloop list="#FAList#" index="i" delimiters="|,">
USING FINDNOCASE I SEARCH IN I FOR WORDS FROM BADWORDSLIST
      <cfif findnocase(i,#badwordslist#)>
IF BADWORDS FOUND INCREMENT THAT VALUE BY 1
        <cfset badWordsstatus = badWordsstatus + 1>
      </cfif>            
    </cfloop>
CLEAR THE LIST AND RESTART THE PROCESS...THIS IS BECAUSE I HAVE OTHER CODING ABOVE IT THAT GOES THROUGH ANOTHER QUERY
  <cfset FAList = "">
</cfloop>
I know this may seem odd, but this works great. If I replace the valuelist() with a hard-coded list, no issues.


____________________________________
Just Imagine.
 
Upvote 0 Downvote
I know this may seem odd, but this works great"

no, it doesn't

using the same variable for two different things like you are doing is one day going to bite you in the ...

uh oh

rudy
SQL Consulting
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Luvsql
  • Locked
  • Question
Hidden Date Field Issue Not Updating Properly
Replies
3
Views
251
darryncooke
darryncooke
ChitradeviSelvam
  • Locked
  • Question
Facing issues with Coldfusion server-it is going down at peak usage time
Replies
1
Views
232
Wullie
Wullie
Klyphq
  • Locked
  • Question
cfqueryparam syntax issue
Replies
7
Views
263
cfSearching
cfSearching
forevermoon
  • Locked
  • Question
&lt;cflock&gt;issue
Replies
0
Views
137
forevermoon
forevermoon
redoakhg
  • Locked
  • Question
cfpdf merge issue
Replies
1
Views
132
LyndonOHRC
LyndonOHRC

Part and Inventory Search

Sponsor

Back
Top