Returning identity values through a recordset

[PatHastings]

Hi,<br><br>IN SQL Server you can use the value @@identity to return the value of an identity field when you have inserted a row into that table.<br><br>Does anyone know if it is possible to pass this value through a recordset.<br><br>ie you insert a new row into the table using an ADO recordset then is there any way of getting the value of the idenity field for the row that you have just inserted other than creating and other recordset to read the row from the table?<br><br>Just curious as it seems terribly wasteful otherwise<br><br>Pat

 

[AnandMishra]

You can write SQL stored procedure for this and excute the stored procedure in place of insert statement from ASP page.<br>You are stotred procedure once and rest of thing procedure will do.<br><br>Hope it may give you a direction.<br><br>Anand

 

[palbano]

Dear Pat,<br><br>Yes, if you actually have an 'IDENTITY' column defined in the table. Not sure what version of SQL implemented this. I think 6.5. Here is what I mean:<br><br>If you have a table with a column named 'ID' and it is an IDENTITY column. Then you need to index on this column. Then in your ASP code you have a query like this:<br>SELECT * from mytable<br>and store the recordset in a variable named 'rs'<br><br>rs.AddNew();<br>rs(&quot;columnName&quot = &quot;some value&quot;;<br>rs(&quot;columnName2&quot = &quot;another value&quot;;<br>... you get the idea, then<br>rs.Update();<br><br>var nID = rs(&quot;ID&quot; // get the IDENTITY generated by SQL<br><br>Hope this helps<br>-pete