Finding next ID auto increment

[MidnightSun]

I'm having trouble trying to find the next ID in a mysql table which is set to autoincrement. I would like to retrieve this before the record is created so I can store its ID as a foriegn key with into another table.

I've tried a query

SELECT Max(ID) nextID FROM screenings

But I found this works until I delete the most recently created records because the Max ID is no longer the one previous of the next ID.

Any help would be appreciated

[Banfa]

Do you have to know the ID of the record before it is inserted?

Can't you

1. create the record in the first table,
2. get the ID it was created with
3. store this in the second table

rather than

1. guess at the ID you are about to create
2. store this in the second table
3. create the record in the first table,

If you do it as a transaction then you tables will never be out of sync.

[tempest]

Hi.
You could use DBCC CHECKIDENT (<table Name>, NORESEED) which will give you the current seed, add 1 to that (as long as the seed is set to inc. by one). However, this really isn't a good idea.

As previous post suggests. You should grab the identity of the new record with @@Identity and post to second table as the foreign key - don't forget to wrap in transaction so you don't lose data integrity.