Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor
  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. Database & SysAdmin
  3. Database
  4. Row ID after INSERT to database

Row ID after INSERT to database

Scheduled Pinned Locked Moved Database
questiondatabasejsontutorial
17 Posts 5 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • J Jeff Martin

    You should really use SCOPE_IDENTITY() to be safe. @@IDENTITY returns the last Identity value no matter what the scope. For example, You have 2 tables, table1 and table2. table1 has a trigger that when a record is inserted, it inserts a record in table2. INSERT INTO table1 VALUES ... --the record you inserted into table1 --created the identity of i.e. 1001 --and it fired the insert trigger --which inserted identity value of --i.e. 2002 into table2. DECLARE @myID int SELECT @myID = @@IDENTITY @myID will be equal to 2002 (the value from table2) because @@IDENTITY ignores scope if you use... SELECT @myID = SCOPE_IDENTITY() @myID would be equal to 1001 because the trigger is outside the scope of this query/stored proc/whatever. Jeff Martin My Blog

    _ Offline
    _ Offline
    _J_
    wrote on last edited by
    #5

    u absolutly right. my answer was not accurate .... From russia with love ....

    1 Reply Last reply
    0
    • J Jeff Martin

      You should really use SCOPE_IDENTITY() to be safe. @@IDENTITY returns the last Identity value no matter what the scope. For example, You have 2 tables, table1 and table2. table1 has a trigger that when a record is inserted, it inserts a record in table2. INSERT INTO table1 VALUES ... --the record you inserted into table1 --created the identity of i.e. 1001 --and it fired the insert trigger --which inserted identity value of --i.e. 2002 into table2. DECLARE @myID int SELECT @myID = @@IDENTITY @myID will be equal to 2002 (the value from table2) because @@IDENTITY ignores scope if you use... SELECT @myID = SCOPE_IDENTITY() @myID would be equal to 1001 because the trigger is outside the scope of this query/stored proc/whatever. Jeff Martin My Blog

      A Offline
      A Offline
      Abyss
      wrote on last edited by
      #6

      Hi, Many thanks for help. I'm a C++ programer and the suggested methods for some reason fail. I try to get the inserted row's ID (primary key) which was inserted to a table. I tried the following API calls immediately after insert. I use the same m_hStmn for the insert and the select query. This call is successfull however when I query the returned value using the SQLFetch and SQLGetData the returned SQLINTEGER is always zero!? SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT @@IDENTITY AS 'Ident'"), SQL_NTS) I tried to use the more save SCOPE_IDENTITY() function, however these API calls fail. The driver returns (for Access table) undefined function. SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY()"), SQL_NTS) SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY() As [Ident]"), SQL_NTS) In this case I got from the driver one parameter missing error code: SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY"), SQL_NTS) Can anybody explain how can I correctly get the inserted row's ID? Why the above calls fail :((? Thanks, Abyss

      H J 2 Replies Last reply
      0
      • A Abyss

        Hi, Many thanks for help. I'm a C++ programer and the suggested methods for some reason fail. I try to get the inserted row's ID (primary key) which was inserted to a table. I tried the following API calls immediately after insert. I use the same m_hStmn for the insert and the select query. This call is successfull however when I query the returned value using the SQLFetch and SQLGetData the returned SQLINTEGER is always zero!? SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT @@IDENTITY AS 'Ident'"), SQL_NTS) I tried to use the more save SCOPE_IDENTITY() function, however these API calls fail. The driver returns (for Access table) undefined function. SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY()"), SQL_NTS) SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY() As [Ident]"), SQL_NTS) In this case I got from the driver one parameter missing error code: SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY"), SQL_NTS) Can anybody explain how can I correctly get the inserted row's ID? Why the above calls fail :((? Thanks, Abyss

        H Offline
        H Offline
        Huseyin Altindag
        wrote on last edited by
        #7

        Hi I think I've got something it can help you on www.codeproject.com/cs/database/DBManipulationADONET.asp Huseyin Altindag

        A 1 Reply Last reply
        0
        • H Huseyin Altindag

          Hi I think I've got something it can help you on www.codeproject.com/cs/database/DBManipulationADONET.asp Huseyin Altindag

          A Offline
          A Offline
          Abyss
          wrote on last edited by
          #8

          Well, it is a great idea to use "SELECT MAX(ID) from TableXXX". I suppose that if some record is removed from the table then the given gap is not filled by the new records - the ID is not reused. Always new ID is generated which is greater than the rest. Right? Thanks, Abyss

          H J 2 Replies Last reply
          0
          • A Abyss

            Well, it is a great idea to use "SELECT MAX(ID) from TableXXX". I suppose that if some record is removed from the table then the given gap is not filled by the new records - the ID is not reused. Always new ID is generated which is greater than the rest. Right? Thanks, Abyss

            H Offline
            H Offline
            Huseyin Altindag
            wrote on last edited by
            #9

            Hi Yes but remember always the new ID generated is greater than the last one you insert. I'll send you(maybe today) a complete application(C#) with SQLServer solution how to do it Huseyin

            A 1 Reply Last reply
            0
            • H Huseyin Altindag

              Hi Yes but remember always the new ID generated is greater than the last one you insert. I'll send you(maybe today) a complete application(C#) with SQLServer solution how to do it Huseyin

              A Offline
              A Offline
              Abyss
              wrote on last edited by
              #10

              Thanks in advance. Have you any idea why the original solution fail with "SELECT SCOPE_IDENTITY()" and "SELECT @@IDENTITY AS 'Ident'"? It is on the MSDN - how to obtain the ID of the inserted record. Is it an Access related problem? Anyway your solution is great I think that it will work in all cases. Abyss

              M 1 Reply Last reply
              0
              • A Abyss

                Thanks in advance. Have you any idea why the original solution fail with "SELECT SCOPE_IDENTITY()" and "SELECT @@IDENTITY AS 'Ident'"? It is on the MSDN - how to obtain the ID of the inserted record. Is it an Access related problem? Anyway your solution is great I think that it will work in all cases. Abyss

                M Offline
                M Offline
                Mike Dimmick
                wrote on last edited by
                #11

                Those features are part of Microsoft SQL Server, not Access/Jet. Stability. What an interesting concept. -- Chris Maunder

                1 Reply Last reply
                0
                • A Abyss

                  Hi, Many thanks for help. I'm a C++ programer and the suggested methods for some reason fail. I try to get the inserted row's ID (primary key) which was inserted to a table. I tried the following API calls immediately after insert. I use the same m_hStmn for the insert and the select query. This call is successfull however when I query the returned value using the SQLFetch and SQLGetData the returned SQLINTEGER is always zero!? SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT @@IDENTITY AS 'Ident'"), SQL_NTS) I tried to use the more save SCOPE_IDENTITY() function, however these API calls fail. The driver returns (for Access table) undefined function. SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY()"), SQL_NTS) SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY() As [Ident]"), SQL_NTS) In this case I got from the driver one parameter missing error code: SQLExecDirect(m_hStmn, (SQLTCHAR*)_T("SELECT SCOPE_IDENTITY"), SQL_NTS) Can anybody explain how can I correctly get the inserted row's ID? Why the above calls fail :((? Thanks, Abyss

                  J Offline
                  J Offline
                  Jeff Martin
                  wrote on last edited by
                  #12

                  If you are calling this in a separate query from the insert, I know SCOPE_IDENTITY() won't work. I'm not sure about @@IDENTITY. I write my stored procs that do the insert where they get the scope_identity() and return it. On the code side, I just get the return from teh stored proc. Jeff Martin My Blog

                  A 1 Reply Last reply
                  0
                  • A Abyss

                    Well, it is a great idea to use "SELECT MAX(ID) from TableXXX". I suppose that if some record is removed from the table then the given gap is not filled by the new records - the ID is not reused. Always new ID is generated which is greater than the rest. Right? Thanks, Abyss

                    J Offline
                    J Offline
                    Jeff Martin
                    wrote on last edited by
                    #13

                    No, it is not a great idea. This only works if it is a single user database AND if the ID has not been reset AND SQL Server decides not to fill in gaps. Do not use select max(ID)... Jeff Martin My Blog

                    A 1 Reply Last reply
                    0
                    • J Jeff Martin

                      No, it is not a great idea. This only works if it is a single user database AND if the ID has not been reset AND SQL Server decides not to fill in gaps. Do not use select max(ID)... Jeff Martin My Blog

                      A Offline
                      A Offline
                      Abyss
                      wrote on last edited by
                      #14

                      I'm using an Access (MDB) database - via Access/Jet ODBC driver. I suppose that it is a single user database - maybe it is possible to access the database table from different applications at the same time... Will such database fill in gaps (reuse IDs) and when resets the server the ID? Mark told (see the thread) that "SELECT SCOPE_IDENTITY()" are part of SQL server not Access/Jet. Any other idea? I really appreciate your help :) Thanks, Abyss

                      J 1 Reply Last reply
                      0
                      • J Jeff Martin

                        If you are calling this in a separate query from the insert, I know SCOPE_IDENTITY() won't work. I'm not sure about @@IDENTITY. I write my stored procs that do the insert where they get the scope_identity() and return it. On the code side, I just get the return from teh stored proc. Jeff Martin My Blog

                        A Offline
                        A Offline
                        Abyss
                        wrote on last edited by
                        #15

                        Uhh, it is out of my knowledge (I'm rather a novice). How does such stored procedure looks like? Something like this? INSERT INTO TableXXX (A, B) VALUES (3, 5); SELECT SCOPE_IDENTITY(); in one call. Is it possible at all? Thanks, Abyss

                        J 1 Reply Last reply
                        0
                        • A Abyss

                          I'm using an Access (MDB) database - via Access/Jet ODBC driver. I suppose that it is a single user database - maybe it is possible to access the database table from different applications at the same time... Will such database fill in gaps (reuse IDs) and when resets the server the ID? Mark told (see the thread) that "SELECT SCOPE_IDENTITY()" are part of SQL server not Access/Jet. Any other idea? I really appreciate your help :) Thanks, Abyss

                          J Offline
                          J Offline
                          Jeff Martin
                          wrote on last edited by
                          #16

                          Sorry, no. I don't use Access. Jeff Martin My Blog

                          1 Reply Last reply
                          0
                          • A Abyss

                            Uhh, it is out of my knowledge (I'm rather a novice). How does such stored procedure looks like? Something like this? INSERT INTO TableXXX (A, B) VALUES (3, 5); SELECT SCOPE_IDENTITY(); in one call. Is it possible at all? Thanks, Abyss

                            J Offline
                            J Offline
                            Jeff Martin
                            wrote on last edited by
                            #17

                            Basically, yes. It is basically a predefined collection of SQL statements stored on the database. But with Access, you can't use them. Jeff Martin My Blog

                            1 Reply Last reply
                            0
                            Reply
                            • Reply as topic
                            Log in to reply
                            • Oldest to Newest
                            • Newest to Oldest
                            • Most Votes

                            • Login
                            • Don't have an account? Register
                            • Login or register to search.
                            • First post
                              Last post
                            0
                            • Categories
                            • Recent
                            • Tags
                            • Popular
                            • World
                            • Users
                            • Groups