Execute Insert command and return inserted Id in Sql

by

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;
    }
}

More Related Contents:

  1. SQL Server Char/VarChar DateTime Error [closed]
  2. Delete from two tables? [closed]
  3. How to get last inserted id?
  4. What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]
  5. Adding multiple parameterized variables to a database in c#
  6. Insert entire DataTable into database at once instead of row by row?
  7. Getting return value from stored procedure in C#
  8. SQL error: Incorrect syntax near the keyword ‘User’
  9. What represents a double in sql server?
  10. Passing List to SQL Stored Procedure
  11. Query extremely slow in code but fast in SSMS
  12. How to retrieve data from a SQL Server database in C#?
  13. Code to validate SQL Scripts
  14. Any way to SQLBulkCopy “insert or update if exists”?
  15. SQLException : String or binary data would be truncated
  16. Multi threading C# application with SQL Server database calls
  17. What’s the fastest way to bulk insert a lot of data in SQL Server (C# client)
  18. ALTER TABLE DROP COLUMN failed because one or more objects access this column
  19. When executing a stored procedure, what is the benefit of using CommandType.StoredProcedure versus using CommandType.Text?
  20. Can I use ADFS 2.0 to authenticate certain users against SQL Server?
  21. ExecuteNonQuery returning -1 when using sql COUNT despite the query string
  22. What is passing parameters to SQL and why do I need it?
  23. Add WHERE clauses to SQL dynamically / programmatically
  24. How to pass string array in SQL parameter to IN clause in SQL
  25. How to pass a null variable to a SQL Stored Procedure from C#.net code
  26. How to insert data into SQL Server
  27. Query validation using C#
  28. Database Error: There is no row at position 0
  29. Generating sql code programmatically
  30. Capturing count from an SQL query

Leave a Comment