private void LToSubjectBtn_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(LIDtxt.Text) && string.IsNullOrEmpty(SIDtxt.Text) && string.IsNullOrEmpty(GIDtxt.Text)) { MessageBox.Show("Fill in all blanks !"); } else { using (SqlConnection sqlCon = new SqlConnection(con)) { sqlCon.Open(); LecturerSubject subject = new LecturerSubject(); subject.StaffID = Int32.Parse(LIDtxt.Text.Trim()); subject.GroupID = Int32.Parse(SIDtxt.Text.Trim()); LecturerSubject subjects = repos.LecturerToSubject(LIDtxt.Text.Trim(), SIDtxt.Text.Trim(), GIDtxt.Text.Trim()); } lecturerListView.DataSource = null; showLecturers(); LecturerSubjectView.DataSource = null; showLSView(); GroupListView.DataSource = null; showGroups(); } }
public LecturerSubject LecturerToSubject(string LID, string SID, string GID) { LecturerSubject ltoGroup = new LecturerSubject(); SqlConnection sqlCon = new SqlConnection(con); sqlCon.Open(); try { SqlCommand cmd = new SqlCommand("SELECT * FROM [LecturerSubject] WHERE StaffID=@LID AND SubjectID=@SID AND GroupID=@GID", sqlCon); cmd.Parameters.AddWithValue("@LID", LID); cmd.Parameters.AddWithValue("@SID", SID); cmd.Parameters.AddWithValue("@GID", GID); SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read() == true) { MessageBox.Show("This Already Exists !"); return(null); } dr.Close(); cmd = new SqlCommand("SELECT * FROM [Group] WHERE ID=@GID", sqlCon); cmd.Parameters.AddWithValue("@GID", GID); dr = cmd.ExecuteReader(); if (dr.Read() == false) { MessageBox.Show("Invalid Group Value"); return(null); } dr.Close(); cmd = new SqlCommand("SELECT * FROM [Staff] WHERE StaffID=@LID AND StaffID!=1", sqlCon); cmd.Parameters.AddWithValue("@LID", LID); dr = cmd.ExecuteReader(); if (dr.Read() == false) { MessageBox.Show("Invalid Lecturer Value"); return(null); } dr.Close(); cmd = new SqlCommand("SELECT * FROM [StudySubject] WHERE ID=@SID", sqlCon); cmd.Parameters.AddWithValue("@SID", SID); dr = cmd.ExecuteReader(); if (dr.Read() == false) { MessageBox.Show("Invalid Subject Value"); return(null); } dr.Close(); cmd = new SqlCommand("INSERT INTO [LecturerSubject] (StaffID,SubjectID,GroupID) VALUES(@LID,@SID,@GID)", sqlCon); cmd.Parameters.AddWithValue("@LID", LID); cmd.Parameters.AddWithValue("@SID", SID); cmd.Parameters.AddWithValue("@GID", GID); cmd.ExecuteNonQuery(); sqlCon.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); return(null); } return(ltoGroup); }