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.

Any chance you could [edit] your question to share a full [mcve] which reproduces your problem? From [ask]: *Help others reproduce the problem... if your problem is with code you've written, you should include some...Include just enough code to allow others to reproduce the problem.* — dbc, Aug 13 '22 at 17:37
Does this answer your question? [converting resultset from OleDbDataReader into list](https://stackoverflow.com/questions/4089628/converting-resultset-from-oledbdatareader-into-list) — June7, Aug 13 '22 at 17:37
@June7 Unfortunatly it doesn't as what's outputted is just numbers such as 1, 3, 4 and 6 and not an actual name. as it's not things like the first row of "ID" or "Answer" being output. But all the rows of a single colum, in this case ID. — Priception, Aug 13 '22 at 18:00
@dbc ive updated the post with the full code sample. is this one more useful? — Priception, Aug 13 '22 at 18:02
Your code attempts to fill the `resourceNames` list twice. Once using `while (reader.Read())` and another using `for (int count = 0; count < rowCount; count++)`. Do neither work? Does `rowCount` have the correct value? — dbc, Aug 13 '22 at 18:08
@dbc those were my who attemps at filling the lists however neither of them work. They both throw an error saying: ```This exception was originally thrown at this call stack: [External Code] QuizManager.Scripts.DatabaseScripts.AccessDatabase.GetIDValueFromQuestionNumber(string) in AccessDatabase.cs ``` — Priception, Aug 13 '22 at 18:17
Your first attempt (`while reader.Read())` looks reasonable. Can you share 1) the full `ToString()` output of the exception you get, including the exception type, message, traceback and inner exception(s) if any? 2) Your database schema? Maybe the `quizNumber` name is wrong. — dbc, Aug 13 '22 at 18:21
Your queries are pulling only ID field - where do you expect names to come from? Your title says you want a list of IDs, not names. Edit question to include requested info. — June7, Aug 13 '22 at 18:22
@dbc The exception i get is ```System.InvalidOperationException: 'No data exists for the row/column.' ``` The Quiznumber is correct — Priception, Aug 13 '22 at 18:30
@June7 ???. I don't want any names. I want the IDs on the far left column to be stored as List within my c# web app. — Priception, Aug 13 '22 at 18:32

Albert D. Kallal · Answer 1 · 2022-08-13T19:55:31.390

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.

Thanks so much. That went and solved it. – Priception Aug 13 '22 at 21:15 — Priception, Aug 13 '22 at 21:15

Get list of IDs from Access Database using OleDbDataReader

1 Answers1