Invalid operator for data type

What is wrong with this select statement?

SELECT lastName + ", " + firstName + " " + middleName as Name
FROM [users]
WHERE ([usrID] = 100)
I kept getting this error:

Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.

Help is appreciated.

SELECT lastName + ', ' + firstName + ' ' + middleName as Name
FROM [users]
WHERE ([usrID] = 100)

|||Thanks! I tried that and got the same error.|||Please check your table definition for these columns. It seems that the data type for these columns cannot do the operation. If that is the case, change them to nvarchar(50) and then give it a try. I tested the code and it works.|||

mychucky:

What is wrong with this select statement?

Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.

Take a look at the error message, it seems that your column(s) is defined to TEXT type, which can not be used with '+' operator. I agreed withlimno, you should change the data type to nvarchar if the column is not used to store large text. To manage TEXT data, you should use some functions. Take a look at 'Usingtext, ntext, and image Functions' topic in SQL2000 Books Online, or go to this link:

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_11_7zox.asp?frame=true

|||Many thanks for the help. Yes, I do have Text and varachar as thedatatype. So what is the best way to concatenate these columns?|||Change both data types to Varchar. Hope this helps.