Appending data to database column using coldfusion

Question

For some reason the following UPDATE will not add my notes to the Admin_Notes field. The field is empty but once there is data there I want to add to the current data not replace it.

<cfargument name="adminNotes" required="yes">
<cfargument name="form_ID" required="yes">
<cfargument name="quoteNumber" required="yes">

<cfquery name="completeRFQ" datasource="RC">
    UPDATE RFQ_Forms
    SET Status = 'Complete',
        Completion_Date = CURRENT_TIMESTAMP,
        Admin_Notes = Admin_Notes + <cfqueryparam value="#ARGUMENTS.adminNotes#">,
        Quote_Num = <cfqueryparam value="#ARGUMENTS.quoteNumber#">
  WHERE ID = <cfqueryparam value="#ARGUMENTS.form_ID#">
</cfquery>

This column is set as nvarchar(MAX)

The rest of my query completes correctly and I verified that variable being passed has a string in it.

I wrapped the query in a cftry but I am not getting anything back.

After testing I have an update to my question

The column is actually empty since this is the first time I am running it. When I add text to the column by hand and then run the query the second part of text is added to the column.

How can I add data to the column if it is empty and when it has text in it already?

I am not getting any errors. I wrapped the invoke that calls the above function and got nothing back. The database does update everything else but the Admin_Notes field. — Denoteone, Mar 02 '15 at 10:30
That is the expected behavior. Most databases [will return NULL when one or more of the concatenated values is NULL.](https://msdn.microsoft.com/en-us/library/ms177561.aspx). — Leigh, Mar 02 '15 at 14:14

score 1 · Accepted Answer · answered Mar 02 '15 at 13:32

1

You should be able to wrap an isNull() check around your column. That way if Admin_Notes is null it will convert it to an empty string and append your data

<cfquery name="completeRFQ" datasource="RC">
UPDATE RFQ_Forms
SET Status = 'Complete',
    Completion_Date = CURRENT_TIMESTAMP,
    Admin_Notes = isNull(Admin_Notes,'') + <cfqueryparam value="#ARGUMENTS.adminNotes#">,
    Quote_Num = <cfqueryparam value="#ARGUMENTS.quoteNumber#">
WHERE ID = <cfqueryparam value="#ARGUMENTS.form_ID#">
</cfquery>

answered Mar 02 '15 at 13:32

Matt Busche

14,216
5
36
61

It is important to include the cfsqltype. For CF10+, see cf_sql_nvarchar / cf_sql_longnvarchar. Omitting the sql type could cause unicode text to be garbled [depending on your settings](http://stackoverflow.com/questions/10802388/what-are-the-details-for-using-cf-sql-nvarchar-in-coldfusion-10/10848136#10848136). – Leigh Mar 02 '15 at 16:32
Thank you for your help and the information you provided. +1 checked – Denoteone Mar 03 '15 at 22:30
Actually, I take back the "could". The above definitely garbles unicode text using the default CF10 settings. The cfsqltype *is* important folks ;-) – Leigh Mar 04 '15 at 20:50

score 0 · Answer 2 · edited Mar 03 '15 at 03:48

0

The column is actually empty since this is the first time I am running it. When I add text to the column by hand and then run the query the second part of text is added to the column.

The reason it fails is that you are attempting to concanenate something to null. To fix this, update your table and set all null values to empty strings. Then alter the column to make it not null with a default value of an empty string.

Not related to your question, you should specify the datatypes for your cfargument and cfqueryparam tags.

edited Mar 03 '15 at 03:48

Community

1
1

answered Mar 02 '15 at 13:17

Dan Bracuk

20,699
4
26
43

Technically, disallowing nulls is not required. Only do that if it is appropriate for your application. *Not related to your question, you should specify the datatypes* Actually that is very relevant. If you omit the type, it defaults to CHAR, which might lead to truncation and/or garbling of unicode values depending on your version and settings. For CF10+, you should use [CF_SQL_NVARCHAR](http://stackoverflow.com/questions/10802388/what-are-the-details-for-using-cf-sql-nvarchar-in-coldfusion-10/10848136#10848136). – Leigh Mar 02 '15 at 15:15
... or CF_SQL_LONGNVARCHAR for max columns. – Leigh Mar 02 '15 at 16:27
Allowing nulls means that you have to run the isnull function as per Matt's option. If you are going to do that, there is no point in having the default value. The reason I recommend this approach instead is that the isnull function adds execution time to each query. – Dan Bracuk Mar 02 '15 at 22:31
Any difference for a single record would be negligible. You are probably thinking of using functions in the WHERE clause. That *can* have a noticeable impact on query performance because it hinders the usage of indexes. As far as default values, there is a difference between NULL and an empty string. It is up to the application which default is used. – Leigh Mar 03 '15 at 02:42

Appending data to database column using coldfusion

2 Answers2