private void subButton_Click(object sender, EventArgs e) { Excel.Application oXL; Excel._Workbook oWB; Excel._Worksheet oSheet; Excel.Range oRng; int test = 0; int r = 0; try { //Start Excel and get Application object. oXL = new Excel.Application(); oXL.Visible = false; oWB = oXL.Workbooks.Open("ImportData.xlsx"); oSheet = (Excel._Worksheet)oWB.ActiveSheet; oRng = oSheet.UsedRange; rows = oRng.Rows.Count; for (r = 2; r <= rows; r++) { try { //studentID = oRng.Cells[r, 1] + ""; studentID = int.Parse(oRng.Cells[r, 1].Value.ToString()); } catch { oWB.Close(false); //MessageBox.Show("error"); MessageBox.Show("There is an error in the Student ID. Please make sure there is a number."); this.Close(); return; } test = 1; students = Model.getStudentIDs(); bool isAlreadyStudent = false; foreach (string a in students) { if (a.TrimStart('0').Equals(studentID.ToString())) { isAlreadyStudent = true; } } if (isAlreadyStudent) { ; } else { try { firstName = oSheet.Cells[r, 2].Value.ToString(); } catch { MessageBox.Show("Please provide a first name"); return; } try { lastName = oSheet.Cells[r, 3].Value.ToString(); } catch { MessageBox.Show("Please provide a last name"); return; } } try { gender = oSheet.Cells[r, 4].Value.ToString(); } catch { gender = " "; } try { race = oSheet.Cells[r, 5].Value.ToString(); } catch { race = " "; } try { gpa = float.Parse((string)oSheet.Cells[r, 6].Value.ToString()); } catch { try { gpa = float.Parse((string)oSheet.Cells[r, 7].Value.ToString()); gpa = Model.convertGPA(gpa); } catch { gpa = 0; } } try { string gradeDate = oRng.Cells[r, 8].Value.ToString(); string[] gradeDate2 = gradeDate.Split(' '); gradeDate = gradeDate2[0]; gradeDate2 = gradeDate.Split('/'); string month = gradeDate2[0]; string day = gradeDate2[1]; string year = gradeDate2[2]; // double d = double.Parse((string) oRng.Cells[r, 8].Value.ToString()); // MessageBox.Show(d.ToString() + "grademod"); // DateTime conv = DateTime.FromOADate(d); //String year = conv.Year.ToString(); //String month = conv.Month.ToString(); //String day = conv.Day.ToString(); gradeMod = year + '-' + month + '-' + day; } catch { DateTime date = DateTime.Now; String year = date.Year.ToString(); String month = date.Month.ToString(); String day = date.Day.ToString(); gradeMod = year + '-' + month + '-' + day; } try { daysAbsent = int.Parse((string)oSheet.Cells[r, 9].Value.ToString()); } catch { daysAbsent = 0; } try { numRefs = int.Parse((string)oSheet.Cells[r, 10].Value.ToString()); } catch { numRefs = 0; } try { school = oSheet.Cells[r, 11].Value.ToString(); } catch { school = " "; } String y = ""; String m = ""; String da = ""; try { string gradeDate = oRng.Cells[r, 12].Value.ToString(); string[] gradeDate2 = gradeDate.Split(' '); gradeDate = gradeDate2[0]; gradeDate2 = gradeDate.Split('/'); m = gradeDate2[0]; da = gradeDate2[1]; y = gradeDate2[2]; regDate = y + '-' + m + '-' + da; } catch { DateTime date = DateTime.Now; String year = date.Year.ToString(); String month = date.Month.ToString(); String day = date.Day.ToString(); gradeMod = year + '-' + month + '-' + day; } try { grade = int.Parse((string)oSheet.Cells[r, 13].Value.ToString()); } catch { grade = 0; } students = Model.getStudentIDs(); isAlreadyStudent = false; foreach (string a in students) { if (a.TrimStart('0').Equals(studentID.ToString())) { isAlreadyStudent = true; } } if (isAlreadyStudent) { try { Model.UpdateStudentTable(studentID.ToString(), grade.ToString(), gradeMod, regDate, gender, race, daysAbsent.ToString()); Model.InsertCumGPA(studentID, gpa, gradeMod); DateTime date = DateTime.Now; String year = date.Year.ToString(); String month = date.Month.ToString(); String day = date.Day.ToString(); string curDate = year + '-' + month + '-' + day; Model.InsertEmptyReferrals(studentID, curDate, numRefs); } catch { MessageBox.Show("SQL update didn't work"); oWB.Close(false); } } else { try { Model.InsertStudent(firstName, lastName, studentID, gpa, school, grade, numRefs, daysAbsent, gender, race, "yes", regDate, gradeMod); DateTime date = DateTime.Now; String year = date.Year.ToString(); String month = date.Month.ToString(); String day = date.Day.ToString(); string curDate = year + '-' + month + '-' + day; List <String> schools = Model.getSchools(); if (!schools.Contains(school)) { Model.InsertSchool(school); } Model.InsertAttends(curDate, studentID, school); Model.InsertCumGPA(studentID, gpa, gradeMod); Model.InsertEmptyReferrals(studentID, curDate, numRefs); } catch { MessageBox.Show("sql insert didn't work"); oWB.Close(false); } } } } catch (Exception ex) { string errorMessage; errorMessage = "Error: "; errorMessage = string.Concat(errorMessage, ex.Message); errorMessage = string.Concat(errorMessage, " Line: "); errorMessage = string.Concat(errorMessage, ex.Source); errorMessage = string.Concat(errorMessage, ex.StackTrace); MessageBox.Show(errorMessage); //oWB.Close(false); } this.Close(); }
private void AddStudentSubmit_Click_1(object sender, EventArgs e) { String firstName; String lastName; if (addFirstName.Text != "" && addLastName.Text != "") { firstName = addFirstName.Text; lastName = addLastName.Text; } else { MessageBox.Show("Please enter a name for the student!"); return; } int studentID; if (addStudentID.Text != "") { try { studentID = int.Parse(addStudentID.Text); } catch { MessageBox.Show("Something went wrong in reading the ID of the student." + "\nPlease make sure that there is a number in the ID field."); return; } } else { MessageBox.Show("Please enter an ID for the student!"); return; } if (Model.StudentExists(studentID.ToString())) { MessageBox.Show("A student with this ID already exists in the database!"); return; } Double gpa; if (AddGPA.Text != "") { try { gpa = Double.Parse(AddGPA.Text); } catch { MessageBox.Show("Something went wrong in reading the GPA of the student." + "\nPlease make sure that there is a number in the GPA field."); return; } } else { MessageBox.Show("Please enter a GPA on a 4.0 scale!"); return; } String school = addSchool.Text; int grade; if (AddGrade.Text != "") { try { grade = int.Parse(AddGrade.Text); } catch { MessageBox.Show("Something went wrong in reading the grade level of the student." + "\nPlease make sure that there is a number in the grade level."); return; } } else { MessageBox.Show("There is nothing in the Grade Level blank!"); return; } int numRefs; if (AddBehavior.Text != "") { numRefs = int.Parse(AddBehavior.Text); } else { numRefs = 0; } int daysMissed; if (AddAttend.Text != "") { daysMissed = int.Parse(AddAttend.Text); } else { daysMissed = 0; } String gender = AddGender.Text; String race = raceBox.Text; String curStudent = AddCurrent.Text; DateTime date = DateTime.Now; String year = date.Year.ToString(); String month = date.Month.ToString(); String day = date.Day.ToString(); String curDay = year + '-' + month + '-' + day; Model.InsertStudent(firstName, lastName, studentID, gpa, school, grade, numRefs, daysMissed, gender, race, curStudent, curDay, curDay); Model.InsertAttends(curDay, studentID, school); Model.InsertCumGPA(studentID, (float)gpa, curDay); Model.InsertEmptyReferrals(studentID, curDay, numRefs); this.Close(); }