none
OleDbDataReader return order RRS feed

  • Question

  • Using OleDbDataReader.Read() against a table in Access, I need to retrieve a value from the newest record.

    If I loop over the records returned, am I guaranteed that the last read will be the last record in the table?  Or is the order not guaranteed?

    Thanks.

    Friday, October 29, 2010 3:55 AM

Answers

  • Order is never guaranteed in the DataReader or DataAdapter. Everything depends on how database engine will retrieve data from the actual database and records are not always stored sequentially to improve performance or storage usage. If you need to guarantee specific order, you need to use ORDER BY clause in your SQL statement. For example, as in your case, if your table has primary key that is identity field (auto increment) that new records will always have greatest primary key value, and if you sort records based on primary key, newest records will be always at the top or bottom depending if it is ascending or descending order in your query.


    Val Mazur (MVP) http://www.xporttools.net
    Friday, October 29, 2010 10:53 AM
    Moderator

All replies

  • Order is never guaranteed in the DataReader or DataAdapter. Everything depends on how database engine will retrieve data from the actual database and records are not always stored sequentially to improve performance or storage usage. If you need to guarantee specific order, you need to use ORDER BY clause in your SQL statement. For example, as in your case, if your table has primary key that is identity field (auto increment) that new records will always have greatest primary key value, and if you sort records based on primary key, newest records will be always at the top or bottom depending if it is ascending or descending order in your query.


    Val Mazur (MVP) http://www.xporttools.net
    Friday, October 29, 2010 10:53 AM
    Moderator
  • That's what I thought.  And I originally had an ORDER BY clause in my sql statement but it was erroring out.  I thought at the time that maybe it was due to some weird shortcoming with using ORDER BY with the Jet provider (I usually work with SQL Server dbs, not Access, so I wasn't sure about Jet's behavior).  It turns out after looking at it again now that the problem was I had a typo in the field name for the ORDER BY clause.  I guess that's what I get for working on this stuff late in the evening.

    Thanks for responding.

    Friday, October 29, 2010 6:14 PM