CODEWITHSUNDEEP
c#sql
Transformerio
Transformerio

Reputation: 23

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

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();

Upvotes: 2

Views: 362

Answers (2)

JackSojourn
JackSojourn

Reputation: 364

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

Upvotes: 0

prinkpan
prinkpan

Reputation: 2247

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.

Upvotes: 1

Related Questions