Populate ComboBox from db, Select RecordID

I can populate the drop down list no problem. What I want to do is save the selected data into another table which is related via foreign key. So I want the list to show the text description(Name) but when it is selected I want the EventID to be saved.

Code:

---

        SqlConnection conn = new SqlConnection(@"
                server = .\sqlexpress;
                integrated security = true;
                database = HoopDataSample");

        string qryEvents = @"select EventID,Name from Events order by Name";

            try
            {
                conn.Open();

                //****Get Event Info to Fill Event Combo Box
                SqlCommand cmd = new SqlCommand(qryEvents, conn);
                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    comboBoxEvents.Items.Add(rdr[1]);  //rdr[0] is the EventID,rdr[1] is the Name
                }
                rdr.Close();

            }

---

Thanks

At first please kep forum rules using code tags because as you see format goes lost if you dont.#

Code:

---

SqlConnection conn = new SqlConnection(@"
server = .\sqlexpress;
integrated security = true;
database = HoopDataSample");
 
string qryEvents = @"select EventID,Name from Events order by Name";
 
try{
  conn.Open();
 
  //****Get Event Info to Fill Event Combo Box
  SqlCommand cmd = new SqlCommand(qryEvents, conn);
  SqlDataReader rdr = cmd.ExecuteReader();
 
  while (rdr.Read()){ 
      MyItem  myItem = new MyItem( rdr[0], rdr[1]);
      comboBoxEvents.Items.Add(myItem); //rdr[0] is the EventID,rdr[1] is the Name
    }
    rdr.Close();
 
  }
 
public class MyItem{
    private int _id;
    private string _item;
 
    public MyItem( int id, string item){
      _id = id;
      _item = item;
 
    }
 
    // properties
  public int ID{
      get {return _id;}
      set{ _id = value;}
  }
  public string Item {
    get { return _item;}
    set { _item = value;}
  }
 
  // This way the object in the combobox will be seen with its text
  public override string ToString(){
      return _item;
  }
}

---

In the selectedIndexChanged deegate you retriev the item as

Code:

---

  MyItem  myItem =  comboBoxEvents.SelectedItem as MyItem;
  if ( myItem != null){
      int id = myItem.ID;
      // whatever you want here
  }

---

BTW in a real code I also would make sure that neither rdr[0] nor rdr[1] was read as dbNull value by checkuÃ*ng before adding to the class. This was only done quick to give you the idea

Jonny,

Thanks for the excellent reply. I figured something a little more straighforward.

Code:

---

        // create query
        string qryEvents = @"select EventID,Name from Events order by Name";

        // create connection
        SqlConnection conn = new SqlConnection(@"
                server = .\sqlexpress;
                integrated security = true;
                database = HoopDataSample");

        // create Data Adapter & Data Set
        SqlDataAdapter da;
        DataSet ds;

            try
            {
                //****Get Event Info to Fill Event Combo Box
               
                da = new SqlDataAdapter(qryEvents, conn);

                ds = new DataSet();

                // fill data set
                da.Fill(ds, "eventList");

                // load the combobox
                comboBoxEvents.DataSource = ds;
                comboBoxEvents.DisplayMember = "eventList.Name";
                comboBoxEvents.ValueMember = "eventList.EventID";
          }

---

The tricky part was realizing that I had to use the 'dataSetName.columnName' format(ie eventList.Name) when setting the DisplayMember and ValueMember properties.

If I understand it correctly, I can actually combine queries and separate the results by using the Data Tables Collection and populate multiple combo boxes with one SQL Query. I will post that later when I get it to work.

Thanks again

Quote:

---

Originally Posted by alephNull

....If I understand it correctly, I can actually combine queries and separate the results by using the Data Tables Collection and populate multiple combo boxes with one SQL Query. I will post that later when I get it to work.

Thanks again

---

I thought about if I should change your design to dataset, datatable, but as you have already used Datareader I didn't. Sure if you have a lot of database access you can easily bind your database to your cotrols and as we are just talking about tips and tricks: I would not have the names of the adabase fields as strings in the program. What will happen if you are changing the database some years later. You would need to search everywhere in your program for this strings. I'm using the Propertys Resources String table for this and instead of having the names directly in the code I would do

Code:

---

comboBoxEvents.DisplayMember = Properties.Settings.Default.EventListName;
...

---

This way going on. So if you ever will have a need to change something on your database you only need to look into your properties Settings table and change the names there.
Additionally I wouldn't have my database access in the same layer as my combobox but instead having a datalayer where I get all my database access and in the view I would have the combobox.
Maybe read a bit about MVP paterrn or MVC pattern