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;
}