Execute Insert command and return inserted Id in Sql

Question

I am inserting some values into a SQL table using C  in MVC 4  Actually  I want to insert values and return the  ID  of last inserted record  I use the following code  public class MemberBasicData       public int Id   get  set        public string Mem NA   get  set        public string Mem Occ   get  set            ID is automatically incremented in the database when inserted  public int CreateNewMember string Mem NA  string Mem Occ         using  SqlConnection con new SqlConnection Config ConnectionString                 using SqlCommand cmd new SqlCommand  quot INSERT INTO Mem Basic Mem Na Mem Occ  VALUES  na  occ  quot  con                         cmd Parameters AddWithValue  quot  na quot   Mem NA               cmd Parameters AddWithValue  quot  occ quot   Mem Occ               con Open                 int modified   cmd ExecuteNonQuery                 if  con State    System Data ConnectionState Open  con Close                    return modified                         I know ExecuteNonQuery denotes the numbers affecting the row  Instead of that I use int modified    int cmd ExecuteScalar     But it s not working  Please help me for solving this  And is there any code like cmd ExecuteInsertAndGetID    not working with my code

User · Answer

USE AdventureWorks2012  GO IF OBJECT ID N t6   N U   IS NOT NULL      DROP TABLE t6  GO IF OBJECT ID N t7   N U   IS NOT NULL      DROP TABLE t7  GO CREATE TABLE t6 id int IDENTITY   CREATE TABLE t7 id int IDENTITY 100 1    GO CREATE TRIGGER t6ins ON t6 FOR INSERT  AS BEGIN    INSERT t7 DEFAULT VALUES END  GO --End of trigger definition  SELECT id FROM t6  --IDs empty   SELECT id FROM t7  --ID is empty   --Do the following in Session 1 INSERT t6 DEFAULT VALUES  SELECT   IDENTITY    Returns the value 100  This was inserted by the trigger     SELECT SCOPE IDENTITY       Returns the value 1  This was inserted by the  INSERT statement two statements before this query     SELECT IDENT CURRENT  t7       Returns value inserted into t7  that is in the trigger     SELECT IDENT CURRENT  t6       Returns value inserted into t6  This was the INSERT statement four statements before this query     -- Do the following in Session 2  SELECT   IDENTITY     Returns NULL because there has been no INSERT action  up to this point in this session     SELECT SCOPE IDENTITY       Returns NULL because there has been no INSERT action  up to this point in this scope in this session     SELECT IDENT CURRENT  t7       Returns the last value inserted into t7

User · Answer

The following solution will work with sql server 2005 and above  You can use output to get the required field  inplace of id you can  write your key that you want to return  do it like this  FOR SQL SERVER 2005 and above      using SqlCommand cmd new SqlCommand  INSERT INTO Mem Basic Mem Na Mem Occ  output INSERTED ID VALUES  na  occ   con                 cmd Parameters AddWithValue   na   Mem NA           cmd Parameters AddWithValue   occ   Mem Occ           con Open             int modified   int cmd ExecuteScalar             if  con State    System Data ConnectionState Open               con Close             return modified            FOR previous versions      using SqlCommand cmd new SqlCommand  INSERT INTO Mem Basic Mem Na Mem Occ   VALUES  na  occ  SELECT SCOPE IDENTITY     con                 cmd Parameters AddWithValue   na   Mem NA           cmd Parameters AddWithValue   occ   Mem Occ           con Open             int modified   Convert ToInt32 cmd ExecuteScalar              if  con State    System Data ConnectionState Open  con Close                return modified

User · Answer

Change the query to   INSERT INTO Mem Basic Mem Na Mem Occ  VALUES  na  occ   SELECT SCOPE IDENTITY      This will return the last inserted ID which you can then get with ExecuteScalar

User · Answer

SQL Server stored procedure   CREATE PROCEDURE  dbo   INS MEM BASIC       na varchar 50        occ varchar 50        New MEM BASIC ID int OUTPUT AS BEGIN     SET NOCOUNT ON       INSERT INTO Mem Basic     VALUES   na   occ       SELECT  New MEM BASIC ID   SCOPE IDENTITY   END   C  code   public int CreateNewMember string Mem NA  string Mem Occ            values 0 -- gt  -99 are SQL reserved      int new MEM BASIC ID   -1971         SqlConnection SQLconn   new SqlConnection Config ConnectionString       SqlCommand cmd   new SqlCommand  INS MEM BASIC   SQLconn        cmd CommandType   CommandType StoredProcedure       SqlParameter outPutVal   new SqlParameter   New MEM BASIC ID   SqlDbType Int        outPutVal Direction   ParameterDirection Output      cmd Parameters Add outPutVal       cmd Parameters Add   na   SqlDbType Int  Value   Mem NA      cmd Parameters Add   occ   SqlDbType Int  Value   Mem Occ       SQLconn Open        cmd ExecuteNonQuery        SQLconn Close         if  outPutVal Value    DBNull Value  new MEM BASIC ID   Convert ToInt32 outPutVal Value           return new MEM BASIC ID      I hope these will help to you       You can also use this if you want      public int CreateNewMember string Mem NA  string Mem Occ         using  SqlConnection con new SqlConnection Config ConnectionString                 int newID          var cmd    INSERT INTO Mem Basic Mem Na Mem Occ  VALUES  na  occ  SELECT CAST scope identity   AS int             using SqlCommand cmd new SqlCommand cmd  con                         cmd Parameters AddWithValue   na   Mem NA               cmd Parameters AddWithValue   occ   Mem Occ                con Open                newID    int insertCommand ExecuteScalar                 if  con State    System Data ConnectionState Open  con Close                    return newID

User · Answer

using SqlCommand cmd new SqlCommand  INSERT INTO Mem Basic Mem Na Mem Occ           VALUES  na  occ  SELECT SCOPE IDENTITY     con         cmd Parameters AddWithValue   na   Mem NA       cmd Parameters AddWithValue   occ   Mem Occ       con Open         int modified   cmd ExecuteNonQuery         if  con State    System Data ConnectionState Open  con Close            return modified      SCOPE IDENTITY   Returns the last identity value inserted into an identity column in the same scope  for more details http   technet microsoft com en-us library ms190315 aspx

[c#] Execute Insert command and return inserted Id in Sql

Examples related to c#

Examples related to sql

Examples related to sql-server

Examples related to asp.net-mvc-4