Help getting Unique ID when using rs.Addnew

Hello, I have the following problem with MS SQL and ASP:
When adding a new code with the code below I need to get the automatically generated ID. I know there are threads discussing this but I have found none which discuss it when using the rs.AddNew method. I don't think "SELECT @@IDENTITY" works for me. Here's the code:

Set rs = Server.CreateObject("ADODB.Recordset")

rs.CursorType = 2
rs.LockType = 3

StrSQL = "SELECT * FROM table1"

rs.Open strSQL, objConn
rs.Addnew

rs("Field1") = "Value1"
rs("Field2") = "Value2"

rs.Update

rs.Movelast
RowID = rs("ID")

rs.Close

I though the rs.Movelast will work and it did most of the time, but I found that it does not work ALL of the time. What happens is that sometimes a record may be added before the last record in the table and "RowID" would actually contain that records ID. So I need your help. Thanks

************************************************************ **********
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

Re: Help getting Unique ID when using rs.Addnew

Use an INSERT statement, then SELECT SCOPE_IDENTITY() should work fine.
Better yet, use a stored procedure to handle the insert and the SELECT.

--
http://www.aspfaq.com/
(Reverse address to reply.)




"Kiril Zlatkov" wrote in message
news:#d3WFxz9EHA.3592@TK2MSFTNGP09.phx.gbl...
> Hello, I have the following problem with MS SQL and ASP:
> When adding a new code with the code below I need to get the automatically
generated ID. I know there are threads discussing this but I have found none
which discuss it when using the rs.AddNew method. I don't think "SELECT
@@IDENTITY" works for me. Here's the code:
>
> Set rs = Server.CreateObject("ADODB.Recordset")
>
> rs.CursorType = 2
> rs.LockType = 3
>
> StrSQL = "SELECT * FROM table1"
>
> rs.Open strSQL, objConn
> rs.Addnew
>
> rs("Field1") = "Value1"
> rs("Field2") = "Value2"
>
> rs.Update
>
> rs.Movelast
> RowID = rs("ID")
>
> rs.Close
>
> I though the rs.Movelast will work and it did most of the time, but I
found that it does not work ALL of the time. What happens is that sometimes
a record may be added before the last record in the table and "RowID" would
actually contain that records ID. So I need your help. Thanks
>
> ************************************************************ **********
> Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

Re: Help getting Unique ID when using rs.Addnew

Aaron [SQL Server MVP] wrote:
> Use an INSERT statement, then SELECT SCOPE_IDENTITY() should work fine.
> Better yet, use a stored procedure to handle the insert and the SELECT.
>
or use select max(ID+1) and use that as the record id

Re: Help getting Unique ID when using rs.Addnew

....and what about all the transactions never committed or deleted since the
last insert?

"Aaron [SQL Server MVP]" wrote in message
news:%23TzGrz19EHA.2984@TK2MSFTNGP09.phx.gbl...
> > or use select max(ID+1) and use that as the record id
>
> Not a very good idea. What happens if someone else inserts a row between
> the time you select and the time you insert???
>
>

Re: Help getting Unique ID when using rs.Addnew

JohnDpatriot wrote:
> Aaron [SQL Server MVP] wrote:
>> Use an INSERT statement, then SELECT SCOPE_IDENTITY() should work
>> fine. Better yet, use a stored procedure to handle the insert and
>> the SELECT.
>>
> or use select max(ID+1) and use that as the record id

Only if you have no concurrent users ...

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Help getting Unique ID when using rs.Addnew

> or use select max(ID+1) and use that as the record id

Not a very good idea. What happens if someone else inserts a row between
the time you select and the time you insert???

Re: Help getting Unique ID when using rs.Addnew

Right, IDENTITY values are not guaranteed to be contiguous.
http://www.aspfaq.com/2523
http://www.aspfaq.com/2174

(The latter really should explain why NOT to use this MAX(ID)+1 kludge...)



"raydan" wrote in message
news:ODL6P219EHA.936@TK2MSFTNGP12.phx.gbl...
> ...and what about all the transactions never committed or deleted since
the
> last insert?