Tag: cfquery

Retrieve Last Record Inserted – Coldfusion

This post highlights an old method to retrieve last record. Coldfusion has changed a lot since then.  Keep searching.


If you are a developer, there is no doubt that you have the need to retrieve the last record of a database insert without requiring another input from the end user.  You want your user experience to appear as fluid as possible and an unnecessary multiple page submit does not accomplish that.

If you have a record that you will be inserting and need the ID of that just inserted record, your code will depend upon the database that you use.  I will cover two of the most common DB’s used, MSSQL and MySQL.

Retrieve last record inserted (ID) if using Microsoft SQL Server (MSSQL):

I won’t go deep into the how and why, what I am looking to do is provide a basis for you to get over this little stumbling block.  When using MSSQL, you can accomplish the task within your record insert query.  I am assuming that you know the basics about inserting data using CF Query.  I am also using <cfqueryparam> to protect my queries from hacks and to require particular datatypes. This has nothing to do with the key retrieval, it is just good practice.

Continue reading Retrieve Last Record Inserted – Coldfusion

Apostrophe Issues in an Update Query

Coldfusion Code Tip…

When things are frustrating, and have a simple solution, I like to share.

When you are a doing a data update, you will often have a text field in a form that will bring an apostrophe with it. For example, if I am collecting business information, I could have a visitor enter their company name. The name might be Amy’s Gems. If I do a sloppy insert statement like the one below, the query will fail.
<cfquery datasource="#application.dsn#">
UPDATE customers
SET companyName='#form.companyName#'
WHERE id=#recordID#
</cfquery>
If you are allowing your users to update or insert their own data, this will error out (with IIS and MS SQL DB running CF8) when your user includes an apostrophe in the field.

You can avoid this by enclosing your values in a cfqueryparam. In fact, getting into the habit of using cfqueryparam in every instance will be a much more secure method and will reduce your exposure to malicious attacks such as a SQL injection hack.

The above query would now look like…
<cfquery datasource="#application.dsn#">
UPDATE customers
SET companyName=<cfqueryparam value="#form.companyName#" cfsqltype="cf_sql_varchar">
WHERE id=<cfqueryparam value="#recordID#" cfsqltype="cf_sql_integer">
</cfquery>
Happy coding. This simple fix might keep someone from pulling their hair out. You can avoid all kinds of cfreplace and cfset statements and end up with an app that is more secure.