How to fill checkbox list with category and sub-category from DB

Question

0.00/5 (No votes)

See more:

hello,

how to fill checkbox list with category and sub-category from DB

this is my sql table 
<pre lang="SQL">SELECT
dbo.category_data.category_id,
dbo.category_data.category_name,
dbo.category_data.category_parent,
dbo.category_data.category_description,
dbo.category_data.category_image,
dbo.category_data.category_status

FROM [dbo].[category_data]

and this is my c# code i need help to complete it

C#

private void bind_cat_menu()
   {
       SqlConnection con= new SqlConnection();
       con.ConnectionString = ConfigurationManager.ConnectionStrings["QMALL"].ToString();
       con.Open();
       DataSet ds = new DataSet();
       DataTable dt = new DataTable();
       string sql = "Select * from (category_data]";
       SqlDataAdapter da = new SqlDataAdapter(sql, con);
       da.Fill(ds);
       dt = ds.Tables[0];
       DataRow[] drowparent = dt.Select("category_parent=" + 0);
       foreach (DataRow dr in drowparent)
       {

       }
       foreach (DataRow dr in dt.Select("category_parent>" + 0))
       {

       }

What I have tried:

private void bind_cat_menu()
    {
        SqlConnection con= new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["QMALL"].ToString();
        con.Open();
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        string sql = "Select * from (category_data]";
        SqlDataAdapter da = new SqlDataAdapter(sql, con);
        da.Fill(ds);
        dt = ds.Tables[0];
        DataRow[] drowparent = dt.Select("category_parent=" + 0);
        foreach (DataRow dr in drowparent)
        {
            
        }
        foreach (DataRow dr in dt.Select("category_parent>" + 0))
        {
            
        }

Posted 1-Mar-17 23:12pm

Eng Abdull A-ziz

Updated 2-Mar-17 1:51am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-03-02T01:51:00

Here is an example that gets the category names and status and populates the checkbox - you will need to adapt it to your own needs.
I've assumed that category_status equates to true or false - for example it might be a bit column where 0 = false and 1 = true.

I haven't faffed around with category and sub-category because you haven't really described the relationship and because the example lists all from the table, regardless of whether they have a "parent" or not.

One final thing to note - it is bad practice to use

SQL

Select * from (category_data]

Better is use the explicit column names that you need:

SQL

SELECT category_name, category_status FROM category_data

Here is a working example that populates a ComboBoxList and automatically sets the selected value based on category_status.

C#

private void BindCheckBoxList()
{

    var constr = ConfigurationManager.ConnectionStrings["ConnectToDB"].ConnectionString;
    using (var con = new SqlConnection(constr))
    {
        using (var cmd = new SqlCommand("SELECT category_name, category_status FROM category_data"))
        {
            using (var sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;

                using (var ds = new DataSet())
                {
                    sda.Fill(ds);
                    if (ds.Tables[0].Rows.Count <= 0) return;   //nothing to do if no data on the table
                    CheckBoxList1.DataSource = ds;
                    CheckBoxList1.DataTextField = "category_name";
                    CheckBoxList1.DataValueField = "category_name";
                    CheckBoxList1.DataBind();

                    var i = 0;
                    foreach (DataRow r in ds.Tables[0].Rows)
                    {
                        Debug.Print("{0} {1}", r.ItemArray[0],r.ItemArray[1]);
                        if (r.ItemArray[1].ToString().ToLower() == "true")
                            CheckBoxList1.Items[i].Selected = true;
                        i++;
                    }
                }
            }
        }
    }