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