How to get last inserted id

Question

I have this code   string insertSql         INSERT INTO aspnet GameProfiles UserId GameId  VALUES  UserId   GameId     using  SqlConnection myConnection   new SqlConnection myConnectionString        myConnection Open        SqlCommand myCommand   new SqlCommand insertSql  myConnection       myCommand Parameters AddWithValue   UserId   newUserId      myCommand Parameters AddWithValue   GameId   newGameId       myCommand ExecuteNonQuery        myConnection Close        When I insert into this table  I have an auto increment int primary key column called GamesProfileId  how can i get the last inserted one after this so I can use that id to insert into another table

User · Answer

I tried the above but they didn t work  i found this thought  that works a just fine for me     var ContactID   db GetLastInsertId      Its less code and i easy to put in    Hope this helps someone

User · Answer

There are all sorts of ways to get the Last Inserted ID but the easiest way I have found is by simply retrieving it from the TableAdapter in the DataSet like so    lt Your DataTable Class gt  tblData   new  lt Your DataTable Class gt      lt Your Table Adapter Class gt  tblAdpt   new  lt Your Table Adapter Class gt           Initialize and update Table Data Here            Make sure to call the EndEdit   method           of any Binding Sources before update       lt YourBindingSource gt  EndEdit       Update the Dataset tblAdpt Update tblData      Get the New ID from the Table Adapter long newID   tblAdpt Adapter InsertCommand LastInsertedId    Hope this Helps

User · Answer

INSERT INTO aspnet GameProfiles UserId GameId  VALUES  UserId   GameId    then you can just access to the last id by ordering the table in desc way    SELECT TOP 1  UserId FROM aspnet GameProfiles ORDER BY UserId DESC

User · Answer

If you re using executeScalar  cmd ExecuteScalar    result id cmd LastInsertedId ToString

User · Answer

After inserting any row you can get last inserted id by below line of query   INSERT INTO aspnet GameProfiles UserId GameId   VALUES  UserId   GameId   SELECT   IDENTITY

User · Answer

You can also use a call to SCOPE IDENTITY in SQL Server

User · Answer

I had the same need and found this answer     This creates a record in the company table  comp   it the grabs the auto ID created on the company table and drops that into a Staff table  staff  so the 2 tables can be linked  MANY staff to ONE company  It works on my SQL 2008 DB  should work on SQL 2005 and above                                CREATE PROCEDURE  dbo   InsertNewCompanyAndStaffDetails     comp name varchar 55     Big Company      comp regno nchar 8     12345678      comp email nvarchar 50     no1 home com      recID INT OUTPUT   -- The   recID  is used to hold the Company auto generated ID number that we are about to grab  AS  Begin    SET NOCOUNT ON    DECLARE  tableVar TABLE  tempID INT    -- The line above is used to create a tempory table to hold the auto generated ID number for later use  It has only one field  tempID  and its type INT is the same as the   recID      INSERT INTO comp comp name  comp regno  comp email      OUTPUT inserted comp id INTO  tableVar   -- The  OUTPUT inserted   line above is used to grab data out of any field in the record it is creating right now  This data we want is the ID autonumber  So make sure it says the correct field name for your table  mine is  comp id   This is then dropped into the tempory table we created earlier     VALUES   comp name   comp regno   comp email     SET  recID    SELECT tempID FROM  tableVar    -- The line above is used to search the tempory table we created earlier where the ID we need is saved  Since there is only one record in this tempory table  and only one field  it will only select the ID number you need and drop it into   recID     recID  now has the ID number you want and you can use it how you want like i have used it below     INSERT INTO staff Staff comp id     VALUES   recID    End   -- So there you go  You can actually grab what ever you want in the  OUTPUT inserted WhatEverFieldNameYouWant  line and create what fields you want in your tempory table and access it to use how ever you want   I was looking for something like this for ages  with this detailed break down  I hope this helps

User · Answer

