i'm lost: what is wrong with this ado.net code?

Question

well, the question is clear i hope, the code is this:

string sql = "delete from @tabelnaam";
            SqlCommand sc = new SqlCommand();

                sc.Connection = getConnection();
                sc.CommandType = CommandType.Text;
                sc.CommandText = sql;
                SqlParameter param = new SqlParameter();

                param.Direction = ParameterDirection.Input;
                param.ParameterName = "@tabelnaam";
                param.Value  = tableName;

                sc.Parameters.Add(param);
                OpenConnection(sc);
                sc.ExecuteScalar();

tableName is supplied to this function.

I get the exception:

Must declare the table variable @tabelnaam

Answer 1

IIRC, you cant use a substitute the table name for a parameter.

Rather build the SQL string containing the correct table name.

Answer 2

Make to changes

rather than using paramter use this

string sql = string.format( "delete from {0}",tableName);

make use of executenonquery intead of ExecuteScalar

sc.ExecuteNonQuery();

Answer 3

I dont think you can parameterize the table name. From what I have read you can do it via Dynamic sql and calling sp_ExecuteSQL.

Answer 4

Your SQL is incorrect, you are deleting from a table variable yet you haven't defined that variable.

Update: as someone has pointed out, you are trying to dynamically build a query string but have inadvertantly used SQL parameters (these do not act as place holders for string literals).

Answer 5

More here: http://stackoverflow.com/questions/372033/parametise-table-name-in-net-sql

Answer 6

As mentioned by others, you can't parameterise the table name.

However, as you rightly mention in comments on other answers, using simple string manipulation potentialy introduces a SQL injection risk:

If your table name input is fro an untrusted source, such as user input, then using this:

string sql = string.format( "DELETE FROM {0}",tableName);

leaves you open to the table name "myTable; DROP DATABASE MyDb" being inserted, to give you:

DELETE FROM myDb; DROP DATABASE MyDB

The way round this is to delimit the table name doing something such as this:

string sql = string.format("DELETE FROM dbo.[{0}]", tableName);

in combination with checking that the input does not contain either '[' or ']'; you should probably check it also doesn't contain any other characters that can't be used as a table name, such as period and quotes.

Answer 7

You cannot parameterise the table name, you have to inject it into the command text.

What you can and should do is protect yourself against SQL injection by delimiting the name thus:

public static string Delimit(string name) {
    return "[" + name.Replace("]", "]]") + "]";
}

// Construct the command...
sc.CommandType = CommandType.Text;
sc.CommandText = "delete from " + Delimit(tableName);
sc.ExecuteNonQuery();

See here and here for more background info.