www.webdeveloper.com
Results 1 to 3 of 3

Thread: ExecuteNonQuery how to get the Identifer

  1. #1
    Join Date
    Nov 2003
    Location
    California
    Posts
    248

    ExecuteNonQuery how to get the Identifer

    Hi all im using asp.net and i use the ExecuteNonQuery code to insert into table. Is there a way to get the id back from sql server. I dont want to do a select after it doesnt make sense. And if the data has duplicates it wouldnt get the right id. HELP ME PLEASE

  2. #2
    Join Date
    Nov 2003
    Posts
    655
    You can do a SELECT to get the row id: "SELECT max(id) FROM wherever", BUT you must do it in a transaction to ensure that no other INSERT is performed on that table between your INSERT and SELECT. My recommendation would be to do both in a stored procedure.

    Example:
    Code:
    CREATE PROCEDURE INSERT_Q
    @Q varchar(20),
    @id int OUTPUT
    AS
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION
     INSERT INTO BLAH(Q) VALUES (@Q)
     SET @id = (SELECT MAX(id) FROM BLAH)
    COMMIT
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    GO
    EDIT: After a bit more inspection, for SQL Server 7 (works for 2000 as well), @@IDENTITY returns the last-inserted identity value (look it up in SQL Server Books Online for full details).

    So you could do it like this to save a SELECT:
    Code:
    CREATE PROCEDURE INSERT_Q
    @Q varchar(20),
    @id int OUTPUT
    AS
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION
     INSERT INTO BLAH(Q) VALUES (@Q)
     SET @id = @@IDENTITY
    COMMIT
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    GO
    Last edited by CardboardHammer; 05-24-2004 at 10:11 AM.

  3. #3
    Join Date
    Nov 2003
    Location
    California
    Posts
    248
    Hey I found a better way to do it. But i dont have the code right now. easier solution so i dont have to do it trough a SP.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles