New records are not in numerical order

[screaming_banjo]

Hi

I hope I'm posting this in the right place.

I have a SQL database and when I add a new record, while sequentially it may be ID 974, it appears before record 958 in the database. Since I want to access the last record added (which, in theory, should be 974) I thought I could use 'movelast', however it continues to pick up 958 since all new records appear to be inserting themselves before 958.

Does anyone know why this is happening and if there is anything I can do to stop it happening.

Thanks!

[buntine]

An easier solution would be to modify the SQL query.

SELECT TOP 1 * FROM TableName ORDER BY YourIDField DESC;

I think MS SQL supports the TOP clause.

Regards.

[screaming_banjo]

Hi

The SELECT TOP 1

didn't work, however it did get me in the right direction:) I did this instead:

Set rsRequestID = Server.CreateObject("adoDB.recordset")
rsRequestID.open "SELECT * FROM tblRequest ORDER BY requestID DESC", conn, adOpenKeyset, AdLockReadOnly

if not rsRequestID.eof then
rsRequestID.movefirst
end if

It is now working perfectly, thanks a lot for the help!

[screaming_banjo]

Hi

The SELECT TOP 1

didn't work, however it did get me in the right direction:) I did this instead:

Set rsRequestID = Server.CreateObject("adoDB.recordset")
rsRequestID.open "SELECT * FROM tblRequest ORDER BY requestID DESC", conn, adOpenKeyset, AdLockReadOnly

if not rsRequestID.eof then
rsRequestID.movefirst
end if

It is now working perfectly, thanks a lot for the help!

I don't suppose you know why it starts putting new records in 'out of sync'? I had been deleting a few because of testing and wondered if that had anything to do with it?

[buntine]

Not sure. I do not think the database will bother ordering records on the backend automatically. It only needs to ensure the ID's are unique. The ordering can be done programmatically.

Regards.

[russell]

They aren't "out of synch". Nothing gurantees the order the data will be returned without an order by clause in your query. MS SQL will store the records any way it sees fit (and usually in the way that it can return them the fastest, or store them the most efficiently). It is important to note that MSSQL tables are not files, and there is no reason to suppose that the first in will be the first out. The ONLY way to order a recordset is with the order by clause.