Populating an ArrayList with MySQL data

Question

I'm trying to populate two ArrayLists (listOfAnswers and listOfAnswerIDs) with fields from a database ('answer' and 'answer_id').

The following code seems to work perfectly fine when question_id=1 in the string cmdText. However, if I change this to 2 or 3, the ArrayLists remain empty and I don't know why. Any ideas?

I think it's to do with the string cmdGetAnswersQuery as "SELECT * FROM answers WHERE question_id=2" works...

    string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
    MySqlConnection conn = new MySqlConnection(connStr);
    MySqlDataReader reader;

    ArrayList listOfAnswerIDs = new ArrayList();
    ArrayList listOfAnswers = new ArrayList();
    try
    {
        conn.Open();
        string cmdText = "SELECT * FROM questions_t WHERE question_id=2";
        MySqlCommand cmd = new MySqlCommand(cmdText, conn);
        cmd.Parameters.Add("@ModuleID", MySqlDbType.Int32);
        cmd.Parameters["@ModuleID"].Value = ddlModules.SelectedValue;

        reader = cmd.ExecuteReader();

        if (reader.Read())
        {
            lblQuestion.Text = reader["question"].ToString();
            ViewState["QuestionID"] = reader["question_id"].ToString();
            ViewState["AnswerID"] = reader["correct_answer_id"].ToString();

            reader.Close();

            string cmdGetAnswersQuery = "SELECT * FROM answers WHERE question_id=@QuestionID";
            MySqlCommand cmdGetAnswers = new MySqlCommand(cmdGetAnswersQuery, conn);
            cmdGetAnswers.Parameters.Add("@QuestionID", MySqlDbType.Int32);
            cmdGetAnswers.Parameters["@QuestionID"].Value = ViewState["AnswerID"];

            reader = cmdGetAnswers.ExecuteReader();

            while (reader.Read())
            {
                listOfAnswerIDs.Add(reader["answer_id"].ToString());
                listOfAnswers.Add(reader["answer"].ToString());
            }
            reader.Close();
            populateAnswers(listOfAnswers, listOfAnswerIDs);
        }
        else
        {
            reader.Close();
            lblError.Text = "(no questions found)";
        }
    }
    catch
    {
        lblError.Text = "Database connection error - failed to insert record.";
    }
    finally
    {
        conn.Close();
    }

The actual database contents are shown here: https://i.sstatic.net/UQKX1.png https://i.sstatic.net/6lNLN.png

Answer 1

I can't say for sure that this is the problem, but the first thing I'd look at is this line of code:

cmdGetAnswers.Parameters["@QuestionID"].Value = ViewState["AnswerID"];

I suspect you want that to be ViewState["QuestionID"].