How to Concatenate notes into a note field

[yoshismokey]

I have a field "notes" that I would like to allow the users to add to without overwriting the last note added. Is there an easy way to do this in SQL?
Here is the code I have so far:

<cfquery name="UpdateApproval" datasource=#MM_connproducts_DSN# username=#MM_connproducts_USERNAME# password=#MM_connproducts_PASSWORD# debug="yes">
UPDATE APPROVALS Set OTI_VP_Name = '#form.OTI_VP_Name#', OTI_VP_Email='#form.OTI_VP_Email#' ,CS_Name='#form.CS_Name#', CS_Email='#form.CS_Email#', Updated_Date='#today#', AddedNotes= '#form.Notes#'
Where Transaction_Number = '#form.Transaction_Number#'
</cfquery>

Thanks in advance.

 

[asounds]

I would create a second table "Notes

 

[yoshismokey]

By that, do you mean every time they go in and add a note, a new record will be added to the new "Notes" table with the transaction number as the key?

 

[philhege]

For the current table schema,

AddedNotes= AddedNotes + char(13) + char(10) +'#form.Notes#'

HTH,


Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[yoshismokey]

I tried it, but got an error on the char(13) so I just did this:
AddedNotes= AddedNotes +'#form.Notes#'
which works as long as something is in AddedNotes to begin with, otherwise, it doesn't work. Is there something about the char(13) and char(10) that I'm missing?

 

[philhege]

If your database has an NULL eval function, use it. A SQL Server example:

SET AddedNotes = ISNULL(AddedNotes,'')+ISNULL('#form.Notes#','')

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[asounds]

Sorry for taking so long to reply back.

Yes, I would add a new record for each note, and also insert the username or id for that user.