Drill down combo boxes

Question

Hi Everyone I'm learning c# and i'm about to pull my hair out! I need to have one combo box populated from a database based on values selected in another combo box (also populated from a database). I have 2 tables (Courses and Students ) The 'Courses' Table contains CourseID  CourseTitle The 'Students' Table contains StudentID CourseID  FirstName Surname CourseID are both unique keys. I need to be able populate a 'Students' combobox with only students on the course selected in the 'Courses' dropdown box. (Both comboboxes are on the same form). My problem is I just cant do it!! I've spent the best part of 2 days looking for examples/tutorials etc but I have got a bit confused with creating datatables/datasets to fill the comboboxes etc. I want to try and do this in code only rather than using visual studio to create datasets etc so I can understand the process a bit more but the longer I spend failing the less it all makes sense! Can anyone point me to a really easy to follow basic example or perhaps even be kind enough to share any code you may already have so that I can attempt to 'doctor' it for my needs. I'm sure that once I can see this working in a simple project I will gain a better understanding but at the moment I just seem to be going backwards!! Many Thanks Sarah

Vulpes · Answer

There are surprisingly few books on Windows Forms programming which is a huge subject. However I'd recommend this one as I have an earlier edition myself: http://www.amazon.com/Windows-Forms-Programming-Microsoft-Development/dp/0321267966

Sarah Reynolds · Answer

Wow that worked perfect 1st time!! If I can ever get to the point where I can answer an issue like that off the top of my head I'll be extremely happy! Thank you both so much for your help on this issue! I had an idea of a windows app I could build but the whole thing depended on being able to do this process. I'm now going to read through all the completed code several times to get a better understanding before going any further. I think a good book is in order at the weekend so do you know of any good easy to follow books out there? Thanks again your help has been very much appreciated. Sarah.

Vulpes · Answer

One way to do it would be to populate a List with the studentID properties in the same order as they are added to the second combobox. When the selected index changes you can then read off the current student's ID from the list. The following is off the top of my head and so may not be quite right - changes to existing code are highlighted: namespace Combobox { public partial class Form1 : Form { string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings[dsn]; OleDbCommand com; OleDbDataAdapter oda; DataSet ds; string str; List studentIDs = new List (); string currentStudentID; public Form1() { InitializeComponent(); } private void Form1_Load(object sender EventArgs e) { comboBox1.Items.Add(Choose CourseID); OleDbConnection con = new OleDbConnection(ConnectionString); con.Open(); str = select * from Courses; com = new OleDbCommand(str con); OleDbDataReader reader = com.ExecuteReader(); while (reader.Read()) { comboBox1.Items.Add(reader[CourseID]); } comboBox1.SelectedIndex = 0; } private void comboBox1_SelectedIndexChanged(object sender EventArgs e) { comboBox2.Items.Clear(); OleDbConnection con = new OleDbConnection(ConnectionString); con.Open(); str = select * from Students where CourseID=' + comboBox1.Text.Trim() + '; com = new OleDbCommand(str con); OleDbDataReader reader = com.ExecuteReader(); studentIDs.Clear(); while (reader.Read()) { comboBox2.Items.Add(reader[FirstName].ToString()); studentIDs.Add(reader[studentID].ToString()); } comboBox2.SelectedIndex = 0; currentStudentID = studentIDs[0]; con.Close(); reader.Close(); } private void comboBox2_SelectedIndexChanged(object sender EventArgs e) { if (comboBox2.SelectedIndex > -1) { currentStudentID = studentIDs[comboBox2.SelectedIndex]; } else { currentStudentID = null; } } } }

Sarah Reynolds · Answer

Thanks Vulpes That worked great! I just have one more question I'm hoping you guys can help me with. When I select a course from the the students box does now list students on that course. When I click a student in the 2nd dropdown box is there anyway to get the studentID property associated with that student? I'm not sure if I somehow need to add it to the 1st 'Courses' dropdown box? Thanks Sarah

Vulpes · Answer

Just add this line at the end of Form1_Load: comboBox1.SelectedIndex = 0;

Sarah Reynolds · Answer

Hello again I managed to get Satyapriya's example working so thank you sooooo much for that!! I can't believe how much conflicting advice is out there for what seems to be a relatively simple task! I do have a slight issue with it however. The comboboxes are loaded with the correct data and switch perfectly but the combobox doesn't show anything until you click the dropdown arrow. Is there a way to automatically show the 1st item? Thanks Sarah

Sarah Reynolds · Answer

Hi Satyapriya This looks great but i'm having a few issues with the connection to the database. Im using sql2008 that came with visual studio. Should this work connecting to a sql server? Thanks so much for replying. Sarah PS:Abdullah Thank you also for your reply but I am creating a windows app (Sorry I should have mentioned this) so I dont think your method will help me with this issue but it has been useful with regard to the datasets etc so thank you for posting.

Abdullah Munshi · Answer

Try the following code. ============================================== using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.SqlClient; using System.Data; public partial class Default2 : System.Web.UI.Page { SqlConnection con = new SqlConnection(@Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True); protected void Page_Load(object sender EventArgs e) { if (!IsPostBack) { FillCourse(ddlCourse); } } protected void ddlCourse_SelectedIndexChanged(object sender EventArgs e) { ddlStudent.Items.Clear(); FillStudents(ddlStudentConvert.ToInt16(ddlCourse.SelectedValue)); } public void FillCourse(DropDownList ddl) { SqlCommand cmd = new SqlCommand(SELECT * FROM Course con); DataSet ds = new DataSet(); SqlDataAdapter ad = new SqlDataAdapter(cmd); ad.Fill(ds); ddl.DataTextField = CourseTitle; ddl.DataValueField = CourseID; ddl.DataSource = ds; ddl.DataBind(); } public void FillStudents(DropDownList ddl int courseid) { SqlCommand cmd = new SqlCommand(SELECT * FROM Students WHERE CourseID=' + courseid + ' con); DataSet ds = new DataSet(); SqlDataAdapter ad = new SqlDataAdapter(cmd); ad.Fill(ds); ddl.DataTextField = FirstName; ddl.DataValueField = StudentID; ddl.DataSource = ds; ddl.DataBind(); } } =========================================== Default2.aspx code =========================================== Course : AutoPostBack=True onselectedindexchanged=ddlCourse_SelectedIndexChanged> --PLEASE SELECT-- Students : AppendDataBoundItems=True> --PLEASE SELECT--

Satyapriya Nayak · Answer

Hi Sarah Try this... Run the attachments. using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; namespace Combobox { public partial class Form1 : Form { string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings[dsn]; OleDbCommand com; OleDbDataAdapter oda; DataSet ds; string str; public Form1() { InitializeComponent(); } private void Form1_Load(object sender EventArgs e) { comboBox1.Items.Add(Choose CourseID); OleDbConnection con = new OleDbConnection(ConnectionString); con.Open(); str = select * from Courses; com = new OleDbCommand(str con); OleDbDataReader reader = com.ExecuteReader(); while (reader.Read()) { comboBox1.Items.Add(reader[CourseID]); } } private void comboBox1_SelectedIndexChanged(object sender EventArgs e) { comboBox2.Items.Clear(); OleDbConnection con = new OleDbConnection(ConnectionString); con.Open(); str = select * from Students where CourseID=' + comboBox1.Text.Trim() + '; com = new OleDbCommand(str con); OleDbDataReader reader = com.ExecuteReader(); while (reader.Read()) { comboBox2.Items.Add(reader[FirstName].ToString()); } con.Close(); reader.Close(); } } } Thanks If this post helps you mark it as answer