A records placement in a table

  • Thread starter Thread starter Morten Snedker
  • Start date Start date
M

Morten Snedker

A simplified example:

A table with three columns:

ID, UserID, Text

ID is autonumbered, adding 1 for each new row.

On a button:
INSERT INTO Table (UserID, Text) VALUES (1,'Text')

The button is pressed twice.

If after these two inserts, the table is opened with

SELECT * FROM Table WHERE UserID=1 ORDER BY ID ASC

, is it anyhow possible for the last INSERT not to have been given the
highest ID and thereby not being last in the recordset (given that no
same two userID's is ever using the database at the same time)..?

The question goes both for MDB and MS-SQL.


Regards /Snedker
 
Morten said:
A simplified example:

A table with three columns:

ID, UserID, Text

ID is autonumbered, adding 1 for each new row.

On a button:
INSERT INTO Table (UserID, Text) VALUES (1,'Text')

The button is pressed twice.
Click to expand...

If the button is pressed twice, then you are going to have two records
ID UserID Text
1 1 Text
2 1 Text
If after these two inserts, the table is opened with

SELECT * FROM Table WHERE UserID=1 ORDER BY ID ASC

, is it anyhow possible for the last INSERT not to have been given the
highest ID and thereby not being last in the recordset (given that no
same two userID's is ever using the database at the same time)..?
Click to expand...

It's given the highest ID because of the nature of the autonumber, and
the index that's created on it. If you change your sort order of your
select (by adding the UserID field also as a sort criteria) you can
change the way the records are returned.

SELECT * FROM Table WHERE UserID=1 ORDER BY ID DESC, UserID

Given the comment about no two userIDs using the database at the same
time, I might guess that you are storing the active user in this table,
and then deleting their record when the database closes?

Alternately you can adjust this table to make the UserID index unique,
so that the second record couldn't be entered.

Third option is to adjust the underlying code of the command button to
check if the UserID exists in the table before executing the INSERT
INTO, so that it's only added once.
 
Back
Top