Obtaining autonumber value after using recordset AddNew

Discussion in 'ASP General' started by Roger Withnell, Dec 1, 2003.

  1. I'm inserting a new record into an MS SQL database table
    and I want to obtain the new records autonumber
    immediately afterwards, as follows:
    MadminRS.CursorLocation = adUseServer
    MadminRS.CursorType = adOpenKeyset
    MadminRS.LockType = adLockOptimistic
    MadminRS.Open "NavBar", objConn, , , adCmdTable
    MadminRS.AddNew
    MadminRS("Url") = Request.Form("Website")
    MadminRS("ParentRecNo") = 0
    MadminRS("Menu") = "NavBar"
    MadminRS("Sequence") = 1000
    MadminRS("ButtonName") = "Master Admin"
    MadminRS("Link") = "Menu"
    MadminRS("Cos") = "Admin"
    MadminRS.Update
    vRecNo = MadminRS("RecNo") 'the autonumber field
    The record is inserted in the table and the autonumber
    field is created. vRecNo is blank, however. What is
    wrong with this code? I have to obtain the record number
    before closing the recordset because I may not be able to
    locate the record subsequently.
     
    Roger Withnell, Dec 1, 2003
    #1
    1. Advertising

  2. Don't use AddNew, use an INSERT statement.
    http://www.aspfaq.com/2174
    http://www.aspfaq.com/2191

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/




    "Roger Withnell" <> wrote in message
    news:02ec01c3b7ac$9ed939c0$...
    > I'm inserting a new record into an MS SQL database table
    > and I want to obtain the new records autonumber
    > immediately afterwards, as follows:
    > MadminRS.CursorLocation = adUseServer
    > MadminRS.CursorType = adOpenKeyset
    > MadminRS.LockType = adLockOptimistic
    > MadminRS.Open "NavBar", objConn, , , adCmdTable
    > MadminRS.AddNew
    > MadminRS("Url") = Request.Form("Website")
    > MadminRS("ParentRecNo") = 0
    > MadminRS("Menu") = "NavBar"
    > MadminRS("Sequence") = 1000
    > MadminRS("ButtonName") = "Master Admin"
    > MadminRS("Link") = "Menu"
    > MadminRS("Cos") = "Admin"
    > MadminRS.Update
    > vRecNo = MadminRS("RecNo") 'the autonumber field
    > The record is inserted in the table and the autonumber
    > field is created. vRecNo is blank, however. What is
    > wrong with this code? I have to obtain the record number
    > before closing the recordset because I may not be able to
    > locate the record subsequently.
    >
     
    Aaron Bertrand - MVP, Dec 1, 2003
    #2
    1. Advertising

  3. Roger Withnell

    Ken Schaefer Guest

    Why are you using these expensive cursors? Keyset is 2x -> 3x more expensive
    than other alternatives (eg a stored procedure, or even an inline SQL
    statement).

    Cheers
    Ken


    "Roger Withnell" <> wrote in message
    news:02ec01c3b7ac$9ed939c0$...
    : I'm inserting a new record into an MS SQL database table
    : and I want to obtain the new records autonumber
    : immediately afterwards, as follows:
    : MadminRS.CursorLocation = adUseServer
    : MadminRS.CursorType = adOpenKeyset
    : MadminRS.LockType = adLockOptimistic
    : MadminRS.Open "NavBar", objConn, , , adCmdTable
    : MadminRS.AddNew
    : MadminRS("Url") = Request.Form("Website")
    : MadminRS("ParentRecNo") = 0
    : MadminRS("Menu") = "NavBar"
    : MadminRS("Sequence") = 1000
    : MadminRS("ButtonName") = "Master Admin"
    : MadminRS("Link") = "Menu"
    : MadminRS("Cos") = "Admin"
    : MadminRS.Update
    : vRecNo = MadminRS("RecNo") 'the autonumber field
    : The record is inserted in the table and the autonumber
    : field is created. vRecNo is blank, however. What is
    : wrong with this code? I have to obtain the record number
    : before closing the recordset because I may not be able to
    : locate the record subsequently.
    :
     
    Ken Schaefer, Dec 1, 2003
    #3
  4. Hi

    You can obtain the autonumber value by the SQL global variable @@IDENTITY. Just use recordet when inserting data and before closing the rescordset use something like

    Set id = "select myid = @@IDENTITY from table_name", conn

    you will get it in your code. Tell me if you need the full code.

    Regards,
    Bhaskardeep Khaund
     
    Bhaskardeep Khaund, Dec 1, 2003
    #4
  5. >> Set id = "select myid = @@IDENTITY from table_name", conn

    This is not valid syntax, and you don't select @@IDENTITY from a table, it
    is a global variable. With SQL Server 2000, SCOPE_IDENTITY() is far safer
    anyway, but it has to be issued on the *same* instance as the initial
    query...

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/
     
    Aaron Bertrand - MVP, Dec 1, 2003
    #5
  6. >> And the SCOPE _IDENTITY returns the last column entered in the field
    irrespective of the session,

    Not true, I suggest you read up in Books Online, about the differences
    between @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT(). I think you have a
    couple of these mixed up.

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/
     
    Aaron Bertrand - MVP, Dec 1, 2003
    #6
  7. Hi,

    The code is:-


    <%
    strSQL = "SET NOCOUNT ON INSERT INTO tblName(TextField, NumberField)
    VALUES('ABC', 123) SELECT @@IDENTITY AS NewID SET NOCOUNT OFF"

    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open strConnection
    Set objRS = objConn.Execute(strSQL)
    varNewID = objRS("NewID")
    objConn.Close()
    Set objConn = Nothing
    Set objRS = Nothing
    %>

    And the SCOPE _IDENTITY returns the last column entered in the field irrespective of the session, if another column is inserted into the database between the column you have inserted and sending the autonumber column value, the value retrieved would be wrong.


    Regards,
    Bhaskardeep Khaund
     
    Bhaskardeep Khaund, Dec 1, 2003
    #7
  8. After inserting the record, I'm using "ident_current
    ('table')" to establish the autonumber of the record.
    Does it guarantee that the result will be the number of
    the record I have just inserted or could it be the number
    of a record inserted meanwhile? If the latter, does
    using "scope_identity()" guarantee that it is the number
    of my record? If so, how do I issue it on the "same"
    instance as the initial query?

    >-----Original Message-----
    >>> And the SCOPE _IDENTITY returns the last column

    entered in the field
    >irrespective of the session,
    >
    >Not true, I suggest you read up in Books Online, about

    the differences
    >between @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT

    (). I think you have a
    >couple of these mixed up.
    >
    >--
    >Aaron Bertrand
    >SQL Server MVP
    >http://www.aspfaq.com/
    >
    >
    >.
    >
     
    Roger Withnell, Dec 2, 2003
    #8
  9. Roger Withnell

    Bob Barrows Guest

    Roger Withnell wrote:
    > After inserting the record, I'm using "ident_current
    > ('table')" to establish the autonumber of the record.
    > Does it guarantee that the result will be the number of
    > the record I have just inserted


    No. From BOL:
    Returns the last identity value generated for a specified table in any
    session and any scope.

    So you may wind up getting the ID of a row inserted by some other process.

    >or could it be the number
    > of a record inserted meanwhile? If the latter, does
    > using "scope_identity()" guarantee that it is the number
    > of my record?


    Yes. Again from BOL:
    [SCOPE_IDENTITY] ... Returns the last IDENTITY value inserted into an
    IDENTITY column in the same scope. A scope is a module -- a stored
    procedure, trigger, function, or batch. Thus, two statements are in the same
    scope if they are in the same stored procedure, function, or batch.

    I think this is pretty clear ...


    >If so, how do I issue it on the "same"
    > instance as the initial query?
    >


    As always, I suggest using a stored procedure:

    Using Query Analyzer, run this script (adapt it to your table of course):
    CREATE PROCEDURE InsRow (
    @TextCol varchar(50),
    @IntCol int) AS
    SET NOCOUNT ON
    INSERT INTO tblName(TextCol, IntCol)
    VALUES(@TextCol,@IntCol)
    SELECT SCOPE_IDENTITY As [NewID]

    Then in ASP:

    dim rs, cn
    Set cn= Server.CreateObject("ADODB.Connection")
    cn.Open strConnection
    Set rs = Server.CreateObject("ADODB.Recordset")
    cn.InsRow "ABC",123,rs

    Of course, this is inefficient, since it is using a heavy recordset object
    to return a single value to the client. My preference is to use an output
    parameter, retrieving its value using a Command object in ASP:

    CREATE PROCEDURE InsRow (
    @TextCol varchar(50),
    @IntCol int,
    @NewID int output) AS
    SET NOCOUNT ON
    INSERT INTO tblName(TextCol, IntCol)
    VALUES(@TextCol,@IntCol)
    SET @NewID = SCOPE_IDENTITY

    dim cn,cmd,newID, params
    Set cn= Server.CreateObject("ADODB.Connection")
    cn.Open strConnection
    Set cmd= Server.CreateObject("ADODB.Command")
    With cmd
    .CommandText = "InsRow"
    .CommandType = adCmdStoredProc
    Set .ActiveConnection = cn
    Set params = .Parameters
    params.Append .CreateParameter("RETURN_VALUE", adInteger, _
    adParamReturnValue)
    params.Append .CreateParameter("@TextCol", adVarChar, _
    adParamInput,50,"ABC")
    params.Append .CreateParameter("@IntCol", adInteger, _
    adParamInput,,123)
    params.Append .CreateParameter("@NewID", adInteger, _
    adParamOutput)
    .Execute ,,adExecuteNoRecords
    End With
    newID = params(3).value

    Admittedly, it's more code, and it can be tricky to write, but it is more
    efficient than using a recordset. The trickiness can be alleviated by using
    a code generator, such as the one I wrote which is available here:
    http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear


    But, if you have some sort of phobia about using stored procedures, you can
    send a string of batched commands to SQL Server so they all get executed in
    the same scope. You can use what Bhaskardeep posted:

    strSQL = "SET NOCOUNT ON INSERT INTO tblName(TextField, NumberField)
    VALUES('ABC', 123) SELECT @@IDENTITY AS NewID SET NOCOUNT OFF"

    Just substitute "SCOPE_IDENTITY" for "@@IDENTITY"

    HTH,
    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Dec 2, 2003
    #9
  10. > After inserting the record, I'm using "ident_current
    > ('table')" to establish the autonumber of the record.
    > Does it guarantee that the result will be the number of
    > the record I have just inserted or could it be the number
    > of a record inserted meanwhile?


    IDENT_CURRENT returns the most recent IDENTITY value generated for that
    table. That might have been yours, or it might have been someone else's.

    SCOPE_IDENTITY() returns the most recent IDENTITY value generated by *you*.
    Unlike @@IDENTITY, this does not include the IDENTITY value generated by a
    trigger on the table that generated the IDENTITY value you are interested
    in.

    SCOPE_IDENTITY() is safest, @@IDENTITY should be used if you need to support
    SQL Server 7.0, and IDENT_CURRENT() should only be used if you're curious
    what the *current* value is (and don't want to use SELECT
    MAX(identityColumn), not if you're interested in finding out what you just
    did.

    > of my record? If so, how do I issue it on the "same"
    > instance as the initial query?


    sql = "SET NOCOUNT ON; " & _
    "INSERT table(col) VALUES(vals);" & _
    "SELECT SCOPE_IDENTITY()"
    set rs = conn.execute(sql)
    response.write rs(0)

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/
     
    Aaron Bertrand - MVP, Dec 2, 2003
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Nathan Sokalski

    Getting the value of the next Autonumber

    Nathan Sokalski, Jun 30, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    11,467
    William \(Bill\) Vaughn
    Jun 30, 2005
  2. COHENMARVIN
    Replies:
    10
    Views:
    1,196
    Kevin Spencer
    Aug 11, 2005
  3. Jan Hendrickx

    get value of autonumber-field

    Jan Hendrickx, Sep 20, 2005, in forum: ASP General
    Replies:
    2
    Views:
    230
    Bullschmidt
    Sep 21, 2005
  4. michal

    recordset.addNew() with postgres

    michal, Apr 13, 2007, in forum: ASP General
    Replies:
    7
    Views:
    497
    Bob Barrows [MVP]
    Apr 14, 2007
  5. c676228

    recordset.addNew

    c676228, Apr 28, 2009, in forum: ASP General
    Replies:
    6
    Views:
    693
    Vince Xu [MSFT]
    May 5, 2009
Loading...

Share This Page