Reading users from Access into a list returns only one user

Question

I'm working on a WPF App in C# with VS 2010 SP1.

I've looked at quite a few examples on here and elsewhere around the web, and my code seems correct, but when I attempt to read rows from an Access DB into a class then into an ObservableCollection (I've also tried just a List), only one ends up in the collection. I then attempt to display a list of usernames in a ListBox, and of course only the one user in the collection appears.

I've been stuck on this for a while now. Any help would be greatly appreciated. I'm hoping it's something very trivial that I'm just overlooking.

User Class:

public class User
{
    public User() { }
    public int UserID { get; set; }
    public string UserName { get; set; }
    public string UserTitle { get; set; }
    public string UserArea { get; set; }
}

Code:

// Setting up DB stuff.
string s_ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data " + 
    "Source=|DataDirectory|\\CloseoutApp.accdb";
OleDbConnection AccessConn = new OleDbConnection(s_ConnString);

string s_Query = "SELECT UserID, UserName, UserTitle, UserArea FROM Users " +
    "ORDER BY UserID;";

OleDbCommand AccessCmd = new OleDbCommand(s_Query, AccessConn);
OleDbDataReader rdr;

AccessConn.Open();
rdr = AccessCmd.ExecuteReader();

// Collection of Users.
ObservableCollection<User> userList = new ObservableCollection<User>();

try
        {
            // Read each user from DB into a User instance, then add that instance to the userList.
            while(rdr.Read())
            {
                User newUser = new User();
                newUser.UserID = rdr.GetInt32(0);
                newUser.UserName = rdr.GetString(1);
                newUser.UserTitle = rdr.GetString(2);
                newUser.UserArea = rdr.GetString(3);

                userList.Add(newUser);
            }

        }

catch(Exception e)
        {
            //Update Statusbar
        }

// Close the DB connection.
rdr.Close();
AccessConn.Close();

// Add users to the ListBox.
foreach(User u in userList)
    {
        lb_Users.Items.Add(u.UserName);  
    }

I assume there is more than one user in the table? I got rid of my answer since it didn't solve your issue. I'll add it back in if I have something else to add — Dan, Feb 24 '12 at 20:39
If you step through in the debugger are you seeing `rdr.Read()` return `true` the first time and `false` the second (i.e. the loop is only executed once)? Are any `Exception`s being caught in your `catch` block? What if you move the `try...catch` inside of the loop (not sure if that makes sense depending on what `//Update Statusbar` does)? — Lance U. Matthews, Feb 24 '12 at 20:42
Try running your SQL directly in Access to make sure that you are getting the intended results. — Jonathan Nixon, Feb 24 '12 at 21:10
@DanNewhouse - Yes, there are three test users in the table. — Ashton, Feb 24 '12 at 21:23
@Ashton Cool, figured there would be > 1, but you never know ;) — Dan, Feb 24 '12 at 21:24
@Guthwulf - The query returns all three users with the correct fields. — Ashton, Feb 24 '12 at 21:25
I have my solution. The UserTitle and UserArea fields were null for the 2nd and 3rd users in the DB. I added in dummy data and the code @DanNewHouse provided worked flawlessly. It indeed was something trivial I overlooked. So, as a followup, how would I go about handling null values in the program for future reference? Thanks everybody for your input. — Ashton, Feb 24 '12 at 21:34

Dan · Accepted Answer · 2012-02-24T21:41:59.747

I had something similar happen to me when I was handling my database connection in a similar way.

Give the below code a try:

// Setting up DB stuff.
        string s_ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data " +
            "Source=|DataDirectory|\\CloseoutApp.accdb";

        string s_Query = "SELECT UserID, UserName, UserTitle, UserArea FROM Users " +
            "ORDER BY UserID;";

        ObservableCollection<User> userList = new ObservableCollection<User>();

        using (OleDbConnection AccessConn = new OleDbConnection(s_ConnString))
        {
            using (OleDbCommand AccessCmd = AccessConn.CreateCommand())
            {
                AccessCmd.CommandText = s_Query;

                try
                {
                    AccessConn.Open();

                    OleDbDataReader rdr = AccessCmd.ExecuteReader();

                    while (rdr.Read())
                    {
                        User newUser = new User();
                        newUser.UserID = rdr.GetInt32(0);
                        newUser.UserName = rdr.GetValue(1).ToString();
                        newUser.UserTitle = rdr.GetValue(2).ToString();
                        newUser.UserArea = rdr.GetValue(3).ToString();

                        userList.Add(newUser);
                    }

                    rdr.Close();

                }
                catch(Exception ex)
                {
                    //do something with ex
                }

            }

        }

        // Add users to the ListBox.
        foreach (User u in userList)
        {
            lb_Users.Items.Add(u.UserName);
        }

Tried it and got the same exact results. Confirmed that the userList only has one entry by debugging as well. — Ashton, Feb 24 '12 at 20:24
@Ashton I made a tweak in the while() loop. Try that against those null values and see if calling the `ToString()` on `GetValue()` will better deal with the null value problem — Dan, Feb 24 '12 at 21:42

Reading users from Access into a list returns only one user

1 Answers1