Autoincrement field problem

[ionutdinulescu]

I have an sql server table with an autonumbered field as primary key.
I insert a record into the table and then I want to get the id of the new record.
For this i use Select max(id) from myTable

This works fine, but I don't think it will work properly in a multi user environment.

Do you have any other suggestions ?

 

[chrissie1]

in the same insert statement just add a select statement

something like this


insert into tbl (field1) values ('value');select @@identity from tbl

this will return the latest identity value the table has produced.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[Dimandja]

Also, read the article in the following link. You'll be better prepared to manage the 'identity crisis'.

http://www.kamath.com/tutorials/tut007_identity.asp

 

[ionutdinulescu]

Thanks for your advices, they were very usefull.

Now I would like to use a DataSet object to do the inserts (something like myDataSet.Tables(0).NewRow).

Are there any chances that your solution will work if I do a "select @@identity" just after adding a new row using myDataSet ?

Remember that my application will be used in a multi user environment.

 

[chrissie1]

you will have to do an insert on the real table before you will get the correct autonumbering field. Your dataset will also crate an autonumber but you can't be sure that will be the real one untill you save it to the database.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[SqueakinSweep]

Bear in mind when firing inserts to SQL Tables which may have triggers on them, which subsequently may insert records to other tables.

Rather than using @@Identity, use SCOPE_IDENTITY()


Sweep
...if it works dont mess with it

 

[chrissie1]

NEVER USE TRIGGERS.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[ca8msm]

I like triggers...

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[chrissie1]

you're triggerhappy ;-)

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[ca8msm]

I'll tell you what I like about them. We generally use them for creating an audit trail. For example whenever a record is updated we have a corresponding table, which is identical to the table which is being updated apart from it has a column for a timestamp.

This way the application doesn't actually have to touch the history table but whenever any record is updated in the database we write out the old record to the history table. We find it's good for keeping an audit trail and very easy to implement and report from.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[chrissie1]

actually I do exactly the same thing only with proc's

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[ThatRickGuy]

You can use triggers for setting up locking tables and what not also.

I use a trigger on my employee directory app to set a flag on another table so that the web apps know to grab the new data.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[SqueakinSweep]

Does all this mean that we're quite happy to use Triggers now? My main use of triggers is for RI purposes, ohhh how I revel in cascading those delete commands.


Sweep
...if it works dont mess with it