DBNavigator doesn't return all records
lazarus / 16-Databases

----------------------------------------------------------------------
LXC · 2016-09-11 10:10

Hi
I have a DBNavigator on a form linked to a DataSource which allows me
to scroll through, update, insert and delete records from my
underlying MS Access db tables. SQL query does a "select * from
users".

I noticed, however, that it is not showing all records. I have around
300 records in the db table. Primary key is set to autonumber.
First field in the table is last_name.  I expect to see the first
record in my table displayed first but it isn't. My first table entry
starts with an 'A' but I only start seeing records from 'L' onward.  I
can go right to the end and see the last record in my table correctly,
but it seems to be missing the first few.

Any advice gratefully received.

----------------------------------------------------------------------
goodname · 2016-09-11 14:11

If you want a guaranteed order then you must use the order by clause.
Without it the database will return the records in any order.

select * from users order by last_name;

----------------------------------------------------------------------
LXC · 2016-09-12 08:49

thanks very much.
I had actually tried that - my mistake was, when I typed that code
into the tStringList "editing SQL" for that SQLquery I clicked on the
"run SQLcode" icon that's available there. I got an error that the
ODBC driver doesn't support the function, hence reverting back to the
original code (without the "order by").
It turns out that it executes just fine as you suggested without any
errors - not sure why I got (and still get) the error when I run it
just in that window.

Anyhow, problem solved, thank you!