Migration to MSSQL - Concatenation in Update Statement

I am migrating a database from access to MSSQL, and updating the website coding as I go. I had an update statement as follows:

UPDATE Customers SET cCredit = cCredit+"&Request.form("credit")&", cCreditNotes = '"&DATE()&"-"&Request.form("credit")&"-"&why&";"&cCreditNotes WHERE cId = "& Request.form("cust") &"

Open in new window


The update of cCreditNotes is supposed to add the entered values to the beginning of the existing data for that field. However, I get the following error:

The data types varchar and nvarchar(max) are incompatible in the '&' operator.

Open in new window


How can I achieve the same result as I had with Access in MSSQL?

Thank you
stressfreewebsAsked:
Who is Participating?
 
stressfreewebsConnect With a Mentor Author Commented:
Hi David,

I tried +, but found it didn't work when the value in the column was null.

However, I've got around that by repeating the same statement twice, once with a where xxx is null, and once with a where xxx is not null. The null one doesn't use +, the is not null one does.

This seems to do the trick, so thanks for the pointer to using the + sign

Dan
0
 
davidi1Connect With a Mentor Commented:
have u tried using + instead of &?
0
 
stressfreewebsAuthor Commented:
Thanks to the pointer I was able to work a full solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.