private void DetailForm_Load(object sender, EventArgs e) { // Save button will be disable bt_Save.Enabled = false; // Initialize database connection cmd.Connection = connection; /* * Put Term data to Form */ cmd.CommandText = string.Format(@"SELECT * FROM TermTable WHERE TermID = {0}", m_Term); try { connection.Open(); reader = cmd.ExecuteReader(); if (reader.HasRows) while (reader.Read()) { num_Term.Text = reader[1].ToString(); lb_YearValue.Text = reader[2].ToString(); } connection.Close(); } catch (Exception ex) { MessageBox.Show(@"Message6: " + ex.Message.ToString(), @"Error"); connection.Close(); } // Read Grade table and add grade to cb_ABRSMgrade try { cmd.CommandText = string.Format(@"SELECT * FROM ABRSMGrade WHERE ABRSMID = {0}", m_studentInfo.m_ABRSMGradeID); connection.Open(); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { var tempt = new ComboBoxItem(reader[0], reader[1].ToString()); cb_ABRSMgrade.Items.Add(tempt); } connection.Close(); cb_ABRSMgrade.SelectedIndex = 0; } connection.Close(); } catch (Exception ex) { MessageBox.Show(@"Message3: " + ex.Message.ToString(), @"Error"); connection.Close(); } //Add student name to cb_StudentName cb_StudentName.Items.Add(new ComboBoxItem(m_studentInfo.m_studentID, m_studentInfo.m_studentName)); cb_StudentName.SelectedIndex = 0; tb_Instrument.Text = m_studentInfo.m_Instrument.ToString(); tb_TeacherName.Text = m_studentInfo.m_TeacherName.ToString(); tb_SchoolName.Text = m_studentInfo.m_EnsemblesPartition.ToString(); // Read Grade table and add to List for using many time var lt_Grade = new List<ComboBoxItem>(); try { cmd.CommandText = @"SELECT * FROM Grade"; connection.Open(); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { lt_Grade.Add(new ComboBoxItem(reader[0], reader[1].ToString(), reader[2])); } } connection.Close(); } catch (Exception ex) { MessageBox.Show("Message: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); connection.Close(); } // Add Grade to cb_MusicalLiteracy, cb_Listening, cb_Performing, cb_Musicianship, cb_Technique, cb_Effort foreach (var str_grade in lt_Grade) { cb_MusicalLiteracy.Items.Add(str_grade); cb_Performing.Items.Add(str_grade); cb_Listening.Items.Add(str_grade); cb_Musicianship.Items.Add(str_grade); cb_Technique.Items.Add(str_grade); cb_Effort.Items.Add(str_grade); } // Read Grade from database and put cb_MusicalLiteracy, cb_Listening, cb_Performing, cb_Musicianship, cb_Technique, cb_Effort to correct database try { cmd.CommandText = string.Format( @"SELECT GradeTable.ValueID, GradeTable.GradeID, Grade.GradeName, Grade.PercentGrade FROM GradeTable, Grade WHERE GradeTable.StudentID = {0} AND GradeTable.Term = {1} AND GradeTable.GradeID = Grade.GradeID AND GradeTable.ABRSMID = {2}", m_studentInfo.m_studentID, m_Term, m_studentInfo.m_ABRSMGradeID); connection.Open(); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { /*TODO: Need to enhanced add to commbo box. Do not hard code value ID for cb_Musicalship, etc...*/ switch ((int) reader[0]) { case 1: { cb_MusicalLiteracy.SelectedIndex = cb_MusicalLiteracy.FindStringExact(reader[2].ToString()); break; } case 2: { cb_Listening.SelectedIndex = cb_Listening.FindStringExact(reader[2].ToString()); break; } case 3: { cb_Technique.SelectedIndex = cb_Technique.FindStringExact(reader[2].ToString()); break; } case 4: { cb_Musicianship.SelectedIndex = cb_Musicianship.FindStringExact(reader[2].ToString()); break; } case 5: { cb_Performing.SelectedIndex = cb_Performing.FindStringExact(reader[2].ToString()); break; } case 6: { cb_Effort.SelectedIndex = cb_Effort.FindStringExact(reader[2].ToString()); break; } default: break; } } } connection.Close(); } catch (Exception ex) { MessageBox.Show(@"Message: " + ex.Message.ToString(), @"Error", MessageBoxButtons.OK, MessageBoxIcon.Error); connection.Close(); throw; } // Load command of Target and put to tb_Target cmd.CommandText = string.Format( @"SELECT Target FROM DatabaseTable WHERE StudentID = {0} AND Term = {1} AND ABRSMID = {2}", m_studentInfo.m_studentID, m_Term, m_studentInfo.m_ABRSMGradeID); try { connection.Open(); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { tb_TargetComment.Text = reader[0].ToString(); } } connection.Close(); } catch (Exception exception) { MessageBox.Show(@"Message: " + exception.Message.ToString(), @"Error", MessageBoxButtons.OK, MessageBoxIcon.Error); connection.Close(); throw; } }
private void cb_ABRSMGrade_SelectedIndexChanged(object sender, EventArgs e) { // Read from database to show Student name in list // Read Student Infor and load to lb_StudentNameBaseOnABRSMGrade // Clear lb_StudentName.... lb_studentNameBaseOnABRSM.Items.Clear(); tableLayoutScoreSummary.Controls.Clear(); tableLayoutScoreSummary.RowCount = 0; try { //cmd.CommandText = string.Format("SELECT DISTINCT Student.StudentID, Student.Name FROM DatabaseTable INNER JOIN Student ON DatabaseTable.StudentID = Student.StudentID INNER JOIN ABRSMGrade ON DatabaseTable.ABRSMID = {0}", (cb_ABRSMGrade.SelectedItem as ComboBoxItem).m_Value); _cmd.CommandText = string.Format("SELECT DISTINCT StudentID, Name FROM Student WHERE ABRSMID = {0}", (cb_ABRSMGrade.SelectedItem as ComboBoxItem).MValue); _connection.Open(); _reader = _cmd.ExecuteReader(); if (_reader.HasRows) { while (_reader.Read()) { ComboBoxItem tempt = new ComboBoxItem(_reader[0], _reader[1].ToString()); lb_studentNameBaseOnABRSM.Items.Add(tempt); } _connection.Close(); lb_studentNameBaseOnABRSM.SelectedIndex = 0; } _connection.Close(); } catch (Exception ex) { MessageBox.Show(@"Message1: " + ex.Message.ToString(), @"Error", MessageBoxButtons.OK, MessageBoxIcon.Error); _connection.Close(); } }
private void ScoreManagement_Load(object sender, EventArgs e) { // Connection String _connection.ConnectionString = ConnectionStringToDatabase.getConnectionString(); _cmd.Connection = _connection; _connection.Close(); cb_ABRSMGrade.DropDownStyle = ComboBoxStyle.DropDownList; tableLayoutScoreSummary.AutoScroll = true; tableLayoutScoreSummary.AutoSize = true; // Read ABRSM Grade infor load to cb_ABRSMGrade try { _cmd.CommandText = @"SELECT * from ABRSMGrade"; _connection.Open(); _reader = _cmd.ExecuteReader(); if (_reader.HasRows) { while (_reader.Read()) { ComboBoxItem tempt = new ComboBoxItem(_reader[0], _reader[1].ToString()); cb_ABRSMGrade.Items.Add(tempt); } _connection.Close(); cb_ABRSMGrade.SelectedIndex = 0; } _connection.Close(); } catch (Exception ex) { MessageBox.Show(@"Message3: " + ex.Message.ToString(), @"Error"); _connection.Close(); } // Read Student Infor and load to lb_StudentNameBaseOnABRSMGrade try { lb_studentNameBaseOnABRSM.Items.Clear(); //cmd.CommandText = string.Format("SELECT DISTINCT Student.StudentID, Student.Name FROM DatabaseTable INNER JOIN Student ON DatabaseTable.StudentID = Student.StudentID INNER JOIN ABRSMGrade ON DatabaseTable.ABRSMID = {0}", (cb_ABRSMGrade.SelectedItem as ComboBoxItem).m_Value); _cmd.CommandText = string.Format("SELECT DISTINCT StudentID, Student.Name FROM Student WHERE ABRSMID = {0}", ((ComboBoxItem) cb_ABRSMGrade.SelectedItem).MValue); _connection.Open(); _reader = _cmd.ExecuteReader(); if (_reader.HasRows) { while (_reader.Read()) { var tempt = new ComboBoxItem(_reader[0], _reader[1].ToString()); lb_studentNameBaseOnABRSM.Items.Add(tempt); } _connection.Close(); lb_studentNameBaseOnABRSM.SelectedIndex = 0; } _connection.Close(); } catch (Exception ex) { MessageBox.Show(@"Message2: " + ex.Message.ToString(), @"Error"); _connection.Close(); } }
private void AddScore_Load(object sender, EventArgs e) { // Connection String connection.ConnectionString = ConnectionStringToDatabase.getConnectionString(); cmd.Connection = connection; connection.Close(); cb_ABRSMgrade.DropDownStyle = ComboBoxStyle.DropDownList; cb_YearofTerm.DropDownStyle = ComboBoxStyle.DropDownList; cb_StudentName.DropDownStyle = ComboBoxStyle.DropDownList; cb_Effort.DropDownStyle = ComboBoxStyle.DropDownList; cb_Listening.DropDownStyle = ComboBoxStyle.DropDownList; cb_MusicalLiteracy.DropDownStyle = ComboBoxStyle.DropDownList; cb_Musicianship.DropDownStyle = ComboBoxStyle.DropDownList; cb_Performing.DropDownStyle = ComboBoxStyle.DropDownList; cb_Technique.DropDownStyle = ComboBoxStyle.DropDownList; /* * Add several year in to Year combobox * - Add one smaller and 4 years late with current year */ for (var i = -1; i < 3; ++i) { cb_YearofTerm.Items.Add(DateTime.Now.Year + i); } cb_YearofTerm.SelectedIndex = cb_YearofTerm.FindStringExact(DateTime.Now.Year.ToString()); /* * - Read info this student in database, if exist, increase Term of this student * - Asume Term has increase one by one */ try { cmd.CommandText = string.Format(@"SELECT Term FROM DatabaseTable WHERE StudentID = {0} AND ABRSMID = {1}", m_studentInfo.m_studentID, m_studentInfo.m_ABRSMGradeID); connection.Open(); reader = cmd.ExecuteReader(); var count = 1; if (reader.HasRows) { while (reader.Read()) { count++; if (count == 6) count = 1; } } num_Term.Text = count.ToString(); connection.Close(); } catch (Exception ex) { MessageBox.Show(@"Message3: " + ex.Message.ToString(), @"Error"); connection.Close(); } // Read Grade table and add grade to cb_ABRSMgrade try { cmd.CommandText = string.Format(@"SELECT * FROM ABRSMGrade WHERE ABRSMID = {0}", m_studentInfo.m_ABRSMGradeID); connection.Open(); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { ComboBoxItem tempt = new ComboBoxItem(reader[0], reader[1].ToString()); cb_ABRSMgrade.Items.Add(tempt); } connection.Close(); cb_ABRSMgrade.SelectedIndex = 0; } connection.Close(); } catch (Exception ex) { MessageBox.Show(@"Message3: " + ex.Message.ToString(), @"Error"); connection.Close(); } //Add student name to cb_StudentName cb_StudentName.Items.Add(new ComboBoxItem(m_studentInfo.m_studentID, m_studentInfo.m_studentName)); cb_StudentName.SelectedIndex = 0; tb_Instrument.Text = m_studentInfo.m_Instrument.ToString(); tb_TeacherName.Text = m_studentInfo.m_TeacherName.ToString(); tb_SchoolName.Text = m_studentInfo.m_EnsemblesPartition.ToString(); // Read Grade table and add to List for using many time var lt_Grade = new List<ComboBoxItem>(); try { cmd.CommandText = "SELECT * FROM Grade"; connection.Open(); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { lt_Grade.Add(new ComboBoxItem(reader[0],reader[1].ToString(), reader[2])); } } connection.Close(); } catch (Exception ex) { MessageBox.Show("Message: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); connection.Close(); } // Add Grade to cb_MusicalLiteracy, cb_Listening, cb_Performing, cb_Musicianship, cb_Technique, cb_Effort foreach (ComboBoxItem str_grade in lt_Grade) { cb_MusicalLiteracy.Items.Add(str_grade); cb_Performing.Items.Add(str_grade); cb_Listening.Items.Add(str_grade); cb_Musicianship.Items.Add(str_grade); cb_Technique.Items.Add(str_grade); cb_Effort.Items.Add(str_grade); } // Select value default for all Common Box cb_MusicalLiteracy.SelectedIndex = 0; cb_Performing.SelectedIndex = 0; cb_Listening.SelectedIndex = 0; cb_Musicianship.SelectedIndex = 0; cb_Technique.SelectedIndex = 0; cb_Effort.SelectedIndex = 0; }