CODEWITHSUNDEEP
c#ado.netparameters
Aram Gevorgyan
Aram Gevorgyan

Reputation: 2195

Problem with adding parameter

SqlParameter param = new SqlParameter();
param.ParameterName = "@name";
param.Value = tableName;
SqlCommand cmd = new SqlCommand("select * from @name", con);
cmd.Parameters.Add(param);
SqlDataReader rd = cmd.ExecuteReader();

The above code results in the following error message:

Must declare the table variable "@name".

Why do I get this error and how can I fix it?

Upvotes: 4

Views: 793

Answers (3)

FIre Panda
FIre Panda

Reputation: 6637

Wrong query

select * from @name

Please correct it, after From table or view name is expected to come, you should put your param like 'select * from MyTable where col1 = @param'.

You can't put @param instead of table name. Use String.Format("select * from {0}", "MyTable"); instead.

Upvotes: 2

Pranay Rana
Pranay Rana

Reputation: 176946

rather than passing name in the query you can easily replace it over here 

string s = "select * from " + name;
SqlCommand cmd = new SqlCommand(s, con);
SqlDataReader rd = cmd.ExecuteReader();

but this will cause the sql injection error

so i would like to suggest you go for dyanmic query execution in the sql server which you can do with SP_ExecuteSQL.

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1502806

Parameterized queries generally deal with parameters for values within the query - not for table names, column names etc. I don't believe SQL Server supports parameterizing the table name.

You probably want to restrict the names to a known set of valid table names (to avoid SQL injection attacks etc), and use normal string replacement / formatting / whatever to construct the query.

Upvotes: 4

Related Questions