//Shows the sector of the job in the drop down box in a textbox
private void showSector()
{
String query = "select [Sector] from Jobs where [Job Name] = @JobName";
using (connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(query, connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
command.Parameters.AddWithValue("@JobName", JobBox.Text);
connection.Open();
SqlDataReader cr = command.ExecuteReader();
if (cr.Read())
{
SectorTextBox.Text = (cr["Sector"].ToString());
}
else
{
SectorTextBox.Clear();
}
}
}
//enters a job into the system using the data in the textboxes
private void AddJobBut_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(JobNameBox.Text))//Job won't be entered if Job name text box is empty
{
MessageBox.Show("You have not entered a name for this job");
}
else
{
if (string.IsNullOrEmpty(JobDescTextBox.Text))//Job won't be entered if Job description text box is empty
{
MessageBox.Show("You have not entered a decription for this job");
}
else
{
if (string.IsNullOrEmpty(SectorTextBox.Text))
{
if (string.IsNullOrEmpty(SectorBox.Text))//Job won't be entered if sector text box and drop down is empty
{
MessageBox.Show("You have not entered the name of the sector of this job");
}
else
{
connection = new SqlConnection(connectionString);
String query = "declare @a int; " +
"declare @b int; " +
"declare @c int; " +
"if ((select count(*) from Jobs) > 0) Begin set @c = (select max(JobID) from Jobs) end else Begin set @c = 0 end " + // if there are no jobs in the table c = 0 but if there's at least 1 job, c = the highest job ID in the table
"set @a = (select count(*) from Jobs where [Job Name] is null and [Job Description] is null and Sector is null) " + // a = the number of jobs with no details except the JobID
"if @a > 0 " +
"Begin " +
"set @b = (select min(JobID) from Jobs where [Job Name] is null and [Job Description] is null and Sector is null) " + // b = the ID number of the job with the lowest ID number with no other details
"update Jobs set [Job Name] = (@JobName), [Job Description] = @Description, Sector = @Sector where JobID = @b " + //updates the details of the Job with JobID of b with the details from the text boxes
"end " +
"else " +
"begin " +
"DBCC CHECKIDENT(Jobs, RESEED, @c) " +
"Insert into [Jobs]([Job Name], [Job Description], [Sector]) Values (@JobName, @Description, @Sector); " +//inserts the details from the text boxes at the end of the table
"end";
using (connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(query, connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
connection.Open();
command.Parameters.AddWithValue("@JobName", JobNameBox.Text);
command.Parameters.AddWithValue("@Description", JobDescTextBox.Text);
if (string.IsNullOrEmpty(SectorTextBox.Text))
{
command.Parameters.AddWithValue("@Sector", SectorBox.Text);
}
else
{
command.Parameters.AddWithValue("@Sector", SectorTextBox.Text);
}
try
{
command.ExecuteReader();
MessageBox.Show("Job Entered");
}
catch (System.Data.SqlClient.SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
showJobs();
showSector();
JobNameBox.Clear();
JobDescTextBox.Clear();
SectorTextBox.Clear();
}
}
else
{
connection = new SqlConnection(connectionString);
String query = "declare @a int; " +
"declare @b int; " +
"declare @c int; " +
"if ((select count(*) from Jobs) > 0) Begin set @c = (select max(JobID) from Jobs) end else Begin set @c = 0 end " + // if there are no jobs in the table c = 0 but if there's at least 1 job, c = the highest job ID in the table
"set @a = (select count(*) from Jobs where [Job Name] is null and [Job Description] is null and Sector is null) " + // a = the number of jobs with no details except the JobID
"if @a > 0 " +
"Begin " +
"set @b = (select min(JobID) from Jobs where [Job Name] is null and [Job Description] is null and Sector is null) " + //b = the ID number of the job with the lowest ID number with no other details
"update Jobs set [Job Name] = (@JobName), [Job Description] = @Description, Sector = @Sector where JobID = @b " + //updates the details of the Job with JobID of b with the details from the text boxes
"end " +
"else " +
"begin " +
"DBCC CHECKIDENT(Jobs, RESEED, @c) " +
"Insert into [Jobs]([Job Name], [Job Description], [Sector]) Values (@JobName, @Description, @Sector); " +//inserts the details from the text boxes at the end of the table
"end";
using (connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(query, connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
connection.Open();
command.Parameters.AddWithValue("@JobName", JobNameBox.Text);
command.Parameters.AddWithValue("@Description", JobDescTextBox.Text);
if (string.IsNullOrEmpty(SectorTextBox.Text))
{
command.Parameters.AddWithValue("@Sector", SectorBox.Text);
}
else
{
command.Parameters.AddWithValue("@Sector", SectorTextBox.Text);
}
try
{
command.ExecuteReader();
MessageBox.Show("Job Added");
}
catch (System.Data.SqlClient.SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
showJobs();
showSector();
JobNameBox.Clear();
JobDescTextBox.Clear();
SectorTextBox.Clear();
}
}
}
}
