C# SQL parameterized SQL Query

Question

I just keep on getting Syntax error when I used parameterized sql query.

public List<string> Cat(string product,string table)
{
    List<string> Products = new List<string>();
    Global global = new Global();
    string sql = "SELECT @prod FROM @tbl";
    MySqlConnection connection = new MySqlConnection(global.ConnectionString);
    MySqlCommand command = new MySqlCommand(sql, connection);
    command.Parameters.AddWithValue("@prod", product);
    command.Parameters.AddWithValue("@tbl", table);
    connection.Open();
    MySqlDataReader reader = command.ExecuteReader();
    if (reader.HasRows)
    {
        while (reader.Read())
            Products.Add(reader.GetString("@prod"));
    }
    connection.Close();
    return Products;
}

public List<string> CallProducts(string category)
{
    string table;
    string product;
    List<string> stacks = new List<string>();
    if (category == "Accessories")
    {
        product = "Accessories_Name";
        table = "tbl_accessories";
        stacks.AddRange(Cat(product, table).ToArray());                
    }
    else if (category == "Batteries")
    {
        table = "tbl_batteries";
    }
    else if (category == "Cotton")
    {
        table = "tbl_cotton";
    }
    else if (category == "Juices")
    {
        table = "tbl_juices";
    }
    else if (category == "Kits")
    {
        table = "tbl_kits";
    }
    else if (category == "Mods")
    {
        table = "tbl_mods";
    }
    else
    {
        table = "tbl_vapeset";
    }
    return stacks;
}

I just keep on getting SQL Syntax Error. It works if the table and the name is manually inputted rather than using parameters.

Hoping you can help.

Need for a project.

Thanks!

Pro Tip we cant see your screen, so copying the errors in is mandatory — TheGeneral
– TheGeneral, Commented Sep 30, 2018 at 8:20
Also public List<string> Cat(string product, string table) — TheGeneral
– TheGeneral, Commented Sep 30, 2018 at 8:21
Hi Pro Tip,You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''tbl_accessories'' at line 1. — Richard Pabalan
– Richard Pabalan, Commented Sep 30, 2018 at 9:02

Michał Turczyn · Accepted Answer · 2018-09-30 08:29:21Z

0

Correct use would be:

string sql = $"SELECT {product} FROM {table}";

Because table and column are not parameters.

Moreover, I would recommend using Command.Parameters.Add(...).Value(...), over Parameters.AddWithValue, since in first approach you can explicitly decide what datatype you want to pass and prevent SQL from guessing it.

answered Sep 30, 2018 at 8:29

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

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

3 Comments

Richard Pabalan Over a year ago

Hi, I tried using this but also gives the same error. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '} FROM {table}' at line 1

Richard Pabalan Over a year ago

Hey thanks for the tip :) it was such a big help :) we got it now

Michał Turczyn Over a year ago

If answer helped you, you should accept it (green check mark on the left) and optionally upvote it.

Collectives™ on Stack Overflow

C# SQL parameterized SQL Query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related