System.Data.SqlClient.SqlException: Incorrect syntax near "="

Question

I try am trying to build a function that populates a table when given the name of the table and what parameter to order it by.

I think I am just making a syntax error in my SQL command but I can't find it. Please help.

public DataTable populateTable(string tableName, string orderByParameter)
{
    DataTable table = new DataTable();
    string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
    string cmdString = "SELECT * FROM (value = @tbl) ORDER BY (parameter = @obp) DESC";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = cmdString;
            cmd.Parameters.AddWithValue("@tbl", tableName);
            cmd.Parameters.AddWithValue("@obp", orderByParameter);
            using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
            {
                ad.Fill(table);
            }
        }
        try

        {
            GridView1.DataSource = table;
            GridView1.DataBind();
            return table;
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
            return null;
        }
    }
}

Nisarg Shah · Accepted Answer · 2017-11-26 06:13:42Z

2

You can't have variables in table name or in 'order by' clause.

You could build the query dynamically as:

string cmdString = "SELECT * FROM [" + tableName + "] ORDER BY " + orderByParameter +" DESC";

With this you won't need to add the parameters @tbl and @obp to the command.

Note that this runs into SQL injection related vulnerabilities. So you shouldn't do this unless you are absolutely certain that the table with given name exists, and the orderByParameter is a valid expression.

answered Nov 26, 2017 at 6:13

Nisarg Shah

14.6k6 gold badges38 silver badges58 bronze badges

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

1 Comment

Warstra Over a year ago

Thank you for relieving my head scratching for the night.

Collectives™ on Stack Overflow

System.Data.SqlClient.SqlException: Incorrect syntax near "="

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related