ColdFusion Online - Tips, Tricks and Tutorials!

SQLAuthority.com News - Journey to SQL Authority Milestone of SQL Server

2007-06-19T07:00:00.000-07:00

SQLAuthority.com News - Journey to SQL Authority Milestone of SQL Server

I am very glad to write this 200th post of this blog. I would like to express my gratitude to all of YOU - my readers for continuously reading this blog. I receive many comments and emails with feedback, questions and suggestion everyday. I enjoy meeting few of you during this journey as well. Please do send me feedback and your request to make this blog better. Following is milestone of Journey to SQL Authority.

SQL Server Interview Questions and Answers Complete List Download (PDF)

SQL Server Database Coding Standards and Guidelines Complete List Download (PDF)

Find SQL Server Job or Post SQL Server Job

Lighter Side of SQL - SQL Humor

Subscribe to SQLAuthority.com by Email for Regular Update

SQLAuthority Feed

New Personal Homepage of SQLAuthority Author Pinal Dave

Updated Resume of SQL Authority Author

List of All Articles of SQL Authority Blog for Easy Navigation

List of My Favorite SQL Articles on SQLAuthority

Search SQLAuthority.com for Quick Search of SQL Keywords

Support Child Rights and You

Thank you,

Pinal Dave (http://www.SQLAuthority.com)

Visit New Blog on SQL Server - SQLAuthority.com

2007-06-12T22:01:00.000-07:00

Hello All,

Visit my new Blog about SQL Server. I post there regularly.

http://blog.sqlauthority.com

Align ColdFusion MX 7 Flash Forms using CSS

2005-10-20T07:00:00.000-07:00

Goal:
Align ColdFusion MX 7 Flash Forms using CSS. Table less layout.

Story:

Tables are only for tabular data display.
CSS is the best to web programming we all know that by now.

ColdFusion MX 7 has feature of Flash Forms. I have come across this question many times. Many developers have asked that how to use HTML element in the flash forms and how to align the input fields. The solution is “Do not use table or other html element. Use CSS”. It is not that difficult at all. I have created this simple example for that.

Result:
It is very simple. Flash forms are aligned beautifully with CSS, no use of Table element of HTML.

Code:
<style type="text/css">

</style>
</head>

<body>

<div id="container">
<form id="example-form" method="post" action="">
<fieldset>
<legend>Personal information</legend>

<label for="fm-req_forename">First name</label>
<div><input type="text" name="fm-req_forename" id="fm-req_forename" /> <br /> </div>
<label for="fm-req_forename">Last Name</label>
<div><input type="text" name="fm-req_surname" id="fm-req_lastname" /> <br /> </div>
<label for="fm-eml_email">Email</label>
<div class="fm-required"><input type="text" name="fm-eml_email" id="fm-eml_email" value=" " /> </div>
</fieldset>

<div id="fm-submit"><input type="submit" name="Submit" value="Submit" /></div>
</form>

CFFORM validation does not work

2005-10-14T07:00:00.000-07:00

Goal:
CFFORM validation does not work.

Story:
I have faced this issue quite a few times so far. There are many ColdFusion servers, in my company. We keep on upgrading hardware of servers very frequently. Every new installation of operating system requires the installation of ColdFusion.

Personally, I like to use the regular FORM over CFFORM and write my own JavaScript validation. Well, there is a lot of “ancient” code, which we are cleaning up as we go. After importing all the code, configuration and settings while try to use forms sometime CFFORM does not work. When I move the same code to different server to test code it works as expected and when we move it back it does not work. This was quite a surprising problem for a while. We have blamed browser, operating system, domain etc thing for this issue; none of them are correct.

Problem:
The error we faced was

Line:8 Error: Object Expected

None of the JavaScript Validation worked. Form did not preserve any of the data which was posted back.

Solution:
The solution of this problem is not code, rather a configuration and adjustment to the server. The journey to find the solution was very interesting. I removed the CFFORM validation and tried to use simple form. No Luck! I uploaded the same code to another server on different domain, it worked fine. I re-wrote the CFFORM to regular HTML FORM and JavaScript validation. It worked fine. Well, it determines that Code was perfect but the there is something about CFFORM is not working. Carefully, observing the code, which is generated using CFFORM, I see following line of code.

<SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript" SRC="/CFIDE/scripts/cfform.js"></SCRIPT>

I played with that and I realize that cfform.js was physically there but missing as relative path for server. I checked the ColdFusion Administrator it was missing the path there.

I) I went to IIS & then map my CFIDE folder under my Web Site giving the alias as CFIDE.

II) After that I created mapping to my CFIDE folder in ColdFusion Administrator

