CODEWITHSUNDEEP
sqlasp.netsql-server
Warstra
Warstra

Reputation: 13

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

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;
        }
    }
}

Upvotes: 1

Views: 640

Answers (1)

Nisarg Shah
Nisarg Shah

Reputation: 14541

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.

Upvotes: 2

Related Questions