Execute Insert Command and Return Inserted Id in Sql

Execute Insert command and return inserted Id in Sql

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

Execute Insert command and return inserted Id in SQL Server using C#

1. You are not casting, you are aliasing column name to int,

   Use: SELECT CAST(scope_identity() AS int)

2. rowCount is boxed integer. change return statement and return type. Example:

return (int)rowCount;

This would be your required ID.

Ref: SqlCommand.ExecuteScalar MSDN

Return id after insert C# using SQL Server

Try this:

public int CreateAlbum(string _titel, string _name, string _thumb, int _userid)
{
    // define return value - newly inserted ID
    int returnValue = -1;

    // define query to be executed
    string query = @"INSERT INTO tblFotoalbum (fldAlbumHead, fldAlbumName, fldAlbumThumb, fldUserID_FK)
                              VALUES (@titel, @name, @thumb, @userid);
                     SELECT SCOPE_IDENTITY();"

    // set up SqlCommand in a using block   
    using (objCMD = new SqlCommand(query, connection)) 
    {
        // add parameters using regular ".Add()" method 
        objCMD.Parameters.Add("@titel", SqlDbType.VarChar, 50).Value = _titel;
        objCMD.Parameters.Add("@name", SqlDbType.VarChar, 100).Value = _name;
        objCMD.Parameters.Add("@thumb", SqlDbType.VarChar, 100).Value = _thumb;
        objCMD.Parameters.Add("@userid", SqlDbType.VarChar, 25).Value = _userid;

        // open connection, execute query, close connection
        connection.Open();
        object returnObj = objCMD.ExecuteScalar();

        if(returnObj != null)
        {
           int.TryParse(returnObj.ToString(), out returnValue);
        }

        connection.Close();
    }

    // return newly inserted ID
    return returnValue;
}

Not sure how you can integrate that with your objData class - maybe you need to add a new method to that DAL class for this.

Check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results...

Return ID on INSERT?

You just need to add @ID to the params collection and then retrieve it like this,

cmd.Parameters.Add("@ID", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
//Now just read the value of: cmd.Parameters["@ID"].value

Or, if you prefer this syntax:

SqlParameter param = new SqlParameter("@ID", SqlDbType.Int, 4);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);

Get an identity value in a controller for INSERT (Execute Insert command and return inserted Id in Sql)

Actually, DBUtil code consists of:

public static int ExecSQL(string sql, params object[] list)
{
    List<String> escParams = new List<String>();

    foreach (object o in list)
    {
        if (o == null)
            escParams.Add("");
        else
            escParams.Add(EscQuote(o.ToString()));
    }

    DB_SQL = String.Format(sql, escParams.ToArray());

    int rowsAffected = 0;

    using (SqlConnection dbConn = new SqlConnection(DB_CONNECTION))
    using (SqlCommand dbCmd = dbConn.CreateCommand())
    {
         try
         {
              dbConn.Open();
              dbCmd.CommandText = DB_SQL;
              rowsAffected = dbCmd.ExecuteNonQuery();
         }
         catch (System.Exception ex)
         {
              DB_Message = ex.Message;
              rowsAffected = -1;
         }
     }

     return rowsAffected;
}

And as you know ExecuteNonQuery denotes the numbers affecting the row

So, You can do as shown below:

FOR SQL SERVER 2005 and above

   using (SqlConnection con=new SqlConnection(@"Your connectionString"))
    {
        using(SqlCommand cmd=new SqlCommand("INSERT INTO Payment(Payment_Method,Currency_Type,Total_Amount) output INSERTED.Payment_id VALUES(@Payment_Method,@Currency_Type,@Total_Amount)",con))
        {
            cmd.Parameters.AddWithValue("@Payment_Method", "Cash");
            cmd.Parameters.AddWithValue("@Currency_Type", currency);
            cmd.Parameters.AddWithValue("@Total_Amount", total);
            con.Open();
        
            int payment_id =Convert.ToInt32(cmd.ExecuteScalar());
        
            if (con.State == System.Data.ConnectionState.Open) 
                con.Close();
        
            return payment_id ;
        }
}

also, you can change your query to:

"INSERT INTO Payment(Payment_Method,Currency_Type,Total_Amount) VALUES(@Payment_Method,@Currency_Type,@Total_Amount)"; SELECT SCOPE_IDENTITY()" 

Return inserted row

You could insert the row and then use SCOPE_IDENTITY() to get the ID of the row you inserted and return the row with that id from the table.

For example:

INSERT INTO tableA VALUES (a1, a2);

DECLARE @Id INT = SCOPE_IDENTITY();

SELECT * FROM tableA WHERE ID = @Id

SQL Server - Return value after INSERT

No need for a separate SELECT...

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');

This works for non-IDENTITY columns (such as GUIDs) too

Can SCOPE_IDENTITY return value after another user's Insert

Don't use SCOPE_IDENTITY or any other single value mechanisms.

Use OUTPUT:

DECLARE @ids TABLE (id int);

INSERT INTO table (ross)
    OUTPUT inserted.id INTO @ids  -- use correct column name
    VALUES ('whatever');

SELECT *
FROM ids;

Note:

• This works for an arbitrary number of rows being inserted.
• This guarantees that you only get the values back for the rows that were actually inserted by the statement.
• This works even if triggers are defined on the table.
• You can drop the INTO and just get the results returned from the INSERT.

---

Related Topics