C# SQLCommand adding parameters return 'Invalid object name '@table'

Question

I'm working on a C# DB project and I keep getting this error when I try to add parameters to the command text.

'Invalid object name '@table'.'

This error appears for every parameter I add.

sqlcmd.CommandText = "INSERT INTO [@table](@iVar, @uVar, @pVar) VALUES (@vali, @val1, @val2)";

sqlcmd.Parameters.AddWithValue("@table", "Data");
sqlcmd.Parameters.AddWithValue("@iVar", "Var1");
sqlcmd.Parameters.AddWithValue("@uVar", "Var2");
sqlcmd.Parameters.AddWithValue("@pVar", "Var3");
sqlcmd.Parameters.AddWithValue("@vali", "Val1");
sqlcmd.Parameters.AddWithValue("@val1", "Val2");
sqlcmd.Parameters.AddWithValue("@val2", "Val3");

sqlcmd.ExecuteNonQuery();

You can not pass the table name as parameter to query. Parameters are used for passing values to the command not the table name or column names. — Chetan
– Chetan, Commented Aug 31, 2019 at 3:46

prinkpan · Accepted Answer · 2019-08-31 03:47:18Z

1

Table names & column names cannot be passed as a parameter to SQL command. You can, however, use the below.

var tableName = "Data";
var iVar = "Var1";
var uVar = "Var2";
var pVar = "Var3";
sqlcmd.CommandText = $"INSERT INTO [{tableName}]({iVar}, {uVar}, {pVar}) VALUES (@vali, @val1, @val2)";

sqlcmd.Parameters.AddWithValue("@vali", "Val1");
sqlcmd.Parameters.AddWithValue("@val1", "Val2");
sqlcmd.Parameters.AddWithValue("@val2", "Val3");

sqlcmd.ExecuteNonQuery();

Note that $ is used for string interpolation

You will then have to ensure that tableName, iVar, uVar and pVar is whitelisted to avoid any kind of SQL injection attacks if you are taking this value from end-user.

answered Aug 31, 2019 at 3:47

prinkpan

2,2661 gold badge23 silver badges39 bronze badges

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

Comments

JackSojourn · Accepted Answer · 2019-08-31 03:52:07Z

0

Tables names need to be static. If you are getting the table name from some form of user input you need to validate it. You can do something like the following using a stored procedure:

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'SELECT * from tablename'
EXEC (@sqlCommand)

or something like

SELECT @sSQL = N'SELECT * FROM' + QUOTENAME(@TableName);
EXEC sp_executesql @sSQL

answered Aug 31, 2019 at 3:52

JackSojourn

3644 silver badges18 bronze badges

Collectives™ on Stack Overflow

C# SQLCommand adding parameters return 'Invalid object name '@table'

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related