Posts by arrgh

I changed cfsqltype="cf_sql_timestamp" to cfsqltype="cf_sql_date"

Shouldn't make a difference. Not unless you're storing times too, not just dates. Are you? Also, what db type?

It will not output if the selectedDate = dayleft.

Let's do some debugging. Grab all records for resident #193. Can you dump the dates only. Then post the results. So we can see why they're not matching?

<cfquery name="checkResident" ...>
SELECT dayleft , dayreturned 
FROM hospital
WHERE residentID = 193
</cfquery>

Debugging stuff <br>
selectedDate = <cfdump var="#selectedDate#">
<cfdump var="#checkResident#">

Then all you'd need is this query.

ie
<cfset selectedDate = "07/11/2011">

<cfquery name="checkResident" ...>
SELECT *
FROM   hospital
<!--- be sure to use cfqueryparam if resident id is a variable --->
WHERE  residentID = 193
AND    dayleft <= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_timestamp">
AND    dayreturned >= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_timestamp">
</cfquery>

<!--- if a matching record was found .. --->
<cfif checkResident.recordCount gt 0>
  <cfoutput>resident was in the hospital on #selectedDate#</cfoutput>
</cfif>

Or you could generate a list of all residents in the hospital on date X by eliminating the residentID = 193 clause. Then output the query results.

<cfoutput query="checkResident">
    ... show names ...
</cfoutput>

I think so. Can you also post a few examples? Those are better than a 1000 words of description. Something like this

theSelectedDate => 08/30/2011

Sample Data
recordID, dayLeft, dayreturned,
#1, 08/01/2011, NULL
#2, 08/10/2011, 08/16/2011
#3, 08/28/2011, 09/05/2011
....

Which records should the query return and why?

It's not going to be nearly that complicated.

What's your query used for in plain english? ie Is it a reservation system and you're trying to find out what's booked?

Neither of these seemed to work

Can you give an an example of all 3 dates and what you expect to happen?

When I do this in MSAccess sql view it works but doesn't work on cfm page.

(Yeah, that's special "Access" syntax. It may work with some drivers, or it may not.) But the correct way to pass dates in cfquery is with cfqueryparam.

WHERE Stuff.RegDate = <cfqueryparam value="5/1/2004" cfsqltype="cf_sql_timestamp">

Please help I have also tried shifting the date formats.

That only changes the user display. It doesn't make any difference to your query.

Huh? In one breadth you preach protection against sql injection, and in the next you recommend a function that encourages sql injection ;-)

- DO use cfqueryparam for sql injection protection
- DO NOT use perservesinglequotes, it risks sql injection

If you have data that has single of double quotes then you can use the perservesinglequotes() function.

It does nothing for double quotes. Only single quotes.

This has nothing to do with CF. It is a pure SQL question ;)

AND MoveOutDate = 'NULL'

NULL isn't a string. It is a special system value. You can't use equals "=" either. To find nulls you must use

WHERE  ColumnName IS NULL

you're welcome.

Hi - Thanks for everyones help. I did resolve my problem. Using the example from adobe cfdeveloper pointed out.
Complete Total user error. The Adobe documentation is correct.

Oh, I believed cfpdfform would work. It was the talk about cfdocument and forms that had me scratching my head ;-) Glad it's resolved.

It's caused by the default YUI settings (javascript lib). It only returns stuff that start with whatever you typed. See the bottom comments for how to fix it.
http://www.coldfusionjedi.com/index.cfm/2007/7/23/A-Gotcha-with-Auto-Suggestions-in-ColdFusion-8

Yes, I've tried the cfdocument tag, and it is creating pdf forms for me, but I'd like to get data from a database to populate the pdf form based on the user id that is passed in the url.

Huh? ;) cfdocument doesn't create or populate forms. But you can populate forms with cfpdfform.

is there a way to know the progress of the query to display a progress bar...

No. Queries don't return partial progress. You'll only know when they're complete.

or is there a way to cfflush the output so that it will begin populating while its still querying?

Definitely not for file downloads. The old hack was to display a fake progress image/div when the download starts. Then hide it when the download's finished. The newer tags like cfprogressbar are a spin on that old idea. http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec19562-7fdf.html

You're welcome. I know CF9 has an uploader now too, but the asfusion one still stands the test of time IMO. Those guys do some pretty slick work :)

I'm not doing actionscript, mind you, that's another headache I don't need.

Unfortunately once you start using flash forms, you can't avoid it for long. lol Not unless your forms are dead simple ..

Yeah, I was never a fan of flash forms. They seem nice but I'd rather build something custom sometimes.

They weren't bad. Personally I think their time has come and gone already. Most people prefer html forms these days. But the http://www.asfusion.com/examples/ archives are still a great resource for the old flash form stuff.

I am getting the same problem (Ie EMPTY ARRAY) while trying with your solution.

It works with a copy/paste of the xml you posted. If you're still getting an empty array, something about your actual data must be different.

And i didnt get the meaning of xml malformation.

