public ActionResult Index(Models.aDepartment editDepartmentName) { int tempID = 0; //to temporarily store LM ID before adding to the database if (ModelState.IsValid) { // get slected lm's ID var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; string getIDString = "Select Employee_ID From dbo.Employee Where Employee_ID= '" + editDepartmentName.primaryLMID + "'"; using (var connection = new SqlConnection(connectionString)) { var command = new SqlCommand(getIDString, connection); connection.Open(); using (var reader = command.ExecuteReader()) while (reader.Read()) { tempID = (int)reader[0]; } //System.Diagnostics.Debug.WriteLine("This the LM ID"+tempID); connection.Close(); } //update the selected lm id along on to the selected department in the database string insertString = "UPDATE dbo.Department SET Line_Manager_ID = '" + tempID + "' WHERE Department_Name = '" + editDepartmentName.departmentName + "'"; using (var connection = new SqlConnection(connectionString)) { var command = new SqlCommand(insertString, connection); connection.Open(); using (var reader = command.ExecuteReader()) connection.Close(); } Response.Write("<script> alert ('Successfully edited the department')</script>"); } return(Index()); }
public ActionResult Index(Models.aDepartment newDepartmentName) { int tempID = 0; //to temporarily store LM ID before adding to the database //checks if new department name is entered if (newDepartmentName.departmentName == null) { ModelState.AddModelError("departmentName", "Department Name cannot be null"); } //checks if department name already exists List <string> departmentNames = new List <string>(); var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; string queryString = "Select Department_Name FROM dbo.Department "; using (var connection = new SqlConnection(connectionString)) { var command = new SqlCommand(queryString, connection); connection.Open(); using (var reader = command.ExecuteReader()) { while (reader.Read()) { departmentNames.Add((string)reader[0]); } } connection.Close(); } foreach (var department in departmentNames) { if (newDepartmentName.departmentName.ToLower() == department.ToLower()) { ModelState.AddModelError("departmentName", "Department name already exists"); } } //checks if department name has more than 30 charecters if (newDepartmentName.departmentName.Length > 30) { ModelState.AddModelError("departmentName", "Department name too long"); } //checks if department name has integers in it foreach (char c in newDepartmentName.departmentName) { if (char.IsDigit(c)) { ModelState.AddModelError("departmentName", "Department name cannot contain digits"); } } //if above checks are passed if (ModelState.IsValid) { //selecting the lm's id connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; string getIDString = "Select Employee_ID From dbo.Employee Where Employee_ID='" + newDepartmentName.primaryLMID + "'"; using (var connection = new SqlConnection(connectionString)) { var command = new SqlCommand(getIDString, connection); connection.Open(); using (var reader = command.ExecuteReader()) while (reader.Read()) { tempID = (int)reader[0]; } connection.Close(); //System.Diagnostics.Debug.WriteLine("This the LM ID - " + tempID); } //adding the slected lm id and the entered department name into the database newDepartmentName.departmentName = System.Text.RegularExpressions.Regex.Replace(newDepartmentName.departmentName, @"'", ""); string insertString = "Insert Into dbo.Department (Line_Manager_ID, Department_Name) VALUES ('" + tempID + "','" + newDepartmentName.departmentName + "')"; System.Diagnostics.Debug.WriteLine("insertString:", newDepartmentName.departmentName); using (var connection = new SqlConnection(connectionString)) { var command = new SqlCommand(insertString, connection); connection.Open(); using (var reader = command.ExecuteReader()) connection.Close(); } Response.Write("<script> alert ('Successfully added a new department')</script>"); } return(Index()); }