Must declare table variable error with c# paramaterized query

Question

I am getting an error with an SQL statement:

must declare the table variable @table

I don't know how I should fix it. Here is the code for the part that makes the query (for example 3 is Communications, 4 is Radio:basic, 5 is 45, 6 is 5, and 7 is 0).

string table = "Skills" + info[3];
SqlCommand cmd = new SqlCommand("use palladium; insert into @category(skill, basepercent, lvlpercent, special) values ( @skillname , @base , @lvl , @special);");
cmd.Parameters.AddWithValue("@category", table);
cmd.Parameters.AddWithValue("@skillname", info[4]);
cmd.Parameters.AddWithValue("@base", info[5]);
cmd.Parameters.AddWithValue("@lvl", info[6]);
cmd.Parameters.AddWithValue("@special", info[7]);
general.dbsend(cmd, connection);

Table name in the insert query can not be passed as parameter in SQL query. You need to have table query generated as var query = string.Format("use palladium; insert into {0}(....", table); and use it as SqlCommand cmd = new SqlCommand(query); — Chetan
– Chetan, Commented Jan 30, 2018 at 6:54
This is usually an indication that your data model is broken. Data that should be modelled in a single table has instead been modelled as multiple tables. And worse, one or more additional columns of data has instead been modelled as metadata - to wit, it's currently embedded in your table names. I'd strongly suggest you fix the data model - every query you write that tries to query based on the mismodelled data will be a painful experience. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 30, 2018 at 7:38

TT. · Accepted Answer · 2018-01-30 06:54:44Z

2

You can't have a table name as a variable in a static query. You can in a dynamic query, by formatting the table name in the query rather than supplying it as a parameter.

SqlCommand cmd = new SqlCommand("use palladium; insert into ["+table.Replace("]","]]")+"](skill, basepercent, lvlpercent, special) values ( @skillname , @base , @lvl , @special);");

The replace bit guards against SQL Injection.

answered Jan 30, 2018 at 6:54

TT.

16.2k6 gold badges53 silver badges95 bronze badges

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

Collectives™ on Stack Overflow

Must declare table variable error with c# paramaterized query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related