private void submit_Click(object sender, EventArgs e) { String sql; // Update password if (!string.IsNullOrWhiteSpace(password.Text) && !string.IsNullOrWhiteSpace(confirm.Text)) { sql = "UPDATE Users SET UserPassword = '******' WHERE UserName = '******'"; if (!SqlManager.update(sql)) { MessageBox.Show("User password not updated, contact database admin.", "User Update Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } } // Update NonActive status if (deactivate.Checked != userActive()) { if (!userActive()) { sql = "INSERT INTO NonActive SELECT UserID FROM Users WHERE UserName = '******'"; } else { sql = "DELETE FROM NonActive WHERE NonActiveID = (SELECT UserID FROM Users WHERE UserName = '******')"; } if (!SqlManager.update(sql)) { MessageBox.Show("NonActive database not updated, contact database admin.", "User Update Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } } // Update Admin status if (admin.Checked != userAdmin()) { if (userAdmin()) { sql = "DELETE FROM Admins WHERE AdminID = (SELECT UserID FROM Users WHERE UserName = '******')"; } else { sql = "INSERT INTO Admins SELECT UserID FROM Users WHERE UserName = '******'"; } if (!SqlManager.update(sql)) { MessageBox.Show("Admin database not updated, contact database admin.", "User Update Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } } MessageBox.Show("User " + users.Text + " successfully updated.", "User Updated"); }
private void submit_Click(object sender, EventArgs e) { // Check question title, this should be unique to make searching easier if (questionID == 0 && SqlManager.query("SELECT 1 from Questions WHERE QuestionTitle='" + title.Text + "'").Rows.Count == 1) { MessageBox.Show("Question title already exists, update title and resubmit.", "Title not unique", MessageBoxButtons.OK, MessageBoxIcon.Warning); submit.Enabled = false; return; } else if (questionID != 0 && SqlManager.query("SELECT 1 from Questions WHERE QuestionTitle='" + title.Text + "' AND QuestionID<>" + questionID).Rows.Count == 1) { MessageBox.Show("Question title already exists, update title and resubmit.", "Title not unique", MessageBoxButtons.OK, MessageBoxIcon.Warning); submit.Enabled = false; return; } // Check if user would like to commit question to database var result = MessageBox.Show("Would you like to commit your question to the database?", "Commit Question", MessageBoxButtons.YesNo); if (result == DialogResult.No) { return; } // Check if course topics refernece exists, otherwise create a new reference var courseTopicRefID = 0; while (true) { DataTable dt = SqlManager.query("SELECT CourseTopicRefID FROM CourseTopics " + "WHERE LocationID=" + locationID + " AND DepartmentID=" + departmentID + " AND CourseID=" + courseID + " AND TopicID=" + topicID); if (dt.Rows.Count == 0) { if (!SqlManager.insert("INSERT INTO CourseTopics (LocationID, DepartmentID, CourseID, TopicID) " + "VALUES (" + locationID + ", " + departmentID + ", " + courseID + ", " + topicID + ")")) { MessageBox.Show("Course topic info could not be inserted into database, contact database admin.", "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error); submit.Enabled = false; return; } } else { courseTopicRefID = Convert.ToInt32(dt.Rows[0][0]); break; } } StringBuilder sql; if (questionID != 0) { sql = new StringBuilder("UPDATE Questions SET "); if (!string.IsNullOrWhiteSpace(image.Text)) { sql.Append("QuestionImage='" + image.Text + "', "); } sql.Append("QuestionTitle='" + title.Text + "', "); sql.Append("QuestionBody='" + body.Text + "', "); sql.Append("CourseTopicRefID=" + courseTopicRefID + ", "); sql.Append("DifficultyID=" + difficultyID + ", "); sql.Append("TypeID=" + typeID + ", "); sql.Append("FormatID=" + formatID + " "); sql.Append("WHERE QuestionID=" + questionID); } else { sql = new StringBuilder("INSERT INTO Questions ("); if (!string.IsNullOrWhiteSpace(image.Text)) { sql.Append("QuestionImage, "); } sql.Append("QuestionPublished, QuestionTitle, QuestionBody, CourseTopicRefID, "); sql.Append("DifficultyID, TypeID, FormatID, AuthorID) VALUES (1, '"); sql.Append(title.Text + "', '"); sql.Append(body.Text + "', "); sql.Append(courseTopicRefID + ", "); sql.Append(difficultyID + ", "); sql.Append(typeID + ", "); sql.Append(formatID + ", "); sql.Append(userID + ")"); } if (SqlManager.insert(sql.ToString())) { if (questionID != 0 && subquestionDt.Rows.Count > 0) { SqlManager.update("DELETE FROM SubQuestions WHERE QuestionID=" + questionID); } // Add subquestion(s) if present for (int i = 0; i < subquestionDt.Rows.Count; i++) { var sqlSubQ = new StringBuilder("INSERT INTO SubQuestions (QuestionID, SubQuestionID, SubQuestionText"); if (!string.IsNullOrWhiteSpace(subquestionDt.Rows[i]["SubQuestionImage"].ToString())) { sqlSubQ.Append(", SubQuestionImage) "); } else { sqlSubQ.Append(") "); } sqlSubQ.Append("SELECT QuestionID, "); sqlSubQ.Append(i + ", "); sqlSubQ.Append("'" + subquestionDt.Rows[i]["SubQuestionText"] + "' "); if (!string.IsNullOrWhiteSpace(subquestionDt.Rows[i]["SubQuestionImage"].ToString())) { sqlSubQ.Append(", '" + subquestionDt.Rows[i]["SubQuestionImage"] + "' "); } sqlSubQ.Append("FROM Questions WHERE QuestionTitle='"); sqlSubQ.Append(title.Text + "'"); SqlManager.insert(sqlSubQ.ToString()); } // Add answer(s) if present if (questionID != 0 && answerDt.Rows.Count > 0) { SqlManager.update("DELETE FROM Answers WHERE QuestionID=" + questionID); } for (int i = 0; i < answerDt.Rows.Count; i++) { var sqlAns = new StringBuilder("INSERT INTO Answers (QuestionID, AnswerID, AnswerText"); if (!string.IsNullOrWhiteSpace(answerDt.Rows[i]["AnswerImage"].ToString())) { sqlAns.Append(", AnswerImage) "); } else { sqlAns.Append(") "); } sqlAns.Append("SELECT QuestionID, "); sqlAns.Append(i + ", "); sqlAns.Append("'" + answerDt.Rows[i]["AnswerText"] + "' "); if (!string.IsNullOrWhiteSpace(answerDt.Rows[i]["AnswerImage"].ToString())) { sqlAns.Append(", '" + answerDt.Rows[i]["AnswerImage"] + "' "); } sqlAns.Append("FROM Questions WHERE QuestionTitle='"); sqlAns.Append(title.Text + "'"); SqlManager.insert(sqlAns.ToString()); } // Add history tag sql = new StringBuilder("INSERT INTO History (QuestionID, UserID, EditDescription)"); sql.Append("SELECT QuestionID, AuthorID, '"); if (questionID == 0) { sql.Append("Initial Commit"); } else { var commitMsg = new CommitMessage(); string msg = null; while (string.IsNullOrWhiteSpace(msg)) { commitMsg.ShowDialog(); msg = commitMsg.msg; } sql.Append(msg); } sql.Append("' FROM Questions WHERE QuestionTitle='"); sql.Append(title.Text + "'"); if (SqlManager.insert(sql.ToString())) { MessageBox.Show("Question successfully added to database.", "Question Added Successfully"); this.Close(); } else { MessageBox.Show("An error occured trying to insert into history table, see database admin.", "Error Occured", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("An error occured trying to insert into questions table, see database admin.", "Error Occured", MessageBoxButtons.OK, MessageBoxIcon.Error); } }