private void cmdalocatePAdvisor_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(conStr);
con.Open();
string Insert;
if (con.State == System.Data.ConnectionState.Open)
{
Insert = "INSERT INTO ProjectAdvisor(AdvisorId, ProjectId, AdvisorRole, AssignmentDate) VALUES ('" + Convert.ToInt32(cmbAdvisorId.Text) + "', (SELECT Id FROM Project WHERE Title = '" + Convert.ToString(cmbtitle.Text) + "'), (SELECT Lookup.Id FROM Lookup WHERE Lookup.Value = '" + Convert.ToString(cmbadvisorRole.Text) + "'), '" + Convert.ToDateTime(dTPAssigmentDay.Value) + "')";
SqlCommand cmd = new SqlCommand(Insert, con);
cmd.ExecuteNonQuery();
MessageBox.Show("Succesfully Inserted");
// query = "INSERT INTO ProjectAdvisor(AdvisorId, ProjectId , AdvisorRole , AssignmentDate) VALUES('" + id + "','" + Convert.ToString(txtRegNo.Text) + "')";
}
projectTitle();
//MessageBox.Show("SuccessFully Added");
this.Hide();
ProjectAdvisor a = new ProjectAdvisor();
a.Show();
}
private void button10_Click(object sender, EventArgs e)
{
ProjectAdvisor proadvfm = new ProjectAdvisor();
proadvfm.Show();
}
private void button4_Click_1(object sender, EventArgs e)
{
ProjectAdvisor id1 = new ProjectAdvisor();
id1.Show();
}
private void button8_Click(object sender, EventArgs e)
{
con.Close();
//outer if check if Project Id and advisor Id exists or not.
//inner if check if there is any kind of primary key violation inside table
con.Open();
SqlCommand check_User_Name = new SqlCommand("SELECT ID FROM Advisor WHERE ([ID] = @ID)", con);
check_User_Name.Parameters.AddWithValue("ID", AdvId.Text);
SqlDataReader reader1 = check_User_Name.ExecuteReader();
check_User_Name = new SqlCommand("SELECT ID FROM Project WHERE ([ID] = @ID)", con);
check_User_Name.Parameters.AddWithValue("ID", ProId.Text);
SqlDataReader reader2 = check_User_Name.ExecuteReader();
if (reader1.HasRows && reader2.HasRows)
{
con.Close();
con.Open();
SqlCommand check_User_Name3 = new SqlCommand("SELECT ProjectId,AdvisorId FROM ProjectAdvisor WHERE ([AdvisorId] = @AdvisorId and [ProjectId] = @ProjectId)", con);
check_User_Name3.Parameters.AddWithValue("@AdvisorId", AdvId.Text);
check_User_Name3.Parameters.AddWithValue("@ProjectId", ProId.Text);
SqlDataReader reader3 = check_User_Name3.ExecuteReader();
if (reader3.HasRows)
{
con.Close();
MessageBox.Show("There must exists one advisor of one type for 1 project.");
}
else
{
con.Close();
con.Open();
SqlCommand cmd = new SqlCommand("Select COUNT(1) As pid from ProjectAdvisor WHERE ProjectId = '" + ProId.Text + "';", con);
object countstd1 = cmd.ExecuteScalar();
int countstd = 0;
if (!(countstd1 == DBNull.Value))
{
countstd = Convert.ToInt32(countstd1);
}
if (countstd >= 3)
{
con.Close();
MessageBox.Show("No More Advisor can be added against this project Can Be Added.");
}
else
{
cmd = con.CreateCommand();
cmd = new SqlCommand("INSERT INTO ProjectAdvisor(AdvisorId, ProjectId,AdvisorRole,AssignmentDate) VALUES(@AdvisorId,@ProjectId,@AdvisorRole,@AssignmentDate)", con);
cmd.Parameters.AddWithValue("@ProjectId", ProId.Text);
cmd.Parameters.AddWithValue("@AdvisorId", AdvId.Text);
string desig = AdvR.Text.ToString();
int g = Advisor_look(desig);
cmd.Parameters.AddWithValue("@AdvisorRole", g);
cmd.Parameters.AddWithValue("@AssignmentDate", DateTime.Parse(Assgdate.Text));
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Data Inserted Successfully");
this.Hide();
ProjectAdvisor pradfm = new ProjectAdvisor();
pradfm.Show();
}
}
}
else
{
con.Close();
MessageBox.Show("Record does not exists.");
}
con.Close();
}
private void projectAdvisorToolStripMenuItem_Click(object sender, EventArgs e)
{
ProjectAdvisor l = ProjectAdvisor.getInstance();
l.Show();
this.Hide();
}