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
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

davidi1Connect With a Mentor Commented:
have u tried using + instead of &?
stressfreewebsAuthor Commented:
Thanks to the pointer I was able to work a full solution
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.