How to show related category of buttons in C# Windows Form?

Question

I'm building a C# Windows Form application that I would like to allow user to click the category button, and then show the related category product buttons. But I tried many times but it occurs error again. Image 1, Image 2

public void DisplayCategories()
{
    var cmd = new SqlCommand("SELECT DISTINCT category FROM Products ORDER BY category ASC", con);
    con.Open();
    try
    {
        var da = new SqlDataAdapter(cmd);
        var dt = new DataTable();
        da.Fill(dt);

        foreach (DataRow dr in dt.Rows)
        {
            var b = new Button();

            b.Size      = new Size(180, 36);
            b.BackColor = SystemColors.Control;
            b.FlatStyle = FlatStyle.Flat;
            b.UseVisualStyleBackColor = false;
            b.Text   = dr["category"].ToString();
            b.Click += new EventHandler(UpdateList);

            flpCategory.Controls.Add(b);
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error");
    }
    con.Close();
}

private void UpdateList(object sender, EventArgs e)
{
    var b = (Button)sender;
    SqlCommand subcmd = new SqlCommand("SELECT itemName FROM Products WHERE description = " + b.Text, con);

    var subda = new SqlDataAdapter(subcmd);
    var subdt = new DataTable();
    subda.Fill(subdt);

    foreach (var subdr in subdt.Rows)
    {
        var b2 = new Button();
        
        b2.Size      = new Size(180, 50);
        b2.BackColor = SystemColors.Control;
        b2.FlatStyle = FlatStyle.Flat;
        b2.UseVisualStyleBackColor = false;
        b2.Text = subdr["itemName"].ToString();
        
        flpItems.Controls.Add(b2);
    }
}

You don't have a column named Bakery. You should use parameters to avoid sql injection and formatting issues. — LarsTech, May 03 '16 at 19:12
But I already have the column named Bakery in my database. http://i.stack.imgur.com/viT2g.png — Clement, May 03 '16 at 19:18
Without quotes around your b.Text, the query thinks it's looking at a column. Hence, always use parameters. BTW, you don't have a *column* named Bakery, you have *data*. — LarsTech, May 03 '16 at 19:19

score 1 · Answer 1 · edited May 23 '17 at 12:07

When you want to search a field of type text like your description field then you need to put the value to search for between single quotes.

SELECT itemName FROM Products WHERE description = 'Bakery'

However this would be wrong because the best way to create this query is through a parameterized approach

string cmdText = "SELECT itemName FROM Products WHERE description = @desc";
Button b = (Button)sender;
SqlCommand subcmd = new SqlCommand(cmdText, con);
subCmd.Parameters.Add("@desc", SqlDbType.NVarChar).Value = b.Text;
SqlDataAdapter subda = new SqlDataAdapter(subcmd);
DataTable subdt = new DataTable();
subda.Fill(subdt);

A parameterized query avoids the Sql Injection hack and remove the need to check if your string value contains a single quote. (Syntax error without doubling the quote)

How to show related category of buttons in C# Windows Form?

1 Answers1