III) I added new path like

Logical Path:/CFIDE

Directory Path: Z:\Inetpub\wwwroot\CFIDE

IV) Restart the ColdFusion Server.

CFFORM validation worked!

Result:
The solution will work when there is a lot of code using CFFORM on the server and “Object Expected” error occurs. This solution will help to fix the problem right away.

I highly recommended writing the code which does not use CFFORM. Please write your own HTML form and JavaScript validation; which gives far great control on that form element.

Online Reference:
ColdFusion MX 6.1: Hot fix for cfform controls
http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_18951

Best Reference Book for ColdFusion MX

2005-10-13T07:00:00.000-07:00

The frequent question, I come across is “Which is the best reference book for ColdFusion MX?” I own almost all the book available for ColdFusion in my personal library. If I have to select only one book, I would recommend Macromedia ColdFusion MX 7 Web Application Construction Kit by Ben Forta. Following is my review on Amazon.com for the same book.

Must have 'significantly enhanced' MX 7 book

ColdFusion MX 7 Web Application Construction Kit (Web Application Construction Kit) by Ben Forta Ben Forta, Raymond Camden, Leon Chalnick, Angela C. Buraglia. ISBN: 0321223675

I just finished reading ColdFusion MX 7 Web Application Construction Kit. This is another master piece from Ben Forta. This book is definitely "significantly enhanced" than the previous version.

Like previous version it includes all the basic concepts from installing the ColdFusion to Database concepts, from data-entry forms to new Flash integration features. It is great as a guide to planning and making decisions about development than as a comprehensive reference. I have to say it is great in the manner of which it is written, and is easily understandable. All time favorite explanations in the book like next-n paging, error handling, User Defined Functions (UDF) and ColdFusion Components (CFC) are explained in same lucid and easy to understand language with examples.

In this new addition, the Flash applications are described in details with examples. The new features of Graphing, Printing, and Reporting are covered in length. The tricks and tips of the MX 7 like recursive directory search using cffile, flash forms, flash paper, Dreamweaver MX 2004 extensions are explained very well by authors.

Do not forget to read the additional chapters from CD-ROM. The appendix of the book is almost same as previous version with few changes. I will say this book is essential for beginners and advance ColdFusion MX 7 developer.

Pinal Dave (Macromedia Certified Advanced ColdFusion MX Developer)

ColdFusion function using RegEX (Regular Expression) to verify special characters in URL

2005-10-08T07:00:00.000-07:00

Goal:
Create the ColdFusion function using RegEX (Regular Expression) which verify string (URL) do not use special characters.