I'm wondering if your first post got mangled by the forum ... and maybe that's why your search isn't working. In XML double quotes are escaped, so the phrase:

"Enquiry Type"

Would become

"Enquiry Type"

That's different than what you posted. The first post contains an extra & which is wrong.

&"Enquiry Type"

To find the right search string we need to have the _correct_ xml. Can you attach it a file or post a link to it?

"Enquiry Type"

On second glance, that xml looks malformed. If the forum didn't mangle it, that literally translates to:

Please complete the "Enquiry Type" field

.. so the expression to match it would be:

<cfset SearchResult = xmlSearch(ParsedXML, 
		"/langtexts/langtext[originaltext='Please complete the &quot;Enquiry Type"" field']")>

Haven't figured out the magic string yet. But supposedly xpath gets ugly when quotes are involved.
http://kushalm.com/the-perils-of-xpath-expressions-specifically-escaping-quotes

You're welcome. I don't know why the charset="utf-8" isn't taking effect. But at least there is a work around!

That is the same code they started with. Anyway, it's been 3 months. I'm pretty sure he's solved it by now ;-)

Huh?

Ouch. Just looking at all those excess pound signs hurt my eyes ;-)

Don't know about your code. But Paolo's example shows a java.io.ByteArrayOutputStream as the fileContent.

<cfdump var="#response#">

A possible fix is explicitly convert it to UTF8 first. Then deserialize.

...
<!--- response is a java.io.ByteArrayOutputStream for some reason?
      convert it to UTF8 string --->
<cfset utf8Response = response.fileContent.toString("UTF8")>
<cfset response = DeSerializeJSON(utf8Response) />
<cfset townName = response.geonames[1].name />
...

... or

...
<cfset response = DeSerializeJSON(ToString(response.fileContent.toByteArray(), "UTF8")) />
<cfset townName = response.geonames[1].name />
<cfcontent type="text/html; charset=utf-8"/>
<cfoutput>#townName#</cfoutput><br />

You need to read the file content into a variable. Then use <cfmailparam content="...">. Then it won't matter if you delete the file.
http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7c15.html

I think the mail could not be sent before the directory is deleted.

No, not if you're using spooling. With spooling mail is sent via a separate thread and may be sent after you delete the file. If you read the docs on cfmail that's the expected behavior (see below). If you're on 8.0.1+, a better option is to use <cfmailparam content="..."> instead.

http://livedocs.adobe.com/coldfusion/8/Tags_m-o_01.html
Note: The cfmail tag does not make copies of attachments when spooling mail to disk. If you use the cfmail tag to send a message with an attachment with spooling enabled and you use the cffile tag to delete the attachment file, ColdFusion might not send the mail because the mailing process might execute after the file was deleted. ....

Most of the time, replace() is more efficient.

Geez.. this thread's like 3 years old!

Yeah, I tried that one too. I think it works. But it did leave a trailing slash when the # of chars are divisible by 5 ...I think. I was too lazy to trim it. So I went with listAppend(). But insert's probably better performance :)

This should fix those issues

<cfscript>
   str    = "123456789ABC66HYIGGasdfsdf ewqrdsfsdfsdfsd a111sdf134sa65d4asd4as";
   newStr = "";
   while (len(str)) {
      newStr = listAppend(newStr, left(str, 5), "-");
      str = removeChars(str, 1, 5);
   }
   WriteOutput(newStr);
</cfscript>

It's the right idea, but the logic breaks down for large strings like "123456789ABC66HYIGGasdfsdf ewqrdsfsdfsdfsd a111sdf134sa65d4asd4asd";

That was just psuedo-sql ;-) Though ... it should work ok. Notice it's almost the same as your original, aside from the table name

Can you post the actual query you used?

psuedo-sql

SELECT Band, Address
FROM   YourTable
WHERE  Band LIKE '%#form.keyword#%'

original

SELECT *
FROM   links
WHERE  url LIKE '%#form.keyword#%'

Sure you can do that. You can query 1 column, but return totally different columns in your SELECT list. Just do what you described, ie Filter on the "Band" column and return the Band and Address in the SELECT list.

SELECT Band, Address
FROM   YourTable
WHERE Band LIKE '%#form.keyword#%'

Then you can use either or both in your output. ie Use "Address" for the link href and Band for the link title

<a href="#address#">Band Name: #Band# URL: #Address#</a>

Yeah, a straight conversion won't remove the # signs that are already there. You'd need to run an UPDATE to get rid of them. Just find records starting or ending with # and remove them. Not tested. So don't use it on a live table without a back up ;-)

<!--- starts with --->
UPDATE YourTableName
SET    ColumnName = Right( ColumnName, Len(ColumnName)-1)
WHERE  ColumnName LIKE '##%'

<!--- ends with --->
UPDATE YourTableName
SET    ColumnName = Left( ColumnName, Len(ColumnName)-1)
WHERE  ColumnName LIKE '%##'

From what I could tell it's Access that adds the #'s when the "hypertext" data type is used. Weird. Unless there's a built-in function that returns the url without the # signs, I'd probably avoid that data type. Too bad though, perfect in every other way.

