MySQL: Marks Input Design (Used in IComis)

To implement this User Interface:




Scenario:

1. Form loads the Program Combo Box with all the
available Programs
2. User selects a program and it loads the Session
Combo Box
3. User clicks on the Load Subjects Button, and it
loads the Subjects Combo Box.
4. User clicks on Load Students Button, and it
loads the Students Combo Box.


This loads the Program Combo Box:

SELECT name FROM program ORDER BY name;

This loads the Session Combo Box:

SELECT DISTINCT session FROM ssession;

This loads the Subjects Combo Box when the
Load Subjects Button is pressed:

SELECT DISTINCT code FROM ssubject
WHERE sessionid IN
(SELECT sessionid FROM ssession WHERE session='JAN 2009'
AND studentid IN (SELECT studentid
FROM student WHERE program='DICT'));


This loads the Students Combo Box when the
Load Students Button is pressed:

SELECT studentid,name FROM student
WHERE studentid IN
(SELECT studentid FROM ssession
WHERE session='JAN 2009' AND sessionid IN
(SELECT sessionid FROM ssubject
WHERE code='CSC1b')) AND program='DICT'
ORDER BY name;


Sample Java Code:

cbxStudent.removeAllItems();
try {
//---init cbxStudent---
statement = connection.createStatement();
String sSQL =
"SELECT studentid,name FROM student "
+ "WHERE studentid IN "
+ "(SELECT studentid FROM ssession "
+ "WHERE session='"
+ cbxSession.getSelectedItem().toString().trim()
+ "' AND sessionid IN "
+ "(SELECT sessionid FROM ssubject "
+ "WHERE code='"
+ cbxSubject.getSelectedItem().toString().trim()
+ "')) AND program='"
+ cbxProgramName.getSelectedItem().toString().trim() +"'"
+ " ORDER BY name ";

rsStudent = statement.executeQuery(sSQL);


while (rsStudent.next()) {
cbxStudent.addItem(rsStudent.getString("name"));
}
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "btnLoadStudents: " + e.toString());
//System.exit(1);
} finally {
try {
statement.close();
} catch (Exception e) {
}
}