private int getCurrentQuestionIDFromSession()
{
//if questionId not in session, set it up with default one
if (HttpContext.Current.Session[QUESTION_ID] == null)
{
//TODO Find out min questionID from question Table and use that as starting question id
//answer: SELECT MIN(questionId) FROM TestQuestion;
try
{
using (SqlConnection connection = DBUtility.ConnectToSQLDB())
{
SqlCommand minimumNumberCommand = new SqlCommand("SELECT MIN(questionId) FROM Questions", connection);
//RUN command and execute straight away , execute scalar gives back the first row and first value in the first column
int min = (int)minimumNumberCommand.ExecuteScalar();
HttpContext.Current.Session[QUESTION_ID] = min;
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
//get QuestionID stored in current clients session
return((int)HttpContext.Current.Session[QUESTION_ID]);
}
protected void saveButton_Click(object sender, EventArgs e)
{
List <Products> products = GetListOfProductsFromSession();
using (SqlConnection connection = DBUtility.ConnectToSQLDB())
{
foreach (Products product in products)
{
SqlCommand command = new SqlCommand("INSERT INTO Products (name, description, price)" +
" VALUES ('" + product.Name + "','" + product.description + "','" + product.price + "')", connection);
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected <= 0)
{
//could not insert
//do something about it like show to user that the stuff didnt insert properly
}
}
}
//empty products out of session
HttpContext.Current.Session["products"] = null;
//reload page
Response.Redirect("AddProduct.aspx");
}
protected void addButton_Click(object sender, EventArgs e)
{
try
{
Products product = new Products();
product.Name = nameTextbox.Text;
product.description = descriptionTextBox.Text;
product.price = float.Parse(priceTextBox.Text);
using (SqlConnection connection = DBUtility.ConnectToSQLDB())
{
//create insert using parameters
//scope_identity gives the id of what ever you just inserted in the database
SqlCommand command = new SqlCommand("INSERT INTO Products (name, description, price) VALUES (@name, @description, @price); SELECT CAST(scope_identity() as int);", connection);
//add parameter
//prevents sql injection
command.Parameters.Add("@name", SqlDbType.VarChar, 50);
command.Parameters["@name"].Value = product.Name;
command.Parameters.Add("@description", SqlDbType.VarChar, 200);
command.Parameters["@description"].Value = product.description;
command.Parameters.Add("@price", SqlDbType.Float);
command.Parameters["@price"].Value = product.price;
//execute scalar returns first column and row of the comman results
int newId = (int)command.ExecuteScalar();
IdLabel.Text = "New User Id: " + newId;
}
}
catch (Exception ex)
{
//problems
Response.Write(ex.Message);
IdLabel.Text = ex.Message;
}
}
protected void skipBtn_Click(object sender, EventArgs e)
{
int currentQuestionID = getCurrentQuestionIDFromSession();
//get extra questions list from session if it exist, if not make a new one
List <int> extraQuestions = new List <int>();
if (HttpContext.Current.Session[SESSION_EXTRA_QUESTION] != null)
{
extraQuestions = (List <int>)HttpContext.Current.Session[SESSION_EXTRA_QUESTION];
}
try
{
using (SqlConnection connection = DBUtility.ConnectToSQLDB())
{
if (extraQuestions.Count <= 0)
{
SqlCommand command = new SqlCommand("SELECT * FROM Questions WHERE questionId = " + currentQuestionID, connection);
//RUN command and dump results into reader
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
//get index for the nextQuestion column
int nextQuestionColumnIndex = reader.GetOrdinal("nextQuestion");
//check if value in this row and column is NULL
if (reader.IsDBNull(nextQuestionColumnIndex))
{
List <Answers> answers = GetListOfAnswersFromSession();
foreach (Answers answer in answers)
{
//insert User details and get userId
Users users = (Users)HttpContext.Current.Session["currentUserId"];
SqlCommand commandInsertUsers = new SqlCommand("INSERT INTO Users (firstName, lastName, dob, phoneNumber, date, anonymous, ipAddress) VALUES (@firstName, @lastName, @dob, @phoneNumber, @date, @anonymous, @ipAddress); SELECT CAST(scope_identity() as int);", connection);
//add parameter
//prevents sql injection
commandInsertUsers.Parameters.Add("@firstName", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@firstName"].Value = users.firstName;
commandInsertUsers.Parameters.Add("@lastName", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@lastName"].Value = users.lastName;
commandInsertUsers.Parameters.Add("@dob", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@dob"].Value = users.dob;
commandInsertUsers.Parameters.Add("@phoneNumber", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@phoneNumber"].Value = users.phoneNumber;
commandInsertUsers.Parameters.Add("@anonymous", SqlDbType.Int, 4);
commandInsertUsers.Parameters["@anonymous"].Value = users.anon;
commandInsertUsers.Parameters.Add("@date", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@date"].Value = users.date;
commandInsertUsers.Parameters.Add("@ipAddress", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@ipAddress"].Value = users.ipAddress;
//get the userId from database
int newUserId = (int)commandInsertUsers.ExecuteScalar();
Console.WriteLine("New Product Id: " + newUserId);
SqlCommand commandInsert = new SqlCommand("INSERT INTO Answers (optionId, answerText, userId) VALUES (@optionId, @answerText, @userId);", connection);
//add parameter
//prevents sql injection
commandInsert.Parameters.Add("@optionId", SqlDbType.VarChar, 50);
commandInsert.Parameters["@optionId"].Value = answer.optionId;
if (commandInsert.Parameters["@optionId"].Value == null)
{
commandInsert.Parameters["@optionId"].Value = DBNull.Value;
}
commandInsert.Parameters.Add("@answerText", SqlDbType.VarChar, 50);
commandInsert.Parameters["@answerText"].Value = answer.answerText;
commandInsert.Parameters.Add("@userId", SqlDbType.Int, 4);
commandInsert.Parameters["@userId"].Value = newUserId;
var rowsAffected = commandInsert.ExecuteNonQuery();
if (rowsAffected <= 0)
{
//could not insert
//do something about it like show to user that the stuff didnt insert properly
Console.WriteLine("failed to write");
}
}
//empty products out of session
HttpContext.Current.Session["answers"] = null;
//clear all session
Session.Clear();
//if null, at end of survey
Response.Redirect("ThankYouPage.aspx");
}
else
{
//If not null, get the value of the nextQuestion column so we can load that question up next
int nextQuestionId = (int)reader["nextQuestion"];
//save this as the current questionId in session.
HttpContext.Current.Session["questionID"] = nextQuestionId;
HttpContext.Current.Session["currentUserId"] = currentUserId;
//reload this page
Response.Redirect("SurveyQuestions.aspx");
}
}
}
else
{
//if we do have questions on that list
//set current question to load to be equal to first question in the extraQuestions List
HttpContext.Current.Session[QUESTION_ID] = extraQuestions[0];
//add to skip button session
HttpContext.Current.Session[EXTRA_QUESTION_AND_SKIP_BUTTON] = extraQuestions[0];
//remove this question from the list
extraQuestions.RemoveAt(0);
//save extraQuestionlist into session
HttpContext.Current.Session[SESSION_EXTRA_QUESTION] = extraQuestions;
HttpContext.Current.Session["currentUserId"] = currentUserId;
//reload this page
Response.Redirect("SurveyQuestions.aspx");
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
protected void Page_Load(object sender, EventArgs e)
{
skipBtn.Style["visibility"] = "hidden";
Console.WriteLine(HttpContext.Current.Session[EXTRA_QUESTION_AND_SKIP_BUTTON]);
try
{
currentUserId = (int)HttpContext.Current.Session["currentUserId"];
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
//GetIpAddress(out ipAddress);
if (AppSession.isLoggedIn())
{
titleSurvey.Text = "Welcome " + AppSession.getUsername();
}
//get current questionID
int currentQuestionID = getCurrentQuestionIDFromSession();
using (SqlConnection connection = DBUtility.ConnectToSQLDB())
{
SqlCommand command = new SqlCommand("SELECT * FROM Questions WHERE questionId = " + currentQuestionID, connection);
//RUN command and dump results into reader
SqlDataReader reader = command.ExecuteReader();
//must do one read to gete onto first row of results in reader (can only show 1 question per page, so 1 read is all we need)
if (reader.Read())
{
//get question text and put it in our label
string questionText = (string)reader["questionText"];
questionLabel.Text = questionText;
//makes all the value in the reader typeName to lowercase
string questionType = ((string)reader["questionType"]).ToLower();
if (questionType == "textbox")
{
TextBox textBox = new TextBox();
textBox.ID = "questionTextBox";
QuestionPlaceHolder.Controls.Add(textBox);
}
else if (questionType == "checkbox")
{
CheckBoxList checkBoxList = new CheckBoxList();
checkBoxList.ID = "questionCheckBox";
//get options associated with this current question and dump into checkBoxList
SqlCommand optionCommand = new SqlCommand("SELECT * FROM Options WHERE questionId = " + currentQuestionID, connection);
SqlDataReader optionReader = optionCommand.ExecuteReader();
//loop through all the question option results and chuck into the checkBox
while (optionReader.Read())
{
//takes the text value from database as first input and id as last input
ListItem item = new ListItem((string)optionReader["optionText"], optionReader["optionId"].ToString());
checkBoxList.Items.Add(item);
}
QuestionPlaceHolder.Controls.Add(checkBoxList);
HttpContext.Current.Session["currentUserId"] = currentUserId;
if (HttpContext.Current.Session[EXTRA_QUESTION_AND_SKIP_BUTTON] != null)
{
//show when there is extra question
skipBtn.Style["visibility"] = "show";
HttpContext.Current.Session[EXTRA_QUESTION_AND_SKIP_BUTTON] = null;
}
else
{
//make it hidden when there is no extra question
skipBtn.Style["visibility"] = "hidden";
}
}
else if (questionType == "radiobutton")
{
RadioButtonList radioButtonList = new RadioButtonList();
radioButtonList.ID = "questionRadioButton";
//get options associated with this current question and dump into checkBoxList
SqlCommand optionCommand = new SqlCommand("SELECT * FROM Options WHERE questionId = " + currentQuestionID, connection);
SqlDataReader optionReader = optionCommand.ExecuteReader();
//loop through all the question option results and chuck into the checkBox
while (optionReader.Read())
{
//takes the text value from database as first input and id as last input
ListItem item = new ListItem((string)optionReader["optionText"], optionReader["optionId"].ToString());
radioButtonList.Items.Add(item);
}
QuestionPlaceHolder.Controls.Add(radioButtonList);
}
}
}
}
protected void nextBtn_Click(object sender, EventArgs e)
{
int currentQuestionID = getCurrentQuestionIDFromSession();
//get extra questions list from session if it exist, if not make a new one
List <int> extraQuestions = new List <int>();
if (HttpContext.Current.Session[SESSION_EXTRA_QUESTION] != null)
{
extraQuestions = (List <int>)HttpContext.Current.Session[SESSION_EXTRA_QUESTION];
}
try
{
using (SqlConnection connection = DBUtility.ConnectToSQLDB())
{
//check if it was a textbox question
TextBox questionTextBox = (TextBox)QuestionPlaceHolder.FindControl("questionTextBox");
if (questionTextBox != null)
{
//if it was a textBox, do something with the answers
string typedAnswer = questionTextBox.Text;
HttpContext.Current.Session[SESSION_ANSWER_TEXTBOX] = typedAnswer;
//TODO FOR STEVEN
//get hold of optionId, the answerText and userID and add to session
try
{
Answers answer = new Answers();
answer.optionId = null;
answer.answerText = typedAnswer;
///get list from session
List <Answers> answers = GetListOfAnswersFromSession();
answers.Add(answer);
//save this list into the session (overwrite existing list if any)
HttpContext.Current.Session["answers"] = answers;
}
catch (ArgumentException argEx)
{
Response.Write(argEx.Message);
}
catch (FormatException formatEx)
{
Response.Write(formatEx.Message);
}
catch (OverflowException overflowEx)
{
Response.Write(overflowEx.Message);
}
}
//check if it was a checkbox question
CheckBoxList questionCheckBoxList = (CheckBoxList)QuestionPlaceHolder.FindControl("questionCheckBox");
if (questionCheckBoxList != null)
{
List <int> listOptionId = new List <int>();
foreach (ListItem item in questionCheckBoxList.Items)
{
if (item.Selected)
{
try
{
Answers answer = new Answers();
int optionId = int.Parse(item.Value);// may throw exception.
listOptionId.Add(optionId);
SqlCommand optionsCommand = new SqlCommand("SELECT nextQuestionId FROM Options WHERE optionId = " + optionId, connection);
//RUN command and execute straight away , execute scalar gives back the first row and first value in the first column
var dbResult = optionsCommand.ExecuteScalar();
if (dbResult.ToString() != "")
{
extraQuestions.Add((int)dbResult);
extraQuestionAndSkipButton.Add((int)dbResult);
}
//get hold of optionId, the answerText and userID and add to session
try
{
answer.optionId = optionId;
answer.answerText = item.ToString();
///get list from session
List <Answers> answers = GetListOfAnswersFromSession();
answers.Add(answer);
//save this list into the session (overwrite existing list if any)
HttpContext.Current.Session["answers"] = answers;
}
catch (ArgumentException argEx)
{
Response.Write(argEx.Message);
}
catch (FormatException formatEx)
{
Response.Write(formatEx.Message);
}
catch (OverflowException overflowEx)
{
Response.Write(overflowEx.Message);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
}
}
HttpContext.Current.Session[SESSION_ANSWER_CHECKBOX] = listOptionId;
Debug.WriteLine(HttpContext.Current.Session[SESSION_ANSWER_CHECKBOX]);
Debug.WriteLine(listOptionId);
}
//check if it was a radiobutton question
RadioButtonList questionRadioButtonList = (RadioButtonList)QuestionPlaceHolder.FindControl("questionRadioButton");
if (questionRadioButtonList != null)
{
string selectedAnswer = questionRadioButtonList.SelectedItem.Text;
HttpContext.Current.Session[SESSION_ANSWER_TEXTBOX] = selectedAnswer;
try
{
int optionId = int.Parse(questionRadioButtonList.SelectedValue);// may throw exception.
SqlCommand optionsCommand = new SqlCommand("SELECT nextQuestionId FROM Options WHERE optionId = " + optionId, connection);
//RUN command and execute straight away , execute scalar gives back the first row and first value in the first column
var dbResult = optionsCommand.ExecuteScalar();
if (dbResult.ToString() != "")
{
extraQuestions.Add((int)dbResult);
}
//TODO FOR STEVEN
//get hold of optionId, the answerText and userID and add to session
try
{
Answers answer = new Answers();
answer.optionId = optionId;
answer.answerText = selectedAnswer;
///get list from session
List <Answers> answers = GetListOfAnswersFromSession();
answers.Add(answer);
//save this list into the session (overwrite existing list if any)
HttpContext.Current.Session["answers"] = answers;
}
catch (ArgumentException argEx)
{
Response.Write(argEx.Message);
}
catch (FormatException formatEx)
{
Response.Write(formatEx.Message);
}
catch (OverflowException overflowEx)
{
Response.Write(overflowEx.Message);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
}
if (extraQuestions.Count <= 0)
{
SqlCommand command = new SqlCommand("SELECT * FROM Questions WHERE questionId = " + currentQuestionID, connection);
//RUN command and dump results into reader
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
//get index for the nextQuestion column
int nextQuestionColumnIndex = reader.GetOrdinal("nextQuestion");
//check if value in this row and column is NULL
if (reader.IsDBNull(nextQuestionColumnIndex))
{
//insert User details and get userId
Users users = (Users)HttpContext.Current.Session["currentUser"];
SqlCommand commandInsertUsers = new SqlCommand("INSERT INTO Users (firstName, lastName, dob, phoneNumber, date, anonymous, ipAddress) VALUES (@firstName, @lastName, @dob, @phoneNumber, @date, @anonymous, @ipAddress); SELECT CAST(scope_identity() as int);", connection);
//add parameter
//prevents sql injection
commandInsertUsers.Parameters.Add("@firstName", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@firstName"].Value = users.firstName;
commandInsertUsers.Parameters.Add("@lastName", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@lastName"].Value = users.lastName;
commandInsertUsers.Parameters.Add("@dob", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@dob"].Value = users.dob;
commandInsertUsers.Parameters.Add("@phoneNumber", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@phoneNumber"].Value = users.phoneNumber;
commandInsertUsers.Parameters.Add("@anonymous", SqlDbType.Int, 4);
commandInsertUsers.Parameters["@anonymous"].Value = users.anon;
commandInsertUsers.Parameters.Add("@date", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@date"].Value = users.date;
commandInsertUsers.Parameters.Add("@ipAddress", SqlDbType.VarChar, 50);
commandInsertUsers.Parameters["@ipAddress"].Value = users.ipAddress;
//get the userId from database
int newUserId = (int)commandInsertUsers.ExecuteScalar();
Console.WriteLine("New Product Id: " + newUserId);
//Insert Answers
List <Answers> answers = GetListOfAnswersFromSession();
foreach (Answers answer in answers)
{
SqlCommand commandInsert = new SqlCommand("INSERT INTO Answers (optionId, answerText, userId) VALUES (@optionId, @answerText, @userId);", connection);
//add parameter
//prevents sql injection
commandInsert.Parameters.Add("@optionId", SqlDbType.VarChar, 50);
commandInsert.Parameters["@optionId"].Value = answer.optionId;
if (commandInsert.Parameters["@optionId"].Value == null)
{
commandInsert.Parameters["@optionId"].Value = DBNull.Value;
}
commandInsert.Parameters.Add("@answerText", SqlDbType.VarChar, 50);
commandInsert.Parameters["@answerText"].Value = answer.answerText;
commandInsert.Parameters.Add("@userId", SqlDbType.Int, 4);
commandInsert.Parameters["@userId"].Value = newUserId;
var rowsAffected = commandInsert.ExecuteNonQuery();
if (rowsAffected <= 0)
{
//could not insert
//do something about it like show to user that the stuff didnt insert properly
Console.WriteLine("failed to write");
}
}
//empty products out of session
HttpContext.Current.Session["answers"] = null;
//clear all session
Session.Clear();
//if null, at end of survey
Response.Redirect("ThankYouPage.aspx");
}
else
{
//If not null, get the value of the nextQuestion column so we can load that question up next
int nextQuestionId = (int)reader["nextQuestion"];
//save this as the current questionId in session.
HttpContext.Current.Session["questionID"] = nextQuestionId;
HttpContext.Current.Session["currentUserId"] = currentUserId;
//reload this page
Response.Redirect("SurveyQuestions.aspx");
}
}
}
else
{
//if we do have questions on that list
//set current question to load to be equal to first question in the extraQuestions List
HttpContext.Current.Session[QUESTION_ID] = extraQuestions[0];
//add to skip button session
HttpContext.Current.Session[EXTRA_QUESTION_AND_SKIP_BUTTON] = extraQuestions[0];
//remove this question from the list
extraQuestions.RemoveAt(0);
//save extraQuestionlist into session
HttpContext.Current.Session[SESSION_EXTRA_QUESTION] = extraQuestions;
HttpContext.Current.Session["currentUserId"] = currentUserId;
//reload this page
Response.Redirect("SurveyQuestions.aspx");
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
protected void btnSearch_Click(object sender, EventArgs e)
{
string start = "SELECT * FROM [Users] JOIN Answers ON [Users].userId = Answers.userId WHERE [Users].userId IN((SELECT userId FROM Answers WHERE";
string end = "))";
optIDs = "";
//For each loops to check whether items has been selected line 185 to 311
foreach (ListItem checkbox in CheckBoxListGender.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListInputState.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListBank.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListBankServicesCommbank.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListBankServicesNAB.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListBankServicesANZ.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListNewspaper.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListSports.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
foreach (ListItem checkbox in CheckBoxListTravel.Items)
{
if (checkbox.Selected)
{
if (optIDs == "")
{
optIDs = " optionId = " + checkbox.Value;
}
else
{
optIDs = optIDs + " OR optionId = " + checkbox.Value;
}
}
}
//Where admin selects, it will do these queries
using (SqlConnection connection = DBUtility.ConnectToSQLDB())
{
SqlCommand command = new SqlCommand();
command.Connection = connection;
if (optIDs != "")
{
StringBuilder sbCommand = new
StringBuilder(start + optIDs);
if (inputFirstname.Value.Trim() != "")
{
sbCommand.Append(" AND firstName=@firstName");
SqlParameter param = new SqlParameter("@firstName", inputFirstname.Value);
command.Parameters.Add(param);
}
if (inputLastname.Value.Trim() != "")
{
sbCommand.Append(" AND lastName=@lastName");
SqlParameter param = new SqlParameter("@lastName", inputLastname.Value);
command.Parameters.Add(param);
}
if (inputPostcode.Value.Trim() != "")
{
sbCommand.Append(" AND answerText=@Postcode");
SqlParameter param = new SqlParameter("@Postcode", inputPostcode.Value);
command.Parameters.Add(param);
}
if (inputSuburb.Value.Trim() != "")
{
sbCommand.Append(" AND answerText=@Suburb");
SqlParameter param = new SqlParameter("@Suburb", inputSuburb.Value);
command.Parameters.Add(param);
}
sbCommand.Append(end);
command.CommandText = sbCommand.ToString();
command.CommandType = CommandType.Text;
SqlDataReader rdr = command.ExecuteReader();
SearchResultsGridView.DataSource = rdr;
SearchResultsGridView.DataBind();
}
else
{
StringBuilder sbCommand = new
StringBuilder("SELECT * FROM [Users] JOIN Answers ON [Users].userId = Answers.userId");
if (inputFirstname.Value.Trim() != "")
{
sbCommand.Append(" AND firstName=@firstName");
SqlParameter param = new SqlParameter("@firstName", inputFirstname.Value);
command.Parameters.Add(param);
}
if (inputLastname.Value.Trim() != "")
{
sbCommand.Append(" AND lastName=@lastName");
SqlParameter param = new SqlParameter("@lastName", inputLastname.Value);
command.Parameters.Add(param);
}
if (inputPostcode.Value.Trim() != "")
{
sbCommand.Append(" AND answerText=@Postcode");
SqlParameter param = new SqlParameter("@Postcode", inputPostcode.Value);
command.Parameters.Add(param);
}
if (inputSuburb.Value.Trim() != "")
{
sbCommand.Append(" AND answerText=@Suburb");
SqlParameter param = new SqlParameter("@Suburb", inputSuburb.Value);
command.Parameters.Add(param);
}
command.CommandText = sbCommand.ToString();
command.CommandType = CommandType.Text;
SqlDataReader rdr = command.ExecuteReader();
SearchResultsGridView.DataSource = rdr;
SearchResultsGridView.DataBind();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!AdminAppSession.isLoggedIn())
{
Response.Redirect("AdminLogin.aspx");
return;
}
titleAdminPage.Text = "Welcome " + AdminAppSession.getUsername();
try
{
using (SqlConnection connection = DBUtility.ConnectToSQLDB())
{
//numbers of questionId for SQL purposes
int questionIdState = 3;
int questionIdBank = 6;
int questionIdBankCommbank = 8;
int questionIdBankNAB = 11;
int questionIdBankANZ = 12;
int questionIdNewspaper = 7;
int questionIdGender = 1;
int questionIdSports = 9;
int questionIdTravel = 10;
//checking whether the page has been render before, if no then do so.
if (!IsPostBack)
{
//State
SqlCommand optionCommandState = new SqlCommand("SELECT * FROM Options WHERE questionId = " + questionIdState, connection);
SqlDataReader optionReaderState = optionCommandState.ExecuteReader();
//loop through all the question option results and chuck into the checkBox
while (optionReaderState.Read())
{
//takes the text value from database as first input and id as last input
ListItem item = new ListItem((string)optionReaderState["optionText"], optionReaderState["optionId"].ToString());
CheckBoxListInputState.Items.Add(item);
}
//Bank
SqlCommand optionCommandBank = new SqlCommand("SELECT * FROM Options WHERE questionId = " + questionIdBank, connection);
SqlDataReader optionReaderBank = optionCommandBank.ExecuteReader();
//loop through all the question option results and chuck into the checkBox
while (optionReaderBank.Read())
{
//takes the text value from database as first input and id as last input
ListItem item = new ListItem((string)optionReaderBank["optionText"], optionReaderBank["optionId"].ToString());
CheckBoxListBank.Items.Add(item);
}
//-----Bank Services----
//Bank Services Commbank
SqlCommand optionCommandBankServicesCommbank = new SqlCommand("SELECT * FROM Options WHERE questionId = " + questionIdBankCommbank, connection);
SqlDataReader optionReaderBankServicesCommbank = optionCommandBankServicesCommbank.ExecuteReader();
//loop through all the question option results and chuck into the checkBox
while (optionReaderBankServicesCommbank.Read())
{
//takes the text value from database as first input and id as last input
ListItem item = new ListItem((string)optionReaderBankServicesCommbank["optionText"], optionReaderBankServicesCommbank["optionId"].ToString());
CheckBoxListBankServicesCommbank.Items.Add(item);
}
//Bank Services NAB
SqlCommand optionCommandBankServicesNAB = new SqlCommand("SELECT * FROM Options WHERE questionId = " + questionIdBankNAB, connection);
SqlDataReader optionReaderBankServicesNAB = optionCommandBankServicesNAB.ExecuteReader();
//loop through all the question option results and chuck into the checkBox
while (optionReaderBankServicesNAB.Read())
{
//takes the text value from database as first input and id as last input
ListItem item = new ListItem((string)optionReaderBankServicesNAB["optionText"], optionReaderBankServicesNAB["optionId"].ToString());
CheckBoxListBankServicesNAB.Items.Add(item);
}
//Bank Services ANZ
SqlCommand optionCommandBankServicesANZ = new SqlCommand("SELECT * FROM Options WHERE questionId = " + questionIdBankANZ, connection);
SqlDataReader optionReaderBankServicesANZ = optionCommandBankServicesANZ.ExecuteReader();
//loop through all the question option results and chuck into the checkBox
while (optionReaderBankServicesANZ.Read())
{
//takes the text value from database as first input and id as last input
ListItem item = new ListItem((string)optionReaderBankServicesANZ["optionText"], optionReaderBankServicesANZ["optionId"].ToString());
CheckBoxListBankServicesANZ.Items.Add(item);
}
//-----Newspaper related-----
//Newspaper
SqlCommand optionCommandNewspaper = new SqlCommand("SELECT * FROM Options WHERE questionId = " + questionIdNewspaper, connection);
SqlDataReader optionReaderNewspaper = optionCommandNewspaper.ExecuteReader();
//loop through all the question option results and chuck into the checkBox
while (optionReaderNewspaper.Read())
{
//takes the text value from database as first input and id as last input
ListItem item = new ListItem((string)optionReaderNewspaper["optionText"], optionReaderNewspaper["optionId"].ToString());
CheckBoxListNewspaper.Items.Add(item);
}
//Gender
SqlCommand optionCommandGender = new SqlCommand("SELECT * FROM Options WHERE questionId = " + questionIdGender, connection);
SqlDataReader optionReaderGender = optionCommandGender.ExecuteReader();
//loop through all the question option results and chuck into the checkBox
while (optionReaderGender.Read())
{
//takes the text value from database as first input and id as last input
ListItem item = new ListItem((string)optionReaderGender["optionText"], optionReaderGender["optionId"].ToString());
CheckBoxListGender.Items.Add(item);
}
//Sports
SqlCommand optionCommandSports = new SqlCommand("SELECT * FROM Options WHERE questionId = " + questionIdSports, connection);
SqlDataReader optionReaderSports = optionCommandSports.ExecuteReader();
//loop through all the question option results and chuck into the checkBox
while (optionReaderSports.Read())
{
//takes the text value from database as first input and id as last input
ListItem item = new ListItem((string)optionReaderSports["optionText"], optionReaderSports["optionId"].ToString());
CheckBoxListSports.Items.Add(item);
}
//Travel
SqlCommand optionCommandTravel = new SqlCommand("SELECT * FROM Options WHERE questionId = " + questionIdTravel, connection);
SqlDataReader optionReaderTravel = optionCommandTravel.ExecuteReader();
//loop through all the question option results and chuck into the checkBox
while (optionReaderTravel.Read())
{
//takes the text value from database as first input and id as last input
ListItem item = new ListItem((string)optionReaderTravel["optionText"], optionReaderTravel["optionId"].ToString());
CheckBoxListTravel.Items.Add(item);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
