SQL Parameter as table doesn't work [closed]

Question

Closed. This question needs debugging details. It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

Closed 9 years ago.

Improve this question

I want to return a DataTable with this method but SqlParameter is not working properly.

private DataTable GetTable(string tableName)
{
        const string queryString = "SELECT * FROM @TABLE";

        SqlCommand sqlCommand = new SqlCommand(queryString, _sqlConnection);

        SqlParameter sqlParameter = new SqlParameter("@TABLE", SqlDbType.Text)
        {
            Value = tableName
        };

        sqlCommand.Parameters.Add(sqlParameter);

        _sqlConnection.Open();

        SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
        DataTable dataTable = new DataTable();

        dataAdapter.Fill(dataTable);

        _sqlConnection.Close();
        dataAdapter.Dispose();

        dataGridViewTable.DataSource = dataTable;
        dataGridViewTable.AutoResizeColumns();

        return dataTable;
}

I am sure connection is successful. Another method is working. This one doesn't. It throws a SqlException.

Make sure to provide minimal reproducible example inline in the post as code. — Alexei Levenkov
– Alexei Levenkov, Commented Feb 24, 2016 at 17:44
What does “not working properly” mean? It throws an exception? Show it to us. It gives the wrong answer? What does it give? What did you expect? — Dour High Arch
– Dour High Arch, Commented Feb 24, 2016 at 17:49
There should be duplicate on using parameter to replace table name (I can't vote to close, jsut wait till someone copy-pastes answers here) - I.e. - stackoverflow.com/questions/17947736/… — Alexei Levenkov
– Alexei Levenkov, Commented Feb 24, 2016 at 17:49
Also, you do not need to open/close the connection when you use fill. You pass the connection object to the SqlCommand object, which you have already done. Fill open and closes automatically. — Randy
– Randy, Commented Feb 24, 2016 at 18:06

m-albert · Accepted Answer · 2016-02-24 22:41:34Z

2

You can't pass a table name as a parameter. Also, use using to easily close/dispose of disposable resources.

Try this...

private DataTable GetTable(string tableName)
{
    string queryString = "SELECT * FROM [" + tableName + "]";

    DataTable dataTable = new DataTable(tableName);
    using (SqlCommand sqlCommand = new SqlCommand(queryString, _sqlConnection))
    using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand))
    {
        _sqlConnection.Open();
        dataAdapter.Fill(dataTable);
        _sqlConnection.Close();
    }
    dataGridViewTable.DataSource = dataTable;
    dataGridViewTable.AutoResizeColumns();
    return dataTable;
}

EDIT: Added square brackets around table name in query to handle names with spaces.

edited Feb 24, 2016 at 22:41

answered Feb 24, 2016 at 17:56

m-albert

1,1008 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

SQL Parameter as table doesn't work [closed]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related