Retrieve ID on INSERT query

[stone28]

HI,

Can I create a query which will return ID for me from newly created record?

Something like:

SELECT tbl_users.user_id FROM tbl_users (INSERT INTO tbl_users tbl_users.user_fname, tbl_users.user_lname) VALUES ('bla', 'bls');

 

[jdraw]

HI,

Can I create a query which will return ID for me from newly created record?

Something like:

SELECT tbl_users.user_id FROM tbl_users (INSERT INTO tbl_users tbl_users.user_fname, tbl_users.user_lname) VALUES ('bla', 'bls');

No.

However, if ID is an autonumber, you could use

Select max(ID) FROM tbl_users

 

[stone28]

But if I have to do it in a separate query, then I can just ask for ID where fname = 'name'?

 

[georgedwilkinson]

As long as "name" is unique. Hopefully that was just an example since "name" is a reserved word in many programming languages.

 

[stone28]

Yeh, yeh... it was just to say that if it has to be separate query then it can be anything. I thought maybe it can be done in one query...

 

[jal]

If it's an autonumber field, you should be able to do it this way:

SELECT @@Identity FROM tbl_Users

 

[jal]

No.

However, if ID is an autonumber, you could use

Select max(ID) FROM tbl_users

This should work if no records are ever deleted. If records are deleted, the engine might reuse the deleted autonumbers, as far as I know.