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.

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid column error

Hello all,
Does anyone see anything wrong with the sql query below

DECLARE @.BUILDINGLIST nvarchar(100)
SET @.BUILDINGLIST = 'ALABAMA'
DECLARE @.SQL nvarchar(1024)
SET @.SQL = 'SELECT id, CLOSED, building AS BUILDING FROM
 requests WHERE building = (' + @.BUILDINGLIST + ')'
EXEC sp_executesql @.SQL

I keep on getting the following error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ALABAMA'.

Thanks in advance.
Richard M.you need an xtra set of quotations.

SET @.SQL = 'SELECT [id], CLOSED, building AS BUILDING FROM
 requests WHERE building = ('' + @.BUILDINGLIST + '')'

hth|||actually... one more than that.

(''' + @.BuildingList + ''')'

or SET @.BUILDINGLIST = '' + @.BUILDINGLIST + ''

Then run the original way.

'' would cancel into one '.