none
combo boxes and datagridview RRS feed

  • Question

  • I have 3 tables in a SQL database to log music CDs. Artist, Album and tracks. I have set up the foreign keys.
    I can fill the Artist combo box like this:

    musiccdsDataContext db = new musiccdsDataContext();
                var queryArtist = from c in db.Artists
                                  select new
                                  {
                                      Name = c.ArtistName,
                                      ID = c.ArtistsID
                                  };

                this.cboArtists.DataSource = queryArtist;
                this.cboArtists.DisplayMember = "Name";
                this.cboArtists.ValueMember = "ID";

    but I cannot fill the Album combo box with just the data according to the selected index of the Artist combo box. This means I cannot fill the datagridview with tracks from the selected album combo box. I could do it with a dataset, tableadapter etc but with Linq I am really struggling.
    Still trying to program
    Wednesday, February 3, 2010 6:45 AM

All replies

  • Hi,
    Plase have a look at another post where you asked same question, to see whether that reply can help, if you have any other concerns, please feel free to let us know. 
    Sincerely,
    Eric
    MSDN Subscriber Support in Forum
    If you have any feedback of our support, please contact msdnmg@microsoft.com.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, February 5, 2010 7:15 AM
  • Hello Eric, yes, sorry about asking the same question twice but as the first attempt was in the Archived Forums Forums > LINQ Project General > I thought I had better link to the latest forum. My mistake. If I may, I will answer your reply here.

    I have advanced my projectt. I can now fill the albums combo box and the datagridview. This is what I did. Since the combo box selectedValue is not the same as the ArtistID in the data base, I used the selectedItem from the combo box and then manipulated the returned object to retain the correct database ArtisitID. The same problem existed for the Album list to the datagridview. I used the same method for extracting the AlbumID from the selected album combo box.

    I have most probably gone around the houses to find the solution but it works. I just have to find out now how to modify, update and add a new artist and album. I have posted my code below for you to look at. Be kind and don't take the micky too much :-)

         public partial class frmMain : Form
        {

            public frmMain()
            {
                InitializeComponent();
            }

            private void Main_Load(object sender, EventArgs e)
            {           
                InitaliseControls();
            }

            private void InitaliseControls()
            {
                 Initialise();
            }

            private void UpdateAlbumList(object sender, EventArgs e)
            {
                // variables to hold artist and album IDs
                int selectedArtistID, selectedAlbumID = -1;

                // instance of linq database class
                musiccdsDataContext db = new musiccdsDataContext();

                // get selected ArtistID from combo box
                selectedArtistID = IDNumber(cboArtists.SelectedItem.ToString());

                // fill combo box with albums
                var queryAlbum = from d in db.Albums
                                 from c in db.Artists
                                 where d.ArtistID == c.ArtistsID && d.ArtistID == selectedArtistID
                                 select new
                                 {
                                     Name = d.AlbumName,
                                     ID = d.AlbumID
                                 };

                this.cboAlbums.DataSource = queryAlbum;
                this.cboAlbums.DisplayMember = "Name";
                this.cboAlbums.ValueMember = "ID";

                // get selected albumID from combo box
                selectedAlbumID = IDNumber(cboAlbums.SelectedItem.ToString());

                //fill datagridview with tracks
                var queryTracks = from t in db.Tracks
                                  from d in db.Albums
                                  where t.AlbumID == d.AlbumID && d.AlbumID == selectedAlbumID
                                  select new
                                  {
                                      Name = t.TrackName
                                  };
                this.dgvTrackList.DataSource = queryTracks;
                dgvTrackList.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;          
            }

            private void Initialise()
            {
                // variables to hold artist and album IDs
                int selectedArtistID, selectedAlbumID = -1;

                // instance of linq database class
                musiccdsDataContext db = new musiccdsDataContext();

                int numberCDs = db.Albums.Count();
                lblNumCDs.Text = "You currently have " + numberCDs.ToString() + " music albums.";

                // fill combo box with Artists
                var queryArtist = from c in db.Artists
                                  orderby c.ArtistName ascending
                                  select new
                                  {
                                      Name = c.ArtistName,
                                      ID = c.ArtistsID
                                  };

                this.cboArtists.DataSource = queryArtist;
                this.cboArtists.DisplayMember = "Name";
                this.cboArtists.ValueMember = "ID";

                // get selected ArtistID from combo box
                selectedArtistID = IDNumber(cboArtists.SelectedItem.ToString());

                // fill combo box with albums
                var queryAlbum = from d in db.Albums
                                 from c in db.Artists
                                 where d.ArtistID == c.ArtistsID && d.ArtistID == selectedArtistID
                                 select new
                                 {
                                     Name = d.AlbumName,
                                     ID = d.AlbumID
                                 };

                this.cboAlbums.DataSource = queryAlbum;
                this.cboAlbums.DisplayMember = "Name";
                this.cboAlbums.ValueMember = "ID";

                // get selected albumID from combo box
                selectedAlbumID = IDNumber(cboAlbums.SelectedItem.ToString());

                //fill datagridview with tracks
                var queryTracks = from t in db.Tracks
                                  from d in db.Albums
                                  where t.AlbumID == d.AlbumID && d.AlbumID == selectedAlbumID
                                  select new
                                  {
                                      Name = t.TrackName
                                  };
                this.dgvTrackList.DataSource = queryTracks;
                dgvTrackList.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            }

            //return an integer ID from a string
            private int IDNumber(string currentName)
            {
                int x, ID;
                x = currentName.IndexOf("=", 8);
                currentName = currentName.Remove(0, x + 2);
                currentName = currentName.Remove(currentName.Length - 2, 2);
                ID = Convert.ToInt32(currentName);
                return ID;
            }
        }
    }

    Still trying to program
    Friday, February 5, 2010 7:15 PM