Jason Bartholme’s SEO Blog

1
2

<tr bgcolor="###iif(SCOPE.currentrow MOD 2,DE('efefef'),
DE('ffffff'))#">

1
2
3
4
5
6
7
8
9
10
11
12
13
14

<cfquery name="getCustomer" datasource="#APPLICATION.DSN#">
SELECT Fieldname1, Fieldname2
FROM TableName
WHERE FieldName4 = <cfqueryparam value = "#query.ID#"
cfsqltype = "cf_sql_integer">
ORDER BY Fieldname1, Fieldname2
</cfquery>
 
<cfoutput query="getCustomer" group="Fieldname1">
<b>#getCustomer.Fieldname1#</b&gt;
<cfoutput>
#getCustomer.Fieldname2#
</cfoutput>
</cfoutput>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<cfform action="#CGI.SCRIPT_NAME#"
        method="post"
        name="form1">
 
<cfinput type="text"
         name="Field1"
         value=""
         required="yes"
         message="Field1 is required"
         maxlength="255"
         size="30" />
 
<cfinput type="submit"
	    value="Submit" />
 
</cfform>

1
2
3
4
5
6
7

<cfinput type="text"
         name="Field1"
         value=""
         required="yes"
         message="Field1 is required"
         maxlength="255"
         size="30" />

1

<cfparam name="SCOPE.varName" default="">

1
2
3
4
5
6
7
8
9
10
11
12
13
14

<cffunction name="getCustomer" access="public" output="false">
 
	<cfargument name="FieldName4" type="numeric" required="yes">
 
     <cfquery name="getCustomer" datasource="#APPLICATION.DSN#">
     SELECT FieldName1, FieldName2, FieldName3
     FROM TableName
     WHERE FieldName4 = <cfqueryparam value = "#query.ID#" 
     cfsqltype = "cf_sql_integer">
     </cfquery>
 
	<cfreturn getCustomer&gt;
 
</cffunction>

1
2
3
4

<cfinvoke component="componentdirectory.componentname"
 		method="getCustomers">
	<cfinvokeargument name="CustomerID" value="#FORM.ID#">
</cfinvoke&gt;

1
2
3
4
5
6

<cfquery name="DELETECustomer" datasource="#APPLICATION.DSN#">
DELETE FROM  TableName
WHERE FieldName4 = <cfqueryparam value = "#query.ID#"
cfsqltype = "cf_sql_integer">
</cfquery>
<!--- ALWAYS remember your WHERE statement! --->

1
2
3
4
5
6
7
8
9
10
11
12
13
14

<cfquery name="INSERTCustomer" datasource="#APPLICATION.DSN#">
INSERT INTO TableName
	(
      Fieldname1,
      Fieldname2,
      Fieldname3
      )
VALUES
	(
      '#trim(StringValue)#',
      #NumericValue#,
      #createodbcdate(now())#      <!--- Properly formated date --->
      )
</cfquery>

1
2
3
4
5
6
7
8
9

<cfquery name="UPDATECustomer" datasource="#APPLICATION.DSN#">
UPDATE TableName SET 
Fieldname1 = '#trim(StringValue)#',
Fieldname2 = #NumericValue#,
Fieldname3 = #createodbcdate(now())#
WHERE FieldName4 = <cfqueryparam value = "#query.ID#"
              cfsqltype = "cf_sql_integer">
</cfquery>
<!--- ALWAYS remember your WHERE statement! --->

1
2
3
4
5
6

<cfquery name="getCustomer" datasource="#APPLICATION.DSN#">
SELECT Fieldname1, Fieldname2, Fieldname3
FROM TableName
WHERE FieldName4 = <cfqueryparam value = "#query.ID#"
              cfsqltype = "cf_sql_integer">
</cfquery>

1
2
3
4
5
6
7

<cfquery name="UPDATECount" datasource="#APPLICATION.DSN#">
UPDATE TableName SET 
Fieldname2 = [Fieldname2] + 1
WHERE FieldName4 = <cfqueryparam value = "#query.ID#"
			cfsqltype = "cf_sql_integer">
</cfquery>
<!--- ALWAYS remember your WHERE statement! --->

1
2
3
4
5
6
7
8
9
10

<cfquery name="INSERTCustomer" datasource="#APPLICATION.DSN#">
INSERT INTO TableName
	(Fieldname)
VALUES
	('Value');
SELECT LastID = @@identity
</cfquery>
<!--- Don't forget the semicolon at the end of the value list. 
Also, "LastID can be anything and referenced as 
INSERTCustomer.LastID --->

1
2
3
4
5
6
7
8
9

<cfquery name="getCustomer" datasource="#APPLICATION.DSN#">
SELECT TOP 5 Fieldname1, Fieldname2, Fieldname3
FROM TableName
WHERE FieldName4 = <cfqueryparam value = "#query.ID#"
		cfsqltype = "cf_sql_integer">
ORDER BY NEWID()
</cfquery>
<!--- TOP and a number determines how many rows are returned
	 NEWID() picks random records --->

1
2
3
4
5
6

<cfscript>
function maskcardnumber(creditcardnumber){
	return "#repeatstring("*",val(len(creditcardnumber)-4))#
#right(creditcardnumber,4)#";
}
</cfscript>

1

©#dateformat(now(),'yyyy')#

1

#dateformat(now(),'mm/dd/yy')#

1
2
3
4
5
6
7
8
9

<cfmail from=""
        to=""
        replyto=""
        failto=""               
        subject=""
        type="html"
        >
Message Here       
</cfmail>

1

 <cfset StructClear(session)>

1
2
3
4
5
6
7
8
9

<cfdump var="#FORM#" label="Form" expand="no">
<cfdump var="#URL#" label="URL" expand="no">
<cfdump var="#SESSION#" label="Session" expand="no">
 
<cfdump var="#APPLICATION#" label="Application" expand="no">
<cfdump var="#CGI#" label="CGI" expand="no">
<cfdump var="#CLIENT#" label="Client" expand="no">
<cfdump var="#REQUEST#" label="Request" expand="no">
<cfdump var="#SERVER#" label="Server" expand="no">

1

<cfcontent type="application/msexcel">

16 Responses to “21 Time-Saving Snippets for Any ColdFusion Developer”

1. Alan Says:
   January 8th, 2008 at 6:14 pm

   I found one thing that needs to be said for MSSQL 2005 queries when using SELECT LastID = @@identity - its not reliable.

   Using scope_identity() function is your best bet.

   @@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the recently added row in a table. You could have a situation where there is a trigger that inserts a new record in a Logs Table, for example, when your Stored Procedure or INSERT SQL Statement inserts a record in the Orders Table. If you use @@IDENTITY to retrieve the identity of the new order, you will actually get the identity of the record added into the Log Table and not the Orders Table, which will create a nasty, nasty bug in your data access layer.

   To avoid the potential problems associated with someone adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your INSERT SQL Statement or Stored Procedure.” - http://davidhayden.com/blog/dave/archive/2006/01/17/2736.aspx

2. Steven Snell Says:
   January 8th, 2008 at 6:18 pm

   Nice info Jason, everyone likes to save time!

3. Jason Bartholme Says:
   January 8th, 2008 at 10:08 pm

   Hello Alan,

   Thanks for your comment. Currently, our production server is MS SQL 2000, we are planning on migrating to 2005 shortly. For the sake of reliability, I’m going to change my code to use the scope_identity() function.

4. Raymond Camden Says:
   January 8th, 2008 at 11:28 pm

   A few notes. First off - your first queries are missing cfqueryparam. You do use it later, but you need to be consistent and use it -anywhere- you have dynamic data in your query.

   Your first function example doesn’t use var scoping. That’s considered _very_ bad practice.

5. Jason Bartholme Says:
   January 9th, 2008 at 12:03 am

   Thanks for your comment’s, Ray. I fixed the DELETE statement and the grouped output to utilize the cfqueryparam. Honestly, I didn’t know of var scoping or it’s importance of securing cfcs. I’m going to look into it, revamp my cfcs, and run Mike Schierberl’s varScoper tool that you mentioned in your blog.

