Get list of IDs from Access Database using OleDbDataReader

Question

Using a Microsoft Access database for a Web App Quiz Manager, I have table with a ID column that has a list of IDs which looks something like this:

ID   Answer   QuesDescription   QuesAnswer   QuestionNum
1    1        Example           Example      1
3    3        Example           Example      2
4    4        Example           Example      3
6    1        Example           Example      4

Using the query SELECT ID FROM (QuizName) with OleDbCommand I managed to get the ID values from the database and stored into OleDbDataReader reader. But i don't know how to get the ID values from the reader and store them as a String List. Does anyone know how to do this?

I've tried using stuff like

public List<string> GetIDValueFromQuestionNumber(string quizNumber)
        {
            try
            {
                string strSQL = string.Concat("SELECT count(ID) as RowCount FROM ", quizNumber);
                List<string> resourceNames = new List<string>();

                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    OleDbCommand command = new OleDbCommand(strSQL, connection);
                    connection.Open();
                    OleDbDataReader reader = command.ExecuteReader();
                    reader.Read();
                    int rowCount = (int)reader["RowCount"];

                    strSQL = string.Concat("SELECT ID FROM ", quizNumber);
                    command = new OleDbCommand(strSQL, connection);
                    using (reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            resourceNames.Add(" " + reader.GetString(0));
                        }
                    }
                        connection.Close();

                    for (int count = 0; count < rowCount; count++)
                    {
                        int value = (int)reader.GetValue(count);
                        resourceNames.Add(value.ToString());
                    }

                }
                
                return resourceNames;
            }
            catch (Exception e)
            {
                return null;
            }

        }

But to no luck.

I should note that these tables can vary in depth.

Answer 1

I suggest this approach.

Say a form - DataGridView to display our data.

And say a listbox to display the list of id that you build up into that List

So, this form:

And the button click code:

    private void button1_Click(object sender, EventArgs e)
    {
        // load up our data list with Hotels
        string strSQL =
                @"SELECT ID, FirstName, LastName, City, HotelName
                FROM tblHotelsA ORDER BY HotelName";

        DataTable rstData = MyRst(strSQL);
        dataGridView1.DataSource = rstData;

        // now build up a list of id in to string colleciton
        List<string> MyIDList = new List<string>();
        foreach (DataRow MyOneRow in rstData.Rows)
        {
            MyIDList.Add(MyOneRow["ID"].ToString());
        }

        // Lets set the id list to a listbox
        listBox1.DataSource = MyIDList;
    }

    DataTable MyRst(string strSQL)
    {
        DataTable rstData = new DataTable();
        using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB))
        {
            using (OleDbCommand cmdSQL = new OleDbCommand(strSQL, conn))
            {
                conn.Open();
                rstData.Load(cmdSQL.ExecuteReader());
            }
        }
        return rstData;
    }

And now we get/see this:

So, pull the table. Display it, do whatever.

Then use the SAME table, and simple loop each row, grab the ID and add to your list.

And of course, one would probably hide the "id" in the above list (just add the columns using edit columns - only add the ones you want). You can still get/grab/use ANY column from the data source - it not a requirement to display such columns.