Do a search for mod and modmyi website url appears and it works!

Great!

Just to make it more user-friendly, you might default to (show all records) if the text field is left blank though.

the column address to url type

Well that's a new one. I've never used it. I checked ... and seems that's what's adding the # signs. Not sure what the best answer is here. Normally I'd say use "text". But it's max is 255 chars. Not long enough for url's. "memo" is an option, but it's got some limitations too.

I can see the dump. The values do have extra # signs in them. That's why they look strange. The code adding inserting them into your db is probably wrong (ie has too many # signs somewhere). Can you post that code?

You can clean up the existing values w/a query. What db type are you using?

Now I can see the results at least :) From the output, it looks like the url addresses are messed up. Almost as if the db value had extra # signs in it. Like "#http://en.wikipedia.org/wiki/In_Flames#" instead of just "http://en.wikipedia.org/wiki/In_Flames"

CFdump the query on at the top of search.cfm so we can see the raw values.

<cfdump var="#qUsers#">

Just noticed a missing comma

WHERE 1 = 0
<cfloop list="#Qry1.usergroup#" index="chkGroup">
    OR ','+ usersgroup +',' LIKE '%,#chkGroup#,%'
</cfloop>

when i do the search, it has all these # at the start and end i dont know how to get rid of them

Sounds like the code isn't even being executed. It's just being displayed like html. Either the code isn't inside a .CFM script or maybe your server isn't set up properly. Are you sure the CF server is installed correctly?

Gotcha. Sorry you're stuck with such a poorly structured table. MySQL might have other options. But for other db's try using LIKE. You'd have to loop through your list and use LIKE to check for each value. It's slow ... and frankly awful sql, but should work.

WHERE 1 = 0
<cfloop list="#Qry1.usergroup#" index="chkGroup">
    OR ','+ usersgroup +',' LIKE '%,#chkGroup#%'
</cfloop>

... like saying

WHERE  ','+ UserGroup +',' LIKE '%,21,%'
OR     ','+ UserGroup +',' LIKE '%,15,%'
OR     ','+ UserGroup +',' LIKE '%,17,%'

Your problem is the table design. You shouldn't store lists. That's why the query is so hard. Normalize the table and the query will be a simple join.

Unique users and groups should each have a separate table.

User Table: UserID (unique user ID)
GroupsTable: GroupID (unique group ID)

User assignments should go in a 3rd table. They keys being "UserID" and "GroupID"

UserID, GroupID
1 (John), 13 (Group A)
1 (John), 15 (Group B)
...

I need the results in url form not text

Use cfoutput to display the query, and use the columns to construct a link ie <a href="(link url)">link title</a>. Though URL is a reserved word in CF. You might get weird results if you use just #url#. So think about changing the name your "url" column.

<cfoutput query="qUsers">
       <a href="#qUsers.url#">#qUsers.url#</a>
   </cfoutput>

stay in the links.html page

CF code can only be executed in a .cfm page. So you'd need somethin like jquery or iframes to do that.

You're not hiding the url variables. They'll still be just as visible in the browser history and as easy to tamper with.

Ugh... frames.
http://stackoverflow.com/questions/1203068/why-should-i-not-use-html-frames

Yeah, I've never used Dreamweaver. Probably some weird white space/encoding problem. Glad it's solved :)

.. and what happened with the test page? Does the character appear on ALL cfm pages or just that one?

If you view the source for the 2 pages, it's different. So what's the CFM code used to generate this section? It's not in the HTML you posted above.

src="images/linkedin.jpg" /></marquee></p>
                	</td>
				</tr>             
				</table>

	Â 			<br />&nbsp;<br />	
    		</td>

Did you check all directories in the heirarchy? Because the code you posted doesn't create that character when run as a .cfm page. Create a simple test page with just 1 line:
<cfoutput>#now()#</cfoutput>

If the character shows up, it's almost certainly an Application.cfm/cfc problem. If not, try copying the code into a new page. (Use a text editor like notepad). Does it still occur?

Post a small sample of what you've tried and what it's doing wrong/not doing.

Ok. You might want to read up on the http specs, so you'll understand why it's not possible. Best of luck.

I don't see a stray characters. If it only happens with .cfm files, check your Application.cfm/cfc files. It runs every time you request a .cfm page. So it might be the culprit.

Even with a link, you can't hide url variables. They're visible by design and you can't change that. The best you can do is encode them or hash them into a "tiny url". But you're still passing the values. But like I said, obfuscation is a poor substitute for real security. If there's information people shouldn't see, it should be protected by a login and validated on each page.

You can't hide a query string. That's not how it's designed. You can however

- encrypt query strings
- use method=post instead of method=get, so variables are not sent through the query string
- or store variables in the session scope instead

.. But obfuscation isn't a substitute for good app security.

"20110224" isn't a standard date string. IsDate expects something recognizable as a US date string, such as: yyyy-mm-dd. So even 2011-02-24 would work. Keep in mind the form field value's just a string. You can easily validate it and format it however you want with a few string functions.