6. Adam Fairbanks Says:
   January 9th, 2008 at 11:49 am

   Jason - great snippets!

   Here are a few comments for refinement:

   1) Alternate row color

   - might want to add the scope to the variable (q.currentrow instead of currentrow)
   - if the background is already white (which it often is), you can use a simple instead of #iif(), since iff is generally slower.

   bgcolor=”##efefef”>

   or with a css class:

   class=”row”>

   2) Forms

   - it might be useful to add a name attribute to the form tag

   3) Functions

   - I agree with Raymond - use var scoping.

   (after the line)

   Also, it looks like the value of the variable in the query should be

   value=”#arguments.FieldName4#”

   rather than

   value=”#query.ID#”

   4) Delete statement, Update statement, and Increment count

   - This is a nit, but the variable name will most likely be #form.ID# (or #url.ID#) instead of #query.ID#

   5) Insert statement

   - StringValue and NumericValue will probably be form.StringValue and form.NumericValue (or url.StringValue and url.NumericValue)

   - #createodbcdate(now())# might more often be #CreateODBCDateTime(Now())# (my capitalization means nothing)

   6) Record ID of last row inserted

   - another nit.. ‘value’ should be ‘#value#’.. and probably ‘#form.value#’ (or ‘#url.value#’)

   7) Cfmail basic

   - Technically, from, to, and subject are the only required attributes. =)

   - Since emails should generally be smtp authenticated, I suggest adding servername, username, and password (and maybe even cc) as attributes, since they would be commonly used

   - And maybe even

   servername=”#application.mailservername#” username=”#application.mailusername#” password=”#application.mailpassword#” (or similar variables)

   Putting these all into snippets is a great idea!

7. Adam Fairbanks Says:
   January 9th, 2008 at 1:25 pm

   Jason - great snippets!

   Here are a few comments for refinement:

   1) Alternate row color

   - might want to add the scope to the variable (q.currentrow instead of currentrow)
   - if the background is already white (which it often is), you can use a simple instead of #iif(), since iff is generally slower.

   bgcolor=”##efefef”>

   or with a css class:

   class=”row”>

   2) Forms

   - it might be useful to add a name attribute to the form tag

8. Jason Bartholme Says:
   January 9th, 2008 at 2:38 pm

   Hello Adam,

   Thanks for stopping by. I added “SCOPE” to the alt row color snippet and also a name attribute to the form snip.

   Good to know that iif() is slower.

9. Adam Fairbanks Says:
   January 9th, 2008 at 3:04 pm

   Below is the rest of my post…. (Are the posts limited to 500 characters?)

   3) Functions

   - I agree with Raymond - use var scoping.

   (after the line)

   Also, it looks like the value of the variable in the query should be

   value=”#arguments.FieldName4#”

   rather than

   value=”#query.ID#”

   4) Delete statement, Update statement, and Increment count

   - This is a nit, but the variable name will most likely be #form.ID# (or #url.ID#) instead of #query.ID#

10. Adam Fairbanks Says:
    January 9th, 2008 at 3:06 pm

    5) Insert statement

    - StringValue and NumericValue will probably be form.StringValue and form.NumericValue (or url.StringValue and url.NumericValue)

    - #createodbcdate(now())# might more often be #CreateODBCDateTime(Now())# (my capitalization means nothing)

    6) Record ID of last row inserted

    - another nit.. ‘value’ should be ‘#value#’.. and probably ‘#form.value#’ (or ‘#url.value#’)

11. Adam Fairbanks Says:
    January 9th, 2008 at 3:07 pm

    7) Cfmail basic

    - Technically, from, to, and subject are the only required attributes. =)

    - Since emails should generally be smtp authenticated, I suggest adding servername, username, and password (and maybe even cc) as attributes, since they would be commonly used

    - And maybe even

    servername=”#application.mailservername#” username=”#application.mailusername#” password=”#application.mailpassword#” (or similar variables)

    Putting these all into snippets is a great idea!

12. Wen Says:
    March 6th, 2008 at 7:30 pm

    This is related to the Import to excel using . If I want to remove some content prior to exporting to excel, what should I do?

    For example, I don’t want to export some radio buttons which are displayed on the top of the table rows for sorting column. I want to skip this sort by row. Thank you!

    -Wen

13. sam Says:
    June 20th, 2008 at 9:42 am

    thanks for taking time to put this together

14. Larry C. Lyons Says:
    January 3rd, 2009 at 11:17 am

    Very late but I thought I’d share this alternative to your snippet for dumping all scopes

    this will dump all the built in scopes without the separate cfdumps in your example,
    hth,
    larry

15. John Schroeder Says:
    July 29th, 2009 at 10:10 am

    Dynamic Copyright Year - shorter yet…

    ©#year(now())#

16. Foton Says:
    January 31st, 2010 at 9:12 pm

    Very good..I used it and thats right thanks..

Leave a Reply