Take the 2-minute tour ×

I've got the following code:

// personCount = 7291; correct value
int personCount = (int)new OleDbCommand("SELECT COUNT(*) AS [count] FROM [Individual]", _access).ExecuteScalar();

OleDbCommand personQuery = new OleDbCommand("SELECT * FROM [Individual]", _access);

using (OleDbDataReader personReader = personQuery.ExecuteReader())
    int curPerson;

    while (personReader.Read())
        // This runs several times
        if (personReader.IsDBNull(0)) continue;
        // [snip] create a new Person and add it to people
    // at this point, curPerson == 7291 but the list is empty.

This is my exact code. Field 0 is the primary key, so should never be null, but every single row being returned from the database has all the fields set to DBNull! I can't see what I'm doing wrong, can anyone shed some light on this?

My connection string is:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\to\database.mdb

share|improve this question
When you are performing the query on the database, in what order are the columns being returned? Have you checked that? –  Kirtan Aug 21 '09 at 4:58
There's no way to tell. Like I said, every field is returning DBNull. (atleast, for the 2-3 records I checked manually before doing the if (IsDBNull) continue; to check en masse. –  Matthew Scharley Aug 21 '09 at 4:59
I can't believe. Are you sure that the ordinal position 0 is a primary key? Are _access and _accessGp belongs to the same database? –  AVD Aug 21 '09 at 5:00
You can do one thing to debug this situation - fill up a dataset using this query, and bind it to a datagrid. This way you'll know what the exact data is. –  Kirtan Aug 21 '09 at 5:00
@adatapost: they are the same variable, slipped through my sanitising. –  Matthew Scharley Aug 21 '09 at 5:01

1 Answer 1

up vote 2 down vote accepted

For one reason or another, using the * column selector was jumbling columns. Using a specific list fixes this. I'm still curious as to reasons why this might happen.

Fixed version:

OleDbCommand personQuery = new OleDbCommand("SELECT [ID], [Surname], ... FROM [Individual]", _access);
share|improve this answer
With * you are just saying "give me all columns". You are not defining order in which they should be returned. While order is usually same as in definition, in this particular case optimizer decided to play a little. Safest way of approaching would be to read fields by name and not by index. –  Josip Medved Aug 21 '09 at 5:30
Agreed, and I usually do in PHP/MySQL, but near as I can tell OleDb doesn't support this. Oh well. –  Matthew Scharley Aug 21 '09 at 5:39

Your Answer


