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);
}
}