Invalid column name 'Columnname' in SQL Server

Question

alter procedure NewUserTableCreation(@Username varchar(50))
as
    declare @CreateUserTable NVARCHAR(MAX)
    declare @AddRecord NVARCHAR(MAX)
    declare @VisitedClothesKids varchar(50)='VisitedClothesKids'
    declare @InitialCount varchar(20)='0'
BEGIN
    --Building query for creating a user table
    SET @CreateUserTable = 'create table ' + @Username +
                           '_Table(UserActivityData varchar(50),DataValue varchar(20))'
    EXEC(@CreateUserTable);

    --Adding Records in the user table
    SET @AddRecord = 'insert into ' + @Username + '_Table(UserActivityData, DataValue)
                      values(' + @VisitedClothesKids + ','  + @InitialCount + ')'
    EXEC(@AddRecord);
END
GO

I'm executing this procedure from C# code. A table is successfully created and then an exception is thrown saying,

Invalid column name 'VisitedClothesKids'
Invalid column name 'InitialCount'

Please help! Many Thanks :)

Andrew · Accepted Answer · 2015-10-01 16:58:08Z

2

The issue is that the string being concatenated itself is not putting the values in quotes:

values('+@VisitedClothesKids+','+@InitialCount+')'

becomes

values(VisitedClothesKids,0)'

when you want it to be

values('VisitedClothesKids','0')'

We should also warn you that the technique you are using here is open to SQL Injection and should be avoided.

answered Oct 1, 2015 at 16:58

Andrew

27.4k5 gold badges67 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sameer Shaik Over a year ago

Thanks for the clue! :D

Philip Kelley · Accepted Answer · 2015-10-01 17:04:49Z

0

To solve this kind of problem, you have to review the statement that your are executing. For example, if you did this (passing in "MyData" for a table name):

PRINT @AddRecord;
EXEC(@AddRecord);

You would see the following as output:

insert into MyData_Table(UserActivityData,DataValue) values(VisitedClothesKids,0)'

Which fails because SQL doesn't know what "VisitedClothesKids" is. You want the statement to be

insert into MyData_Table(UserActivityData,DataValue) values('VisitedClothesKids',0)'

with the quotes to designate the literal string. To get this, modify your "build statement like so:

SET @AddRecord = 'insert into '+@Username+'_Table(UserActivityData,DataValue)
values('''+@VisitedClothesKids+''','+@InitialCount+')'

In this context, SQL will interpret (or "escape") the two single-quotes, '' as a single quote, '.

answered Oct 1, 2015 at 17:04

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

2 Comments

Philip Kelley Over a year ago

Definitely, the code has both design and security issues. I'm only addressing the "why this particular case" fails issue, with tips on how to catch similar future problems.

Sameer Shaik Over a year ago

Thank You Andrew and Philip. The problem has been solved. Yes, I'm using stored procedures to prevent SQL injection. Sad that the code is still vulnerable. Well, this is form my academic mini project (with deadline of course) that is why I'm less concerned about the security. I'll soon learn how to design it in a more secure way. Anyway, thanks again!

Collectives™ on Stack Overflow

Invalid column name 'Columnname' in SQL Server

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related