private void subj_btn_Click_1(object sender, EventArgs e)
{
//generate random id
Random rnd = new Random();
int subjid = rnd.Next(1, 100000);
//initialize subject class
Subject_class sub = new Subject_class();
sub.Subject_id = subjid;
sub.Subject = subject_txtbx.Text;
sub.Project_id = Convert.ToInt32(project_id_lbl.Text);
try
{
/* string[] projectdiv = project.Split(',');
* Subject_class sub = new Subject_class();
* sub.Subject = subject_txtbx.Text;
* sub.Project_id = projectdiv[0];
* subject_listbx.Items.Add(sub.Showsubject(projectdiv[0]));
*
* subj.Add(sub);
*
* MessageBox.Show(projectdiv[0]);
* subject_txtbx.Text = "";
*/
MySqlConnection connection = new MySqlConnection(Connec);
connection.Open();
string query2 = "INSERT INTO internal_communication_4.subjects(subject_id, subject_name, Project_project_id) VALUES( @subject_id, @subject_name, (SELECT project_id FROM internal_communication_4.projects WHERE (project_id = '" + sub.Project_id + "' )))";
MySqlCommand comm = connection.CreateCommand();
comm.CommandText = query2;
comm.Parameters.AddWithValue("@subject_id", sub.Subject_id);
comm.Parameters.AddWithValue("@subject_name", sub.Subject);
comm.ExecuteNonQuery();
connection.Close();
connection.Open();
string query4 = "SELECT subject_id, subject_name, Project_project_id FROM internal_communication_4.subjects AS s1 JOIN internal_communication_4.projects AS p1 ON s1.Project_project_id = p1.project_id WHERE (project_selection = 1 AND subject_id = " + sub.Subject_id + ")";
MySqlDataAdapter adapter = new MySqlDataAdapter(query4, connection);
adapter.Fill(table);
while (row < table.Rows.Count)
{
Subject_class su = new Subject_class();
su.Subject = (string)table.Rows[row]["subject_name"];
su.Subject_id = Convert.ToInt32(table.Rows[row]["subject_id"]);
subject_listbx.Items.Add(su.Showsubject());
row += 1;
}
connection.Close();
}
catch
{
MessageBox.Show("Please input a correct subject ");
}
subject_txtbx.Text = "";
}
//load the text file
private void view_project_Load(object sender, EventArgs e)
{
string[] projectdiv = project.Split(',');
// subj = SubjectDA.Loadsub(projectdiv[0]);
// AddSubject(subj);
try
{
//fill listbox while loading
MySqlConnection connection = new MySqlConnection(Connec);
connection.Open();
string query1 = @"SELECT
subject_id,
subject_name,
Project_project_id
FROM internal_communication_4.subjects AS s1
JOIN internal_communication_4.projects AS p1
ON s1.Project_project_id = p1.project_id
WHERE (project_selection = 1)";
/*MySqlDataAdapter adapter = new MySqlDataAdapter(query1, connection);
* adapter.Fill(table);
* subject_listbx.DataSource = table;
* subject_listbx.DisplayMember = "subject_name";
*/
MySqlDataAdapter adapt = new MySqlDataAdapter(query1, connection);
adapt.Fill(table);
while (row < table.Rows.Count)
{
Subject_class su = new Subject_class();
su.Subject_id = Convert.ToInt32(table.Rows[row]["subject_id"]);
su.Subject = (string)table.Rows[row]["subject_name"];
subject_listbx.Items.Add(su.Showsubject());
row += 1;
}
connection.Close();
connection.Open();
string query3 = "SELECT project_name, project_id FROM internal_communication_4.projects WHERE (project_selection = 1)";
MySqlDataAdapter adapte = new MySqlDataAdapter(query3, connection);
adapte.Fill(table2);
project_lbl.Text = (string)table2.Rows[0]["project_name"];
project_id_lbl.Text = table2.Rows[0]["project_id"].ToString();
connection.Close();
}
catch
{}
try
{
MySqlConnection connection2 = new MySqlConnection(Connec);
Subject_class sub = new Subject_class();
sub.Subject_select = 2;
connection2.Open();
string query7 = "UPDATE internal_communication_4.subjects SET subjects_selection = @subject_selection";
MySqlCommand comm = connection2.CreateCommand();
comm.CommandText = query7;
comm.Parameters.AddWithValue("@subject_selection", sub.Subject_select);
comm.ExecuteNonQuery();
connection2.Close();
}
catch { }
}
private void Next_Click(object sender, EventArgs e)
{
//reset the current project to inactive in the database and clear the subject list
Projects_class pro2 = new Projects_class();
pro2.Proj_active = 2;
MySqlConnection connection1 = new MySqlConnection(connec);
connection1.Open();
string query8 = @"UPDATE internal_communication_4.projects
SET project_selection = @project_selection
WHERE (project_id = " + proID + ")";
MySqlCommand comm1 = connection1.CreateCommand();
comm1.CommandText = query8;
comm1.Parameters.AddWithValue("@project_selection", pro2.Proj_active);
comm1.ExecuteNonQuery();
connection1.Close();
subject_listbx.Items.Clear();
//reset the subject active
try
{
MySqlConnection connection2 = new MySqlConnection(connec);
Subject_class sub = new Subject_class();
sub.Subject_select = 2;
connection2.Open();
string query10 = "UPDATE internal_communication_4.subjects SET subject_selection = @subject_selection";
MySqlCommand comm = connection2.CreateCommand();
comm.CommandText = query10;
comm.Parameters.AddWithValue("@subject_selection", sub.Subject_select);
comm.ExecuteNonQuery();
connection2.Close();
}
catch
{
MessageBox.Show("error, please repeat the process");
}
Projects_class pro = new Projects_class();
pro.Proj_active = 1;
selectedItems = new ListBox.SelectedObjectCollection(project_list);
selectedItems = project_list.SelectedItems;
int selectedIndex = project_list.SelectedIndex;
if (selectedIndex != -1)
{
for (int i = selectedItems.Count - 1; i >= 0; i--)
{
projIDii = selectedItems[i].ToString();
}
projIDiii = projIDii.Split(',');
proID = Convert.ToInt32(projIDiii[0]);
MySqlConnection connection = new MySqlConnection(connec);
connection.Open();
string query7 = @"UPDATE internal_communication_4.projects
SET project_selection = @project_selection
WHERE (project_id = " + proID + ")";
MySqlCommand comm = connection.CreateCommand();
comm.CommandText = query7;
comm.Parameters.AddWithValue("@project_selection", pro.Proj_active);
comm.ExecuteNonQuery();
connection.Close();
}
//selectedItems = new ListBox.SelectedObjectCollection(project_list);
string project = Convert.ToString(selectedItems);
if (selectedItems != null)
{
string[] projectdiv = project.Split(',');
// subj = SubjectDA.Loadsub(projectdiv[0]);
// AddSubject(subj);
try
{
//fill listbox while loading
MySqlConnection connection = new MySqlConnection(connec);
connection.Open();
string query1 = @"SELECT
subject_id,
subject_name,
Project_project_id
FROM internal_communication_4.subjects AS s1
JOIN internal_communication_4.projects AS p1
ON s1.Project_project_id = p1.project_id
WHERE (project_selection = 1)";
table.Clear();
row = 0;
MySqlDataAdapter adapt = new MySqlDataAdapter(query1, connection);
adapt.Fill(table);
while (row < table.Rows.Count)
{
Subject_class su = new Subject_class();
su.Subject_id = Convert.ToInt32(table.Rows[row]["subject_id"]);
su.Subject = (string)table.Rows[row]["subject_name"];
subject_listbx.Items.Add(su.Showsubject());
row += 1;
}
connection.Close();
selectedItems = null;
row = 0;
}
catch
{
MessageBox.Show("error, please repeat the process");
}
}
else
{
MessageBox.Show("Please select a project");
}
}