Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  @@Identity Help

Author  Topic  Darren Starting Member 1 Post Posted - 2001-04-25 : 10:38:31 Hello Folks I'm a bit stumped here and not knowing what to do...... Im working with MSDE here and ADO 2.5 on PWS I've a identity column set up on my table and need to get the newly generated Identity number upon inserting a new row. THe solution for this in the MSDE SQL docs doesnt seems to work. I found a work around from a SQL site that seemed to address my problem which seems too involve the fact that the insert returns text about the number about of rows affected, which confuses the RS object when you try and call it to get the identity value: SET NOCOUNT ON INSERT INTO Trip (Member_Id, Trip_Desc, Trip_Title, ) VALUES(153523, 'Trip descrip' , 'Goin Soon' ) SELECT Active_Trip_Id = @@identity SET NOCOUNT OFF; Still gives this error: Item cannot be found in the collection corresponding to the requested name or ordinal. I read that the SET NOCOUNT ON shuts off the descritpive text..... and seems too when I run this query in the query analyzer..... This is the ADO code for the insert: Dim Trip_Id_Out Set objCommand = Server.CreateObject("ADODB.Command") objCommand.ActiveConnection = sConn_String objCommand.CommandText = sSQLTP objCommand.CommandType = adCmdText objCommand.Execute Set objRS = objCommand.Execute Set objCommand = Nothing Trip_Id_Out = objRS("Active_Trip_Id") Anyone have any ideas /experince with this?? Thanks in advance Darren