Invalid column name Error
-
hi all,
I'm trying to run this query to select from table (as html) to be used for sending an email
but actually it keeps giving that only two columns doesn't exist while they are already there, what may be the cause of this error?
Msg 207, Level 16, State 1, Line 16
Invalid column name 'OutC'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'Inte'.
DECLARE @html VARCHAR(8000)
DECLARE @Recipient VARCHAR(100)
DECLARE @Login VARCHAR(100)
DECLARE @mySubject VARCHAR(100)
SELECT @html =
'Dear '+ @Login +',
' +
'Please arrange for corrective action.
'+
'Thank You.
'
SELECT @html = @html + '<table border="1"><tr><th>ID</th><th>Ver</th><th>Temp</th><th>OutT</th><th>OutC</th><th>Int</th><th>UserName</th></tr>'
SELECT
@html = @html +
('<tr><td>'+ [ID]+'</td><td>' + [Ver]+'</td><td>' + [Temp]+'</td><td>' + OutT +'</td><td>' + [OutC] +'</td><td>' + [Inte] +'</td><td>'+ UserName + '</td></tr>')
FROM tbl
WHERE UserEmail = @Recipient
ORDER BY OutT- This topic was modified 1 year, 1 month ago by tanehome.
-
Can you post the ddl of your table tbl ?
Also keep in mind you may need to cast / convert columns to a varchar data type !
+ convert(varchar(128),[ID]) +
this test worked:
DECLARE @html VARCHAR(8000) = ''
DECLARE @Recipient VARCHAR(100)
DECLARE @Login VARCHAR(100) = 'login'
DECLARE @mySubject VARCHAR(100)
SELECT @html =
'
Dear '+ @Login +',
' +
'
Please arrange for corrective action.
'+
'
Thank You.
'
Declare @tbl TABLE
([ID] INT
, [Ver] VARCHAR(128)
, [Temp] VARCHAR(128)
, OutT VARCHAR(128)
, [OutC] VARCHAR(128)
, [Int] VARCHAR(128)
, UserName VARCHAR(128)
);
insert into @tbl values ( 1,'a1', 'b1', 'c1','d1', 'e1','user1');
insert into @tbl values ( 2,'a2', 'b2', 'c2','d2', 'e2','user2');
SELECT @html = @html + '
<table border="1"><tr><th>ID</th><th>Ver</th><th>Temp</th><th>OutT</th><th>OutC</th><th>Int</th><th>UserName</th></tr>'
SELECT
@html = @html +
('<tr><td>'+ convert(varchar(128),[ID])+'</td><td>' + [Ver]+'</td><td>' + [Temp]+'</td><td>' + OutT +'</td><td>' + [OutC] +'</td><td>' + [Int] +'</td><td>'+ UserName + '</td></tr>')
FROM @tbl
-- WHERE UserEmail = @Recipient
ORDER BY OutT
Select @html as htmlJohan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
thanks for your reply, actually in my case the ID field is already nvarchar as attached figure, however I've test your proposal but still getting the same error.
-
Please post the full table definition, not just the 2 columns.
-
attached the full table definitions.
-
in your query you are using column [Int] instead of [Inte] !!
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
yes, it was by mistake and have been fixed, however the problem still exist is the both mentioned columns.
I also tried to change the columns definitions to "nvarchar" instead of varchar and not fixed the issue.
-
dears, it so weird but the issue has been fixed suddenly!!!
I've deleted the table and recreate it again by making all columns defeminations as "nvarchar" then run the query and it success without errors.
I'm sure I was missing something !!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply