Display a list of item to CheckListBox when comboBox item is selected

Question

I have a windows form application. Inside the form, I have a ComboBox and a list box. When I select the comboBox, I want to display a list of item that the user can check in the checkListBox, I figured out how to bind data to comboBox part but I am not sure how to display a list of values so that the user can select in the checkListBox. Let say I have a list of items that stored in a SQL database call item_table, how can I display it to the checkListBox according to when I select from the comboBox? Code for reference will be appreciated. Thanks

For example,

let say the user select "Amy" from the comboBox, the checkListBox will display a list of item "item 1, item2, item3, item4".

when the user select "Brad" from the comboBox, it will display a list of item: "item2, item5, item10

etc

Here is my database table in (SQL) server

user_Detail
     In my user_Detail table , I have 10 user and each of them have a primary key (userId) and a column (userName);

item_Detail
    In my item_Detail table, I have 20 items and they also have a primary key (itemId) and a column (itemName) 

In the sql console, I inner join the two table (which I am not sure if I need to do the same in my SqlCommand in the code )

Here is my sql command in the console.

      select
          user_Detail.userId,
          user_Detail.userName,
          item_Detail.itemId,
          item_Detail.itemName
      from
          item_Detail
       INNER JOIN user_Detail ON user_Detail.userId = item_Detail.itemId

Here is my code

namespace Test {

    public partial class MainForm: Form {
        SqlConnection myConn;
        SqlCommand myCommand;
        SqlDataReader myReader;
        SqlDataAdapter myDa;
        DataTable dt;
        DataSet ds = new DataSet();

        public MainForm() {
            InitializeComponent();

            // loadComboBox
            loadComboBox();

        }

         //Connect to my db to fetch the data when the application load

        private void loadComboBox() {
     myConn = new SqlConnection("Server = localhost; Initial Catalog= dbName; Trusted_Connection = True");
            string query = "Select * from user_Detail";

            myCommand = new SqlCommand(query, myConn);

            try {
                myConn.Open();
                myReader = myCommand.ExecuteReader();
                string s = "<------------- Select an item ----------->";
                itemComboBox.Items.Add(s);
                itemComboBox.Text = s;

                while (myReader.Read()) {
                    //declare a string
                    object userId = myReader[userId"];
                    object userName = myReader["userName"];

                    //my comboBox named userComboBox
                    userComboBox.Items.Add(userName.ToString());
                }
            } catch (Exception ex) {
                MessageBox.Show(ex.Message);
            }
        }

        //Display some items here (this is my checkListBox
     private item_checkListBox(Object sender, EventArgs e){



     }


     private void load_item(){




    }

Answer 1

I wish this could help you.

First, i just want to fix your loadComboBox() because reading it might lead to confusion.

private void loadComboBox() {
        myConn = new SqlConnection("Server = localhost; Initial Catalog=dbName; Trusted_Connection = True");
        string query = "Select * from user_Detail";

        myCommand = new SqlCommand(query, myConn);

        try {
            myConn.Open();
            myReader = myCommand.ExecuteReader();
            string s = "<------------- Select an item ----------->";
            itemComboBox.Items.Add(s);
            itemComboBox.Text = s;

            while (myReader.Read()) {
                //declare a string
                string userId = myReader["userId"].toString();
                string userName = myReader["userName"].toString();

                //my comboBox named userComboBox
                userComboBox.Items.Add(userName);
            }

            myConn.Close();
        } catch (Exception ex) {
            MessageBox.Show(ex.Message);
        }
    }

Make sure to close sql connections after using it. Just open it again if youre going to use it.

Now, you added the userName of your users on your combobox.

Next lets create an event that will be fired whenever you choose from your combobox.

userComboBox.SelectedIndexChanged += (o,ev) => { ChangeCheckListItems(); };

The code above can be read as "if userComboBox changed selected index, call ChangeCheckListItems() method." Whenever you change selection, we will call the said method. You can put that code on your class constructor.

Now what does ChangeCheckListItems() method must contain.

private void ChangeCheckListItems(){
    myCheckListBox.Items.Clear();
    string selectedText = userComboBox.Text;

    switch(selectedText){
          case "Amy":
          AddItemsForAmy();
          break;
          case "Brad":
          AddItemsForBrad();
          break:
    }

}

So first, we make sure we clear the myCheckListBox before adding the items to avoid duplication since this method trigger every selection change.

Next we get the selected text from the userComboBox.

Then we will use a switch to choose what we will do depends on the selected userComboBox.

AddItemsForAmy() and AddItemsForBrad() are only example methods.

For example:

private void AddItemsForAmy(){

    myConn = new SqlConnection("Server = localhost; Initial Catalog=dbName         Trusted_Connection=true;"
    string query = "Select * from item_Detail where itemId % 2 = 0"
    myCommand = new SqlCommand(query, myConn);

    try{
      myConn.Open();
      myReader = myCommand.ExecuteReader();

      while(myReader.Read()){

      string itemName = myReader["itemName"].toString();
      myCheckListBox.Items.Add(itemName);
      }
      myConn.Close();
    }
    catch(SqlExcetion ex){
           MessageBox.Show(ex.Message);
    }
}

So in my example above, I selected all items with itemId that are even numbers. And then on while() part, I added those items to the checklistbox.

Its your choice on what items are you going to display for Amy,Brad and other possible users on you database. You can also use parameterized method for shorter solution. Hope this helps. Sorry if its so long.