For SQL Server 2005   if there is no insert trigger  then change the insert statement  all one line  split for clarity here  to this   INSERT INTO aspnet GameProfiles UserId GameId  OUTPUT INSERTED ID VALUES  UserId   GameId    For SQL Server 2000  or if there is an insert trigger   INSERT INTO aspnet GameProfiles UserId GameId   VALUES  UserId   GameId   SELECT SCOPE IDENTITY     And then   Int32 newId    Int32  myCommand ExecuteScalar

User · Answer

You can create a SqlCommand with CommandText equal to INSERT INTO aspnet GameProfiles UserId  GameId  OUTPUT INSERTED ID VALUES  UserId   GameId   and execute int id    int command ExecuteScalar  This MSDN article will give you some additional techniques

User · Answer

Use SELECT SCOPE IDENTITY   in query

User · Answer

After this   INSERT INTO aspnet GameProfiles UserId  GameId  OUTPUT INSERTED ID VALUES  UserId   GameId    Execute this  int id    int command ExecuteScalar    It will work

User · Answer

string insertSql         quot INSERT INTO aspnet GameProfiles UserId GameId  VALUES  UserId   GameId SELECT SCOPE IDENTITY   quot    int primaryKey   using  SqlConnection myConnection   new SqlConnection myConnectionString        myConnection Open        SqlCommand myCommand   new SqlCommand insertSql  myConnection       myCommand Parameters AddWithValue  quot  UserId quot   newUserId      myCommand Parameters AddWithValue  quot  GameId quot   newGameId       primaryKey   Convert ToInt32 myCommand ExecuteScalar         myConnection Close       This will work

User · Answer

In pure SQL the main statement kools like   INSERT INTO  simbs    En   OUTPUT INSERTED  ID  VALUES   en     Square brackets defines the table simbs and then the columns En and ID  round brackets defines the enumeration of columns to be initiated and then the values for the columns  in my case one column and one value  The apostrophes enclose a string  I will explain you my approach   It might be not easy to understand but i hope useful to get the big picture around using the last inserted id  Of course there are alternative easier approaches  But I have reasons to keep mine  Associated functions are not included  just their names and parameter names   I use this method for medical artificial intelligence The method check if the wanted string exist in the central table  1   If the wanted string is not in the central table  simbs   or if duplicates are allowed  the wanted string is added to the central table  simbs   2   The last inseerted id is used to create associated table  3         public List lt int   gt  CreateSymbolByName string SymbolName  bool AcceptDuplicates                if    AcceptDuplicates      check if  AcceptDuplicates  flag is set                       List lt int   gt  ExistentSymbols   GetSymbolsByName SymbolName  0  10      create a list of int arrays with existent records             if  ExistentSymbols Count  gt  0  return ExistentSymbols     1  return existent records because creation of duplicates is not allowed                   List lt int   gt  ResultedSymbols   new List lt int   gt         prepare a empty list         int   symbolPosition     0  0  0  0       prepare a neutral position for the new symbol         try    If SQL will fail  the code will continue with catch statement                         DEFAULT und NULL sind nicht als explizite Identit  tswerte zul  ssig             string commandString    INSERT INTO  simbs    En   OUTPUT INSERTED ID VALUES       SymbolName             Insert in table  simbs  on column  En  the value stored by variable  SymbolName              SqlCommand mySqlCommand   new SqlCommand commandString  SqlServerConnection      initialize the query environment                 SqlDataReader myReader   mySqlCommand ExecuteReader       last inserted ID is recieved as any resultset on the first column of the first row                 int LastInsertedId   0     this value will be changed if insertion suceede                 while  myReader Read       read from resultset                                       if  myReader GetInt32 0   gt  -1                                                 int   symbolID   new int     0  0  0  0                            LastInsertedId   myReader GetInt32 0       2  GET LAST INSERTED ID                         symbolID 0    LastInsertedId      Use of last inserted id                         if  symbolID 0     0    symbolID 1     0     if last inserted id succeded                                                       ResultedSymbols Add symbolID                                                                                     myReader Close                if  SqlTrace  SQLView Log mySqlCommand CommandText      Log the text of the command             if  LastInsertedId  gt  0     if insertion of the new row in the table was successful                               string commandString2    UPDATE  simbs  SET  IR     ID  WHERE  ID        LastInsertedId            update the table by giving to another row the value of the last inserted id                 SqlCommand mySqlCommand2   new SqlCommand commandString2  SqlServerConnection                    mySqlCommand2 ExecuteNonQuery                    symbolPosition 0    LastInsertedId     mark the position of the new inserted symbol                 ResultedSymbols Add symbolPosition      add the new record to the results collection                                 catch  SqlException retrieveSymbolIndexException     this is executed only if there were errors in the try block                       Console WriteLine  Error   0    retrieveSymbolIndexException ToString        user is informed about the error                    CreateSymbolTable LastInsertedId      3     Create new table based on the last inserted id         if  MyResultsTrace  SQLView LogResult LastInsertedId      log the action         return ResultedSymbols     return the list containing this new record