Story:
I came across situation when we allowed our clients to enter the URL variables in our system. The specification for URLs (RFC 1738, Dec. '94) poses a problem, in that it limits the use of allowed characters in URLs to only a limited subset of the US-ASCII character set. We can not have special characters in the URL. There is simple and easy explanation of this issue is at following site by Brian Wilson (http://www.blooberry.com/indexdot/html/topics/urlencoding.htm)

The special characters are as follows:

Dollar ("$")
Ampersand ("&")
Plus ("+")
Comma (",")
Forward slash/Virgule ("/")
Colon (":")
Semi-colon (";")
Question mark ("?")
'At' symbol ("@")
Single Quotation marks ("’")
Double Quotation marks ("”")
'Less Than' symbol ("<")
'Greater Than' symbol (">")
'Pound' character ("#")
Percent character ("%")
Left Curly Brace ("{")
Right Curly Brace ("}")
Vertical Bar/Pipe ("|")
Backslash ("\")
Caret ("^")
Tilde ("~")
Left Square Bracket ("[")
Right Square Bracket ("]")
Grave Accent ("`")

Result:
I wrote the ColdFusion function using Regular Expression (RegEx) which performs following tasks.

Returns (Displays) the error if special character is used in the string.
Returns (Displays) the special character to easily identify.

Code:

<cffunction name="CheckSpecialChars" output="false" returntype="struct">
<cfargument name="PageName" type="string" required="yes">

<cfset arguments.SpecialCharsError = 0>
<cfset arguments.SpecialChar = ''>

<cfset regex = '[$|&|+|,|/|:|;|=|?|@| |/"|/''|<|>|##|.|%|{|}|\||^|~|\]|\[|`]'>

<cfif refind(regex, arguments.PageName)>
<cfset arguments.SpecialCharsError = 1>
<cfset r = refind(regex,arguments.PageName)>
<cfif find(" ", mid(arguments.PageName, r, 1))>
<cfset arguments.SpecialChar = '[space]'>
<cfelse>
<cfset arguments.SpecialChar = mid(arguments.PageName, r, 1)>
</cfif>
</cfif>

<cfset arguments.retSpecialCharsCheck = StructNew()>
<cfset arguments.retSpecialCharsCheck.SpecialCharsError = arguments.SpecialCharsError>
<cfset arguments.retSpecialCharsCheck.SpecialChar = arguments.SpecialChar>


<cfreturn arguments.retSpecialCharsCheck>
</cffunction>


Online Reference:
Good Reference of special URL Encoding
http://www.blooberry.com/indexdot/html/topics/urlencoding.htm

RFC URL Encoding
http://www.rfc-editor.org/rfc/rfc1738.txt

Create an Excel Spreadsheet dynamically using ColdFusion

2005-10-02T07:00:00.000-07:00

Goal:
Best way to create an Excel spreadsheet dynamically using ColdFusion.

Story:
There are many occasions when I have to prepare the Excel Reports from database. I have two options

I run SQL query in query analyzer and open the Excel and copy the results.

I copy the query in my ColdFusion code and create the Excel File dynamically.

The advantages of the second options are:

I save lots of time

I can save the code file for future use.

I do not have to open two different programs (MS SQL and MS Excel) to do one simple job. I am able to do it right away in my HTML Editor. I use Dream Weaver but one can use Notepad.

I can give the name of the Excel file on the file.

If the data is changed, complete process can be done automatically running the same code again.

If we use cfschedule to run the schedule task, we can create the dynamic Excel file with dynamic name everytime.

Result:
I can produce Excel file in ColdFusion using dynamic data and dynamic file name on the fly.

Code:

<cfquery datasource="MyDataSourceHere" name="qryAccessData">
SELECT Field1, Field2, FieldN
FROM AccessTable
</cfquery>


<cfheader name="content-disposition" value="inline;filename=AccessToExcelDump.xls"> 
<cfcontent type="application/msexcel">
<table border="1">
<tr>
<th>Field1</th>
<th>Field2</th>
<th>FieldN</th>
</tr><cfoutput query="qryAccessData">
<tr>
<td>#qryAccessData.Field1#</td>
<td>#qryAccessData.Field2#</td>
<td>#qryAccessData.FieldN#</td>
</tr></cfoutput>
</table>

Online Reference:

cfcontent http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-a17.htm#wp2850760

cfheader http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-p56.htm#wp1355655

Error Debugging using OnRequestEnd.cfm

2005-09-25T07:00:00.000-07:00

Goal: More debugging information.

Story:
Let us start our blog with debugging information.
We all know that we need debugging information all the time. Personally sometime for me all the default debugging information is not enough and I want more.
I put all the information in the OnRequestEnd.cfm . So everytime, I need it I run I include this file. As we know this file should be in the same folder as Application.cfm and also it should be named as OnRequestEnd.cfm on Unix/Linux machine. Windows machine does not care for case. I keep this file in the root folder as _OnRequestEnd.cfm and whenever I need it I rename it as OnRequestEnd.cfm .
Also there is optional code for sending email to user.

Result:
The code will be display the entire possible variables one will ever need to debug the code.

Code:

<cfsavecontent variable="generated_output">
<cfoutput>
<pre><font face="arial">
<strong>date.time:</strong>
#now()#

<strong>cgi.remote_addr:</strong>
#cgi.remote_addr#

<strong>cfcatch.type:</strong>
#cfcatch.type#

<strong>cfcatch.message :</strong>
#cfcatch.message #

<strong>cfcatch.detail:</strong>
#cfcatch.detail#

<strong>cfcatch.tagcontext:</strong>
<cfloop index=i from=1 to=#ArrayLen(CFCATCH.TAGCONTEXT)#><cfset sCurrent = #CFCATCH.TAGCONTEXT[i]#>#i# #sCurrent["ID"]# (#sCurrent["LINE"]#,#sCurrent["COLUMN"]#) #sCurrent["TEMPLATE"]#<br></cfloop>
</pre>

<strong>Variables</<strong><br>
<cfdump var="#variables#"><br>
<strong>FORM</<strong><br>
<cfdump var="#form#"><br>
<strong>URL</<strong><br>
<cfdump var="#url#"><br>
<strong>CGI</<strong><br>
<cfdump var="#cgi#"><br>
<strong>COOKIE</<strong><br>
<cfdump var="#cookie#"><br>
<strong>CLIENT</<strong><br>
<cfdump var="#client#"><br>
<strong>Session</<strong><br>
<cfdump var="#session#"><br>
<strong>Application</<strong><br>
<cfdump var="#application#"><br>
<strong>Server</<strong><br>
<cfdump var="#server#"><br>
</font>
</cfoutput>
</cfsavecontent>

<cfmail server="mail.com" from="system@mail.com" to="admin@mail.com" cc="owner@mail.com" subject="#cgi.http_host# - Error" type="HTML">
#generated_output#
</cfmail>

Online references:

We do not need as I already have wrote most of it here.

---Pinal

eBaySearch Technique and cflocation

2005-09-15T07:00:00.000-07:00

Goal: Submit eBay search Queries through URL.

Story:
Well, every time, I go to eBay I have good search form there. The search is pretty good but sometime it brings wrong information as user has entered the wrong information in it. Well, I do not have any solution for that. There could be some good information in the book “eBay Hack” but I do not have that book so I have to figure out myself.
At present I am working in the company which has to do search in the http://www.ebaymotors.com/. Well, what we were trying to bring out is the similar car information what user is about to launch the auction.

Problems Faced:
• eBay does not allow you to pass the parameter in the URL and it rewrites the URL if you pass any other value then exact string.
• eBay uses JavaScript rewriting the form and submits to DLL of it which brings back the results.
• eBay does not use common delimiter like ?, =, &, comma or other.
• Post or Get can not be used.
• We wanted to be completely legal and did want to use their API, as eBay does not provide API for search, eBay want the user to come to their site to do the search.
• While using any of the form method it posts like this in the URL like
http://mysearch.cfm/?searchterm=car&cat=hondainstead eBay will use following search URL

http://mysearch?searchcatQQAcarZcatQQAhondamoremoremore

Solution:
Using try and error and different approaches finally I figured out following logic. This is for the information and it is not authorized by eBay or any other API development programs.

This only works with PASSENGER cat in eBayMotors:

BASE URL:

http://motors.search.ebay.com/_W0QQsacategoryZ6001

MAKE Parameter: QQsamotorsmakeZbmw

MODEL Parameter: QQsamotorsmodelZ3Q20Series

START YEAR Parameter: QQsamotorsstartyearZ2000

END YEAR Parameter: QQsamotorsendyearZ2000

ALL ITEMS - Pass no value

AUCTIONS - Pass QQsasaleclassZ1

BUY IT NOW - Pass QQsasaleclassZ2

SEPERATOR (LIKE=) – QQSA

DELIMITER – Z

SPACE BETWEEN WORD – Q20

Architecture:
As we know we can not use get or post as they will post the param in the form of the coldfusion and using = and & in them. What we had to do is that we have to create the required search string first and then after open a new page and display the results there.
I finally decided to use the <cflocation url= “someurl”> tag there as it directs the page to the url specified in the tag.
In summery the architecture development steps are as follows.
• Create a form
• Submit to new page use target=_blank
• Create a search string
• Use cflocation to direct that to ebay search page
• Smile.

Result:
We were finally able to create required search string and accomplish our task..

Code:

Use following function to replace things in variable:
<cfset variables.ebay_make = rereplace("#trim(EbayCat.CatName)#"," ", "Q20", "all")>

=====

Use following code to direct to eBay Page:locationpage.cfm
<cfoutput>
<cflocation url="#display_compare##with_the_compare#">
</cfoutput>

=====

Following is the constructions of the form using drop down menu:
<form name="comparable_auctions" method="post" action="locationpage.cfm" target="_blank">
<strong>Comparable Auctions</strong><br><br>
Display
<select name="display_compare" style="font-size:10px; width: 141px;">
<option value="http://motors.search.ebay.com/_W0QQsacategoryZ6001QQsasaleclassZ1">
Auctions Only</option>
<option value="http://motors.search.ebay.com/_W0QQsacategoryZ6001QQsasaleclassZ2">
Buy It Now Auctions Only</option>
<option value="http://motors.search.ebay.com/_W0QQsacategoryZ6001">
All Listings</option>
</select>
with the
<select name="with_the_compare" style="font-size:10px; width: 161px;">
<option value="<cfoutput>QQsamotorsmakeZ#trim(variables.ebay_make)#
QQsamotorsmodelZ#trim(variables.ebay_model)#</cfoutput>">
Same make and model</option>
<option value="<cfoutput>QQsamotorsmakeZ#trim(variables.ebay_make)#
QQsamotorsmodelZ#trim(variables.ebay_model)#QQsamotorsstartyearZ
#trim(val(variables.year))#QQsamotorsendyearZ#trim(val(variables.year))#</cfoutput>">
Same make, model and year</option>
</select>
<input type="submit" value=" View " >
</form>

Online references:

We do not need as I already have wrote most of it here.

---Pinal