comboBox and database

[vinnijain]

Hi,In my application ,there is a combobox in which classname appears .
Now I want that for each selected classname from combobox,I want that user could add sections according to his wish .
Like if user selects class12 and he wants to add 4 sections(A,B,C,D) .he should add "A" in "section name" click on add and "A" should display in table below.
in this I want that user could enter section according to his wish.
After that when user selects any classname from combobox1 the the corresponding sections should
display in combobox2.similarly for each selection of classname corresponding sections should appear
in another combobox...........
The database which I am using is MS Access
Kindly help me..........

you can see the thumbnail attached here

[Geekitygeek]

Have you already written a database to store the class/section combinations?

[vinnijain]

Have you already written a database to store the class/section combinations?

Till now I have made two tables in database one for classname and another for section.
I could add classname in combobox but for each classname I could not add sections. And if sections are added then for each classname the corresponding sections should display in combobox2

[Geekitygeek]

i think i see what you want. One thing, can the same section be added to more than one class? eg, Class 1 - Section A and Class 2 - Section A...would they be the same section or two different sections with the same name?

[vinnijain]

Thanks for replying.............

Yes, the same sectionname can be added to more than one class?
eg, Class 1 - Section A and Class 2 - Section A.
Two different classes can have same section name but they should be different.

[Geekitygeek]

Im assuming each table has a primary key. You will need a table to join the Classes to Sections if you dont already have it.

You can set a datatable as the datasource for a combobox. You can do it through the designer or in code. I tend to do it manually:

DBDataSetTableAdapters.ClassesTableAdapter classes = new DBDataSetTableAdapters.ClassesTableAdapter();
            //GetData() sql: SELECT ClassName, ClassID FROM Classes ORDER BY ClassName
            DataTable dt = Classes.GetData();
            cmbClasses.DataSource = dt;
            cmbClasses.DisplayMember = "ClassName";
            cmbClasses.ValueMember = "ClassID";
            cmbClasses.SelectedIndex = -1;

I use table adapters to populate the datatable, but sql commands etc work just as well.

You can then bind the second combobox when a value is selected in the first:

private void cmbClasses_SelectedIndexChanged(object sender, EventArgs e)
        {
            decimal ClassID = cmbClasses.SelectedValue;
            
            DBDataSetTableAdapters.SectionsTableAdapter sections = new DBDataSetTableAdapters.SectionsTableAdapter();
            DataTable dt = new DataTable();
            //GetData() sql: 
            //SELECT SectionName, SectionID 
            //FROM Sections JOIN ClassSections 
            //ON Sections.SectionID = ClassSections.SectionID         
            //WHERE ClassSections.ClassID = @ClassID
            //ORDER BY ClassName
            dt = sections.GetData(ClassID);
            cmbSections.DataSource = dt;
            cmbSections.DisplayMember = "SectionsName";
            cmbSections.ValueMember = "SectionsID";
            cmbSections.SelectedIndex = -1;
        }

[vinnijain]

Ryshad,
I have made a class for oledb connection and the code which I am using to add class works fine but for section its giving syntax error for insert command.........
I don't know why its giving error...............

I have attached a zip folder in which there is oledbclass, code which I used for adding class(frmclass) and code for adding section which is giving error(frmsection).

[Geekitygeek]

what is the specific error message you are receiving?

[Geekitygeek]

try removing the space in this line cmdForm.Parameters.Add("@c_id", OleDbType .Numeric ).Value = this.newID; between OleDbType and .Numeric. Does that solve the problem?

EDIT - Nevermind, thats in the Class file : /

[vinnijain]

its giving same error.
Error messages are in thumbnail attached

[Geekitygeek]

The database error is usually a result of using a reserved word as a column/table name. "Section" is a reserved word. Either rename the table or enclose the table name in [] like: cmdForm.CommandText = "insert into [section](s_id,s_name) values (?,?)";

[vinnijain]

The database error is usually a result of using a reserved word as a column/table name. "Section" is a reserved word. Either rename the table or enclose the table name in [] like: cmdForm.CommandText = "insert into [section](s_id,s_name) values (?,?)";

Thanks its works ...............
Just tell how can I link the combobox of classname and section..........So that when I select any classname,then I could add section for that particular class........

[Geekitygeek]

i showed you how to do that already. In the SelectionChanged event handler for the Class combobox you need to select all sections that match that class and add them to the Section Combobox. If you have a table that links Classes and Sections then it looks something like this in SQL:

SELECT SectionName, SectionID            
FROM Sections JOIN ClassSections             
ON Sections.SectionID = ClassSections.SectionID                     
WHERE ClassSections.ClassID = @ClassID            
ORDER BY ClassName

[vinnijain]

Hi Ryshad,
I am using following code and I was able to add sections:

//adding section corresponding to class
 clsCon.cmdOpen(cmdForm);
                    cmdForm.CommandText = "insert into [section](c_id,sec_id,sec_name) values (?,?,?)";
                    cmdForm.Parameters.Add("@c_id", OleDbType.Numeric).Value = this.com_sName.SelectedValue;
                    cmdForm.Parameters.Add("@sec_id", OleDbType.Numeric).Value = this.newID;
                    cmdForm.Parameters.Add("@sec_name", OleDbType.Char).Value = this.txt_Sname.Text.Trim();
                    clsCon.cmdClose(cmdForm);
                    clsmyFunction.setMessageBox("Data Save Successfuly", 1);


private void btn_Add_Click(object sender, EventArgs e)
        {
            section_ID();
            save_Section();
            lstv_Function();
            this.txt_Sname.Text = "";
        }

//display section on selecting class from combo     
   private void section_ID()
        {
            try
            {

                int myID;
                clsCon.connCheck();
                OleDbCommand cmddr = new OleDbCommand("select max(sec_id) as ids from [section]" + this.com_sName.SelectedValue + "", clsCon.cn);
                OleDbDataReader dr = cmddr.ExecuteReader();

                while (dr.Read())
                {
                    string strid = dr["ids"].ToString();
                    if (strid == "")
                    {
                        newID = "1";
                    }
                    else
                    {
                        myID = Convert.ToInt32(dr["ids"]) + 1;
                        newID = myID.ToString();
                    }

                }
                dr.Close();
                clsCon.cn.Close();
                cmddr.Dispose();
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
            }
        }
        private void comboClass_Load()
        {
            this.clsCon.comboFill(this.com_sName, "select * from class", "class", "c_name", "c_id");
           
        }

Problem is that now on another form i am having 3 combos for class,section,subject respectively. And there is a textbox and button by which we could add student for any particular class-section-subject combination selected from combos.
How can I do this by modifications in my above code......

[Geekitygeek]

When you say you add a student to any class-section-subject combination, does that include combinations where one or more values is empty? eg just Class1, or Class1-Section1.

Im assuming you have set up subjects the same way you set up sections, where a subject belongs to a single section. In that case you just need to link a student to a subject the same way.

So when user selects class, populate sections. When they select section, populate subjects. When they select subject, enable entry of student name. When they click button, add student the same way you added the sections, with a link between subject and student.

[vinnijain]

When you say you add a student to any class-section-subject combination, does that include combinations where one or more values is empty? eg just Class1, or Class1-Section1.

NO!!! in combination none of the values are empty. When he selects class,the section combo must automatically populate with corresponding section , and then in subject combo must be populated with corresponding subjects.......So one or more values will not be empty. Eg: Class1-Section1-subjetct1

I do not know how the succeeding combos will automatically populate on selection of value from preceeding combo and add the students accordingly.....
Can you modify my given code and help me out.........