private void Form1_Load(object sender, EventArgs e)
{
conn = new SqlConnection(connStr);
studentFieldsAdapter = new SqlDataAdapter("SELECT * FROM studentFields", conn);
// Fill tables
ds = new DataSet();
subjectsAdapter = new SqlDataAdapter("SELECT * FROM subjects", conn);
classesAdapter = new SqlDataAdapter("SELECT * FROM classes", conn);
try
{
studentFieldsAdapter.Fill(ds, "studentFields");
if (ds.Tables["studentFields"] == null)
{
MessageBox.Show("student fields is null?");
}
classesAdapter.Fill(ds, "classes");
subjectsAdapter.Fill(ds, "subjects");
}
catch (SqlException sqlex)
{
ExceptionDialog ed = new ExceptionDialog(sqlex.Message, sqlex.StackTrace);
ed.ShowDialog();
}
// Fill comboBox
foreach (DataRow row in ds.Tables["classes"].Rows)
{
cmbClasses.Items.Add(row["classname"]);
}
// Intialize comboboxes to prevent NREs
cmbClasses.SelectedIndex = 0;
cmbSubjects.SelectedIndex = 0;
}
private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
// First get a column index
String colName = this.dataGridView1
.Columns[this.dataGridView1.CurrentCell.ColumnIndex]
.ToString();
int newValue;
Int32.TryParse(this.dataGridView1.CurrentCell.Value.ToString(), out newValue);
int fieldColumnOffset = getFieldColumnOffsetInPivot();
// Taking the fieldColumn offset, we map the column in the pivot table to it's relevant field
List <int> fieldIds = new List <int>();
foreach (DataRow row in getFieldIdAndNameTable(getCurrentSubjectId()).Rows)
{
int id = Convert.ToInt32(row["fieldid"]);
// Make sure it's distinct
if (!fieldIds.Contains(id))
{
fieldIds.Add(id);
}
}
if (this.dataGridView1.CurrentCell.ColumnIndex - fieldColumnOffset >= this.dataGridView1.Columns.Count)
{
return;
}
int fieldIdToUpdate = fieldIds[this.dataGridView1.CurrentCell.ColumnIndex - fieldColumnOffset];
Debug.WriteLine(fieldIdToUpdate);
int studentid = Convert.ToInt32(this.dataGridView1.CurrentRow.Cells["studentid"].Value);
// Now that we have the studentid and fieldid, we have enough to update student marks.
SqlDataAdapter subjectFieldsAdapter = new SqlDataAdapter("SELECT * FROM subjectfields", conn);
try
{
subjectFieldsAdapter.Fill(ds, "subjectFields");
}
catch (Exception ex)
{
new ExceptionDialog(ex.Message, ex.ToString()).ShowDialog();
}
DataRow rowToUpdate = ds.Tables["subjectFields"]
.Select("subjectid = " + getCurrentSubjectId() + " and fieldid = " + fieldIdToUpdate)
.First();
int maxMarks = Convert.ToInt32(rowToUpdate["maxMarks"]);
if (newValue > maxMarks)
{
MessageBox.Show("Marks given greater than max marks (" + maxMarks + "). Value will not be saved");
return;
}
String query = String.Format("UPDATE studentFields SET studentMarks = {0} where fieldid = {1} and studentid = {2}", newValue, fieldIdToUpdate, studentid);
SqlCommand cmd = new SqlCommand(query, conn);
Debug.WriteLine("update query: " + query);
try
{
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
ExceptionDialog ed = new ExceptionDialog(ex.Message, ex.ToString());
ed.ShowDialog();
}
this.dataGridView1.BeginInvoke(new MethodInvoker(refreshDataGridView));
}