CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Hybrid View

  1. #1
    Join Date
    Mar 2009
    Posts
    2

    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
    Last edited by alephNull; March 15th, 2009 at 09:21 PM.

  2. #2
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    4,538

    Re: Populate ComboBox from db, Select RecordID

    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
    Last edited by JonnyPoet; March 15th, 2009 at 01:16 PM.
    Jonny Poet

    To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
    Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
    If anyone felt he has got help, show it in rating the post.
    Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
    My latest articles :
    Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

  3. #3
    Join Date
    Mar 2009
    Posts
    2

    Re: Populate ComboBox from db, Select RecordID

    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
    Last edited by alephNull; March 17th, 2009 at 08:49 PM.

  4. #4
    Join Date
    Mar 2005
    Location
    Vienna, Austria
    Posts
    4,538

    Re: Populate ComboBox from db, Select RecordID

    Quote Originally Posted by alephNull View Post
    ....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
    Jonny Poet

    To be Alive is depending on the willingsness to help others and also to permit others to help you. So lets be alive. !
    Using Code Tags makes the difference: Code is easier to read, so its easier to help. Do it like this: [CODE] Put Your Code here [/code]
    If anyone felt he has got help, show it in rating the post.
    Also dont forget to set a post which is fully answered to 'resolved'. For more details look to FAQ's about Forum Usage. BTW I'm using Framework 3.5 and you ?
    My latest articles :
    Creating a Dockable Panel-Controlmanager Using C#, Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured