ierich Posted May 7, 2003 Posted May 7, 2003 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? Quote
APaule Posted May 7, 2003 Posted May 7, 2003 (edited) 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 Quote
a_jam_sandwich Posted May 7, 2003 Posted May 7, 2003 there is an example on my site Andy Quote Code today gone tomorrow!
dsgreen57 Posted May 7, 2003 Posted May 7, 2003 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. Quote
a_jam_sandwich Posted May 7, 2003 Posted May 7, 2003 There is no problem doing the method in my site as the IDENTITY returned is connection dependant. Andy Quote Code today gone tomorrow!
*Experts* Nerseus Posted May 7, 2003 *Experts* Posted May 7, 2003 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 Quote "I want to stand as close to the edge as I can without going over. Out on the edge you see all the kinds of things you can't see from the center." - Kurt Vonnegut
ierich Posted May 7, 2003 Author Posted May 7, 2003 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? Quote
*Experts* Nerseus Posted May 8, 2003 *Experts* Posted May 8, 2003 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... Quote "I want to stand as close to the edge as I can without going over. Out on the edge you see all the kinds of things you can't see from the center." - Kurt Vonnegut
a_jam_sandwich Posted May 8, 2003 Posted May 8, 2003 (edited) 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 Quote Code today gone tomorrow!
APaule Posted May 8, 2003 Posted May 8, 2003 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.... Quote
a_jam_sandwich Posted May 8, 2003 Posted May 8, 2003 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 Quote Code today gone tomorrow!
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.