trapping Primary Key increment?

[ierich]

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?

[APaule]

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.

Edited May 7, 2003 by APaule

[a_jam_sandwich]

there is an example on my site

 

Andy

[dsgreen57]

Andy, checked out your site, you need to be careful performing that operation in two calls, whats to say somebody didn't perform the operation at the same time and you have just got that ID.

[a_jam_sandwich]

There is no problem doing the method in my site as the IDENTITY returned is connection dependant.

 

Andy

[Nerseus]

I'm 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, there's 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

[ierich]

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?

[Nerseus]

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

 

Looks like two separate calls are needed. I guess that's one more reason Access isn't great on multi-user performance.

 

-Nerseus, /chants MSDE, MSDE, MSDE...

[a_jam_sandwich]

Too true but it quick and dirty and portable so at least that goes for it

 

Andy

Edited May 8, 2003 by a_jam_sandwich

[APaule]

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 don't think so.

 

By the way Nerseus. I'm 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....

[a_jam_sandwich]

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