trapping Primary Key increment?

I

ierich

New member
Joined
May 7, 2003
Messages
3
When I insert a new record using ExecuteNonQuery method, how can I find out what the primary key was set to if the primary key is an integer which will increment by one?
 
You have to perform a second query afterwards.
Best is an ExecuteScalar and the sql-string is:

SELECT @@IDENTITY

This works on Access and SQL Server. Be aware that you only get the last Increment when you insert more than one record with your query.
 
Last edited by a moderator:
Andy, checked out your site, you need to be careful performing that operation in two calls, whats to say somebody didnt perform the operation at the same time and you have just got that ID.
 
There is no problem doing the method in my site as the IDENTITY returned is connection dependant.

Andy
 
Im not sure about Access, but with SQL Server, if you use the exact same connection string you get a shared connection on SQL Server. This might cause a problem.

Also, theres no reason to make two separate calls. You can easily perform an INSERT followed by a SELECT and save a little traffic to/from the database.

-Nerseus
 
Maybe this for SQL:

conDB = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
strInsert = "Insert... RETURN @@IDENTITY"
cmdInsert = New SqlCommand(strInsert, conDB)
conDB.Open()
cmdInsert.ExecuteNonQuery()
intNewID=cmdInsert.Parameters("ReturnValue").Value
conDB.Close()

Waddayareckon?
 
Actually, a_jam_sandwich asked me how I did the INSERT and SELECT @@IDENTITY in Access. Turns out, I didnt - never tried it in Access (dont work with it much, just assume it worked). Maybe there is a way to do the INSERT and SELECT, but I couldnt get it to work :)

Looks like two separate calls are needed. I guess thats one more reason Access isnt great on multi-user performance.

-Nerseus, /chants MSDE, MSDE, MSDE...
 
Too true but it quick and dirty and portable so at least that goes for it

Andy
 
Last edited by a moderator:
In Access you have no chance to perform this task in one command.

All I can tell is, that we performed a test few weeks ago. 11 users inserting records into the same table. We performed a ready, set, go insert :) ... and all results were correct. (we did this in Access and the SQL Server as well).
Maybe we were lucky, but I dont think so.

By the way Nerseus. Im sure the connection is session dependent and has nothing to do with the form of the connectionstring. So on the SQL Server it works perfect.
What would make me worry is Access. But see above....
 
I does tried and tested it though MSDE and it works no problem at all as the @@identity of and insert can only be read by the connection creating the record.

But in MSDE\SQL server one statment for both the insert and select is by far the best way.

Andy
 
Back
Top