Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- using System;
- using System.Collections.Generic;
- using System.ComponentModel;
- using System.Data;
- using System.Drawing;
- using System.Linq;
- using System.Text;
- using System.Windows.Forms;
- using MySql.Data.MySqlClient;
- using System.Globalization;
- using MySql.Data;
- //Craig D'Souza
- //January 8 2017
- //StudentInformationSystem
- //Create a student info program using MySQL
- namespace StudentInformation
- {
- public partial class frmAdmin : Form
- {
- private MySqlConnection connection = new MySqlConnection();
- int[] Marks = new int[5];
- string toPass;
- string toPass2;
- //admin form
- public frmAdmin(string recieved, string recieved2)
- {
- InitializeComponent();
- connection.ConnectionString = @"Server=cra1g.tk; Database=craigstudentinfo; Uid=craig; Pwd=password123;";
- toPass = recieved;
- toPass2 = recieved2;
- }
- private void frmAdmin_Load(object sender, EventArgs e)
- {
- btnExitNew.Visible = false;
- btnExitEdit.Visible = false;
- txtLevel.Enabled = false;
- txtUsername.Enabled = false;
- txtPassword.Enabled = false;
- txtMarks.Enabled = false;
- btnUpdate.Visible = false;
- PopulateCmbName();
- disableUpdate();
- txtStudentId.Enabled = false;
- txtAVG.Enabled = false;
- btnEdit.Enabled = false;
- btnChart.Enabled = false;
- btnClearChart.Enabled = false;
- }
- public void enableUpdate()
- {
- txtFirstName.Enabled = true;
- txtLastName.Enabled = true;
- dtpDOB.Enabled = true;
- btnUpdate.Visible = true;
- btnDelete.Visible = true;
- }
- public void disableUpdate()
- {
- txtFirstName.Enabled = false;
- txtLastName.Enabled = false;
- dtpDOB.Enabled = false;
- btnSave.Visible = false;
- btnDelete.Visible = false;
- }
- private void PopulateCmbName()
- {
- cmbNames.Items.Clear();
- try
- {
- connection.Open();
- MySqlCommand command = new MySqlCommand();
- command.Connection = connection;
- string query = "SELECT LastName, FirstName FROM tblstudents";
- command.CommandText = query;
- MySqlDataReader reader = command.ExecuteReader();
- while (reader.Read())
- {
- if (reader[0].ToString() == "min6" && reader[1].ToString() == "ad")
- {
- //skip
- }
- else
- {
- cmbNames.Items.Add(reader[0].ToString() + "," + reader[1].ToString());
- }
- }
- reader.Close();
- connection.Close();
- }
- catch (Exception e)
- {
- MessageBox.Show(e.ToString());
- connection.Close();
- }
- }
- private void btnEdit_Click(object sender, EventArgs e)
- {
- DialogResult dialogResult = MessageBox.Show("You are about to enter edit mode!", "Edit Mode", MessageBoxButtons.YesNo);
- if (dialogResult == DialogResult.Yes)
- {
- btnExitEdit.Visible = true;
- cmbNames.Enabled = false;
- txtMarks.Enabled = true;
- btnEdit.Visible = false;
- txtUsername.Enabled = true;
- txtPassword.Enabled = true;
- enableUpdate();
- }
- else
- {
- }
- }
- private void cmbNames_SelectedIndexChanged(object sender, EventArgs e)
- {
- try
- {
- btnChart.Enabled = true;
- btnClearChart.Enabled = true;
- txtUsername.Enabled = false;
- txtPassword.Enabled = false;
- txtMarks.Enabled = false;
- txtFirstName.Enabled = false;
- txtLastName.Enabled = false;
- dtpDOB.Enabled = false;
- btnEdit.Enabled = true;
- txtMarks.Text = "";
- connection.Open();
- MySqlCommand command = new MySqlCommand();
- command.Connection = connection;
- string query = "SELECT FirstName, LastName, DOB, Mark1, Mark2, Mark3, Mark4, Mark5, tblMarks.StuID, Username, Password FROM tblMarks LEFT JOIN tblStudents ON tblMarks.StuID=tblStudents.StuID WHERE LastName + ',' +FirstName='" + cmbNames.Text + "' ";
- command.CommandText = query;
- MySqlDataReader reader = command.ExecuteReader();
- if (reader.Read() == true)
- {
- for (int i = 0; i < cmbNames.SelectedIndex; i++)
- {
- reader.Read();
- }
- }
- int Total = 0;
- txtFirstName.Text = reader["FirstName"].ToString();
- txtLastName.Text = reader["LastName"].ToString();
- txtStudentId.Text = reader["stuID"].ToString();
- txtUsername.Text = reader["Username"].ToString();
- txtPassword.Text = reader["Password"].ToString();
- dtpDOB.Text = Convert.ToDateTime(reader["DOB"].ToString()).ToString("yyyy/MM/dd");
- for (int i = 0; i < 5; i++)
- {
- txtMarks.Text += (reader[i + 3].ToString()) + Environment.NewLine;
- Total += int.Parse(reader[i + 3].ToString());
- }
- txtAVG.Text = (Total / 5).ToString();
- string mark = "";
- if (int.Parse(txtAVG.Text) >= 90)
- {
- mark = "4+";
- }
- else if (int.Parse(txtAVG.Text) >= 85 && int.Parse(txtAVG.Text) < 90)
- {
- mark = "4";
- }
- else if (int.Parse(txtAVG.Text) >= 80 && int.Parse(txtAVG.Text) < 85)
- {
- mark = "4-";
- }
- else if (int.Parse(txtAVG.Text) < 80 && int.Parse(txtAVG.Text) >= 75)
- {
- mark = "3+";
- }
- else if (int.Parse(txtAVG.Text) < 75 && int.Parse(txtAVG.Text) >= 70)
- {
- mark = "3";
- }
- else if (int.Parse(txtAVG.Text) < 70 && int.Parse(txtAVG.Text) >= 65)
- {
- mark = "3-";
- }
- else if (int.Parse(txtAVG.Text) < 65 && int.Parse(txtAVG.Text) >= 60)
- {
- mark = "2+";
- }
- else if (int.Parse(txtAVG.Text) < 60 && int.Parse(txtAVG.Text) >= 55)
- {
- mark = "2";
- }
- else if (int.Parse(txtAVG.Text) < 55 && int.Parse(txtAVG.Text) >= 50)
- {
- mark = "2-";
- }
- else if (int.Parse(txtAVG.Text) < 50)
- {
- mark = "F";
- }
- txtLevel.Text = mark;
- reader.Close();
- connection.Close();
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.ToString());
- connection.Close();
- }
- }
- private void btnNew_Click(object sender, EventArgs e)
- {
- DialogResult dialogResult = MessageBox.Show("Enter new data?", "Entering Data", MessageBoxButtons.YesNo);
- if (dialogResult == DialogResult.Yes)
- {
- txtLevel.Enabled = false;
- txtUsername.Enabled = true;
- txtPassword.Enabled = true;
- btnSave.Enabled = true;
- btnSave.Visible = true;
- btnExitNew.Visible = true;
- enableNew();
- clearText();
- }
- else if (dialogResult == DialogResult.No)
- {
- btnSave.Visible = false;
- txtFirstName.Enabled = false;
- txtLastName.Enabled = false;
- dtpDOB.Enabled = false;
- txtMarks.Enabled = false;
- }
- enableText();
- }
- private void btnSave_Click(object sender, EventArgs e)
- {
- try
- {
- if (countLines() == 5)
- {
- if (checkInputMarks() == true)
- {
- int NewID = assignStudentID();
- connection.Open();
- MySqlCommand command = new MySqlCommand();
- command.Connection = connection;
- for (int i = 0; i < 5; i++)
- {
- Marks[i] = int.Parse(txtMarks.Lines[i]);
- }
- command.CommandText = "INSERT INTO tblStudents (StuID, FirstName, LastName, Username, Password, DOB) VALUES ('" + NewID + "','" + txtFirstName.Text + "','" + txtLastName.Text + "','" + txtUsername.Text + "','" + txtPassword.Text + "','" + dtpDOB.Text + "');";
- command.ExecuteNonQuery();
- command.CommandText = "SELECT StuID FROM tblstudents WHERE FirstName='" + txtFirstName.Text + "' ";
- MySqlDataReader reader = command.ExecuteReader();
- reader.Read();
- reader.Close();
- command.CommandText = "INSERT INTO tblmarks (MarkID, StuID, Mark1, Mark2, Mark3, Mark4, Mark5) VALUES ('" + NewID + "','" + NewID + "', '" + Marks[0] + "', '" + Marks[1] + "', '" + Marks[2] + "', '" + Marks[3] + "', '" + Marks[4] + "')";
- command.ExecuteNonQuery();
- MessageBox.Show("Data Saved Successfully");
- connection.Close();
- clearText();
- cmbNames.Enabled = true;
- cmbNames.Items.Clear();
- PopulateCmbName();
- txtFirstName.Enabled = false;
- txtLastName.Enabled = false;
- dtpDOB.Enabled = false;
- txtUsername.Enabled = false;
- txtPassword.Enabled = false;
- btnSave.Visible = false;
- btnExitNew.Visible = false;
- }
- }
- else
- {
- MessageBox.Show("You currently have " + countLines() + " marks. Please make sure you only have 5 marks");
- }
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.ToString());
- connection.Close();
- }
- }
- private void enableNew()
- {
- btnNew.Enabled = true;
- btnSave.Enabled = true;
- btnEdit.Enabled = true;
- btnDelete.Enabled = true;
- btnExit.Enabled = true;
- btnChart.Enabled = true;
- btnClearChart.Enabled = true;
- }
- private void clearText()
- {
- txtStudentId.Text = "";
- txtFirstName.Text = "";
- txtLastName.Text = "";
- dtpDOB.Text = "";
- txtAVG.Text = "";
- txtMarks.Text = "";
- }
- private void enableText()
- {
- txtStudentId.Enabled = false;
- txtFirstName.Enabled = true;
- txtLastName.Enabled = true;
- dtpDOB.Enabled = true;
- txtAVG.Enabled = false;
- txtMarks.Enabled = true;
- }
- private void btnDelete_Click(object sender, EventArgs e)
- {
- try
- {
- connection.Open();
- MySqlCommand command = new MySqlCommand();
- command.Connection = connection;
- string Query = "DELETE FROM tblmarks WHERE StuID=" + txtStudentId.Text + "";
- //string Query = "DELETE from tblstudents WHERE StudID=" + txtStudentID.Text + "";
- command.CommandText = Query;
- command.ExecuteNonQuery();
- //Query = "DELETE from tblmarks WHERE StudID=" + txtStudentID.Text + "";
- Query = "DELETE FROM tblstudents WHERE StuID=" + txtStudentId.Text + "";
- command.CommandText = Query;
- command.ExecuteNonQuery();
- MessageBox.Show("Data Deleted");
- connection.Close();
- clearText();
- cmbNames.Items.Clear();
- btnDelete.Visible = false;
- btnUpdate.Visible= false;
- btnEdit.Visible = true;
- txtFirstName.Enabled = false;
- txtLastName.Enabled = false;
- dtpDOB.Enabled = false;
- txtUsername.Enabled = false;
- txtPassword.Enabled = false;
- cmbNames.Enabled = true;
- txtMarks.Enabled = false;
- PopulateCmbName();
- }
- catch (Exception ex)
- {
- MessageBox.Show("Error" + ex);
- connection.Close();
- }
- }
- private void btnChart_Click(object sender, EventArgs e)
- {
- try
- {
- connection.Open();
- MySqlCommand command = new MySqlCommand();
- command.Connection = connection;
- string query = "SELECT Mark1, Mark2, Mark3, Mark4, Mark5 FROM tblmarks WHERE tblmarks.StuID=" + txtStudentId.Text;
- command.CommandText = query;
- MySqlDataReader reader = command.ExecuteReader();
- chartMarks.Series["Marks"].YValuesPerPoint = 5;
- while (reader.Read())
- {
- //clear the chart not the series
- foreach (var series in chartMarks.Series)
- {
- series.Points.Clear();
- }
- for (int x = 0; x < 5; x++)
- {
- this.chartMarks.Series["Marks"].Points.AddXY("Mark" + Convert.ToString(x + 1), reader[x].ToString());
- }
- }
- connection.Close();
- }
- catch (Exception err)
- {
- MessageBox.Show("ERROR" + err);
- connection.Close();
- }
- }
- //snippet//works
- private void btnClearChart_Click(object sender, EventArgs e)
- {
- foreach (var series in chartMarks.Series)
- {
- series.Points.Clear();
- }
- }
- private bool CheckMarks(bool NoErrors = false, string Counter = "")
- {
- for (int i = 0; i < 5; i++)
- {
- if ((int.Parse(txtMarks.Lines[i]) > 100) || (int.Parse(txtMarks.Lines[i]) < 0))
- {
- Counter += i + ",";
- }
- }
- if (Counter == "")
- {
- NoErrors = true;
- }
- else
- {
- NoErrors = false;
- MessageBox.Show("Lines " + Counter + "have invalid values");
- }
- return NoErrors;
- }
- public int countLines()
- {
- int counter = 0;
- //Counting the amount of lines
- for (int i = 0; i < txtMarks.Lines.Length; i++)
- {
- if ((txtMarks.Lines[i] != null) && (txtMarks.Lines[i] != ""))
- {
- counter += 1;
- }
- }
- return (counter);
- } //Used to count the lines in txtMarks
- private void txtUpdate_Click(object sender, EventArgs e)
- {
- if (countLines() == 5)
- {
- if (CheckMarks() == true)
- {
- try
- {
- if (checkInputMarks() == true)
- {
- txtUsername.Enabled = false;
- txtPassword.Enabled = false;
- connection.Open();
- string dateOfBirth = dtpDOB.Value.ToString("yyyy/MM/dd");
- MySqlCommand command = new MySqlCommand();
- command.Connection = connection;
- string Query = "Update tblStudents SET FirstName ='" + txtFirstName.Text + "',LastName ='" + txtLastName.Text + "',Username ='" + txtUsername.Text + "',Password ='" + txtPassword.Text + "',DOB ='" + dtpDOB.Text + "'Where StuID =" + txtStudentId.Text;
- command.CommandText = Query;
- command.ExecuteNonQuery();
- Query = "Update tblMarks SET Mark1 ='" + txtMarks.Lines[0] + "',Mark2 ='" + txtMarks.Lines[1] + "',Mark3 ='" + txtMarks.Lines[2] + "',Mark4 ='" + txtMarks.Lines[3] + "',Mark5 ='" + txtMarks.Lines[4] + "'Where StuID =" + txtStudentId.Text;
- command.CommandText = Query;
- command.ExecuteNonQuery();
- connection.Close();
- MessageBox.Show("Data Updated!");
- txtFirstName.Enabled = false;
- txtLastName.Enabled = false;
- dtpDOB.Enabled = false;
- cmbNames.Enabled = true;
- btnUpdate.Visible = false;
- btnDelete.Visible = false;
- txtMarks.Enabled = false;
- btnEdit.Visible = true;
- btnExitEdit.Visible = false;
- }
- }
- catch (Exception error)
- {
- cmbNames.Enabled = false;
- MessageBox.Show("Error" + error);
- }
- }
- }
- else
- {
- MessageBox.Show("You currently have " + countLines() + " marks. Please make sure you only have 5 marks");
- }
- }
- private void txtFirstName_KeyPress(object sender, KeyPressEventArgs e)
- {
- e.Handled = !(char.IsLetter(e.KeyChar) || e.KeyChar == (char)Keys.Back);
- }
- private void txtLastName_KeyPress(object sender, KeyPressEventArgs e)
- {
- e.Handled = !(char.IsLetter(e.KeyChar) || e.KeyChar == (char)Keys.Back);
- }
- private void txtMarks_KeyPress(object sender, KeyPressEventArgs e)
- {
- if (!char.IsControl(e.KeyChar) && !char.IsDigit(e.KeyChar))
- {
- e.Handled = true;
- }
- }
- public bool checkInputMarks() //Remember to add an error check for the date
- {
- bool Errors = false, FormatErrors = true;
- int Counter = countLines(), Check = 0, Holder = 0;
- string LineERRORS = "";
- if (Counter == 5)
- {
- //Checks if the marks input are intergers
- for (int i = 0; i < Counter; i++) //i<txtMarks.Lines.Length;
- {
- if (!int.TryParse(txtMarks.Lines[i], out Holder))
- {
- Check++;
- }
- }
- if (Check == 0)
- {
- for (int i = 0; i < Counter; i++)
- {
- if (int.Parse(txtMarks.Lines[i]) > 100)
- {
- LineERRORS += i + ",";
- }
- else if (int.Parse(txtMarks.Lines[i]) < 0)
- {
- LineERRORS += (i + 1) + ",";
- }
- }
- if (Check == 0)
- {
- if (checkFormat() == true)
- {
- FormatErrors = false;
- }
- else
- {
- FormatErrors = true;
- }
- }
- }
- }
- else if ((Counter == 5) && (Check == 0) && (LineERRORS != "") && (FormatErrors == true))
- {
- Errors = false;
- MessageBox.Show("One or more of your marks is invalid");
- }
- else if ((Counter == 5) && (Check == 0) && (LineERRORS == "") && (FormatErrors == false))
- {
- Errors = false;
- }
- return (Errors);
- }
- public bool checkFormat(bool Correct = true)
- {
- //learned to parse dates from https://www.dotnetperls.com/datetime-tryparse
- DateTime Temp;
- if (DateTime.TryParse("dd,MMM,yy", out Temp))
- {
- Correct = true;
- }
- else
- {
- Correct = false;
- MessageBox.Show("The Date entered was in the wrong format Please follow the format\nyyyy,mm,dd ex.1999,09,14");
- }
- return (Correct);
- }
- public int assignStudentID()
- {
- int NEWID = 0;
- try
- {
- connection.Open();
- MySqlCommand command = new MySqlCommand();
- command.Connection = connection;
- command.CommandText = "SELECT StuID FROM tblStudents Order by StuID";
- MySqlDataReader reader = command.ExecuteReader();
- //reader.Read();
- string Check = "";
- int counter = 0;
- if (reader.HasRows)
- {
- while (reader.Read())
- {
- counter++;
- Check += reader[0].ToString() + ",";
- }
- }
- reader.Close();
- int[] StudentID = new int[counter];
- for (int i = 0; i < StudentID.Length; i++)
- {
- StudentID[i] = int.Parse(Check.Split(',')[i]);
- }
- //Find the largest ID or largest number in the array
- int LargestID = 0;
- for (int i = 0; i < StudentID.Length; i++)
- {
- if (LargestID < StudentID[i])
- {
- LargestID = StudentID[i];
- }
- }
- for (int y = 1; y <= LargestID; y++)
- {
- for (int i = y - 1; i < StudentID.Length; i++)
- {
- if (StudentID[i] == y)
- {
- break;
- }
- else
- {
- NEWID = y;
- }
- }
- if (NEWID != 0)
- {
- break;
- }
- }
- if (NEWID == 0)
- {
- NEWID = LargestID + 1;
- }
- connection.Close();
- return (NEWID);
- }
- catch (Exception ex)
- {
- MessageBox.Show("ERROR" + ex);
- connection.Close();
- return (NEWID);
- }
- }
- private void btnExitEdit_Click(object sender, EventArgs e)
- {
- int Selected = cmbNames.SelectedIndex;
- cmbNames.Enabled = true;
- txtFirstName.Enabled = false;
- txtLastName.Enabled = false;
- dtpDOB.Enabled = false;
- txtUsername.Enabled = false;
- txtPassword.Enabled = false;
- cmbNames.SelectedIndex = 1;
- cmbNames.SelectedIndex = Selected;
- txtMarks.Enabled = false;
- btnExitEdit.Visible = false;
- btnUpdate.Visible = false;
- btnDelete.Visible = false;
- btnEdit.Visible = true;
- }
- private void btnExitNew_Click(object sender, EventArgs e)
- {
- cmbNames.Enabled = true;
- txtFirstName.Enabled = false;
- txtLastName.Enabled = false;
- dtpDOB.Enabled = false;
- txtUsername.Enabled = false;
- txtPassword.Enabled = false;
- cmbNames.SelectedIndex = 0;
- txtMarks.Enabled = false;
- btnExitNew.Visible = false;
- btnUpdate.Visible = false;
- btnDelete.Visible = false;
- btnEdit.Visible = true;
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement