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