OleDB only returning DbNull, what have I done wrong?

Question

I've got the following code:

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

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

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

    while (personReader.Read())
    {
        curPerson++;
        // 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

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 04: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 04: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? — KV Prajapati, Aug 21 '09 at 05: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 05:00
@adatapost: they are the same variable, slipped through my sanitising. — Matthew Scharley, Aug 21 '09 at 05:01
@adatapost: you are right though, for some reason using `*` as the column selector was jumbling them up... Any reason Jet would return columns in a different order to the way they are specified in Access? — Matthew Scharley, Aug 21 '09 at 05:14

score 2 · Accepted Answer · answered Aug 21 '09 at 05:18

2

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);

answered Aug 21 '09 at 05:18

Matthew Scharley

127,823
52
194
222

2

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 05: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 05:39

OleDB only returning DbNull, what have I done wrong?

1 Answers1