User · Answer

using System  using System Collections Generic  using System ComponentModel  using System Data  using System Drawing  using System Linq  using System Text  using System Windows Forms  using System Data SqlClient   namespace DBDemo2       public partial class Form1   Form               string connectionString    Database company Uid sa Pwd mypassword           System Data SqlClient SqlConnection connection          System Data SqlClient SqlCommand command           SqlParameter idparam   new SqlParameter   eid   SqlDbType Int  0           SqlParameter nameparam   new SqlParameter   name   SqlDbType NChar  20           SqlParameter addrparam   new SqlParameter   addr   SqlDbType NChar  10            public Form1                         InitializeComponent                 connection   new System Data SqlClient SqlConnection connectionString               connection Open                command   new System Data SqlClient SqlCommand null  connection               command CommandText    insert into employee ename  city  values  name   addr  select SCOPE IDENTITY                   command Parameters Add nameparam               command Parameters Add addrparam               command Prepare                        private void Form1 Load object sender  EventArgs e                               private void buttonSave Click object sender  EventArgs e                          try                               int id   Int32 Parse textBoxID Text                   String name   textBoxName Text                  String address   textBoxAddress Text                   command Parameters 0  Value   name                  command Parameters 1  Value   address                   SqlDataReader reader   command ExecuteReader                    if  reader HasRows                                        reader Read                        int nid   Convert ToInt32 reader 0                        MessageBox Show  ID       nid                                       int af   command ExecuteNonQuery                    MessageBox Show command Parameters  ID   Value ToString                                                  catch  NullReferenceException ne                                MessageBox Show  Error is       ne StackTrace                             catch  Exception ee                                MessageBox Show  Error is       ee StackTrace                                    private void buttonSave Leave object sender  EventArgs e                                private void Form1 Leave object sender  EventArgs e                        connection Close

User · Answer

set ANSI NULLS ON set QUOTED IDENTIFIER ON GO CREATE PROC  dbo   spCountNewLastIDAnyTableRows     PassedTableName as NVarchar 255    PassedColumnName as NVarchar 225    AS BEGIN DECLARE  ActualTableName AS NVarchar 255  DECLARE  ActualColumnName as NVarchar 225      SELECT  ActualTableName   QUOTENAME  TABLE NAME       FROM INFORMATION SCHEMA TABLES     WHERE TABLE NAME    PassedTableName     SELECT  ActualColumnName   QUOTENAME  COLUMN NAME       FROM INFORMATION SCHEMA COLUMNS     WHERE COLUMN NAME    PassedColumnName     DECLARE  sql AS NVARCHAR MAX      SELECT  sql    select MAX     ActualColumnName        1  as LASTID      FROM      ActualTableName      EXEC  SQL  END

[c#] How to get last inserted id?

Examples related to c#

Examples related to sql

Examples related to sql-server