Return id after insert C# using SQL Server

Question

I know this question has been on this site many times, but I can't get my code working.

I have an Insert statement, and I need the id from that statement on my asp.net page.

I'm getting the return value 0.

public int newid { get; set; }

public void CreateAlbum(string _titel, string _name, string _thumb, int _userid)
{
    objCMD = new SqlCommand(@"INSERT INTO tblFotoalbum 
                                  (fldAlbumHead, fldAlbumName, fldAlbumThumb, fldUserID_FK)
                              VALUES 
                                  (@titel, @name, @thumb, @userid);

                              SET @newid = SCOPE_IDENTITY()");

    objCMD.Parameters.AddWithValue("@titel", _titel);
    objCMD.Parameters.AddWithValue("@name", _name);
    objCMD.Parameters.AddWithValue("@thumb", _thumb);
    objCMD.Parameters.AddWithValue("@userid", _userid);
    objCMD.Parameters.AddWithValue("@newid", newid);

    objData.ModifyData(objCMD);       
}

Did you expect the property newid to be set with the value of SCOPE_IDENTITY? This is not the way in which parameters work. — Steve, Feb 20 '15 at 10:53
What is `objData` and what does `.ModifyData()` on that object do?? — marc_s, Feb 20 '15 at 11:00
Steve - yeah that was my idea, but i'm not hardcore to sql yet :) — Henrik S, Feb 20 '15 at 11:02
marc_s - objData is a instance of my DataAccess file, modifyData is: public void ModifyData(SqlCommand CMD) { CMD.Connection = strCon; strCon.Open(); CMD.ExecuteNonQuery(); strCon.Close(); } — Henrik S, Feb 20 '15 at 11:04
possible duplicate of [How to get last inserted id?](http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id) — Tanner, Feb 20 '15 at 11:10

score 10 · Accepted Answer · answered Feb 20 '15 at 11:09

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...

Okay, i will try this, and how do i access the return value on my asp codebehinde? — Henrik S, Feb 20 '15 at 11:28
@HenrikS: the new `ID` value is returned from the `CreateAlbum` method as an `int` .... — marc_s, Feb 20 '15 at 12:23

Jesús López · Answer 2 · 2015-02-20T11:46:52.080

3

Given the sql statement you are using, you need to configure @newid as an output parameter:

var newIdParam = objCMD.Parameters.Add("@newid", SqlDbType.Int32)
newIdParam.Direction = ParameterDirection.OutPut;

then you execute the command using ExecuteNonQuery, and after that you can read the ouptut parameter:

objCmd.ExecuteNonQuery();
int newId = Convert.ToInt32(newIdParam.Value);

EDIT: I guess ModifyData method set the connection property and calls ExecuteNonQuery, so your code would be:

objData.ModifyData(objCMD);      
int newId = Convert.ToInt32(newIdParam.Value);

edited Feb 20 '15 at 11:46

answered Feb 20 '15 at 11:26

Jesús López

8,338
7
40
66

i'm getting an error: ExecuteNonQuery: Connection property has not been initialized – Henrik S Feb 20 '15 at 11:37
Obviously you need to set the connection property of objCmd and make sure it is open before Executing it: objCmd.Connection = YouConnection; – Jesús López Feb 20 '15 at 11:39
Now i just get the value 0 – Henrik S Feb 20 '15 at 12:33
Are you sure tblFotoalbum table has an identity column? – Jesús López Feb 20 '15 at 12:36
I like this answer because it works with parameters – nuander Jan 13 '20 at 22:52

Tushar Gupta · Answer 3 · 2015-02-20T10:57:42.417

1

It might be the case as this works for SQL SERVER 2000

for 2005+ use

OUTPUT INSERTED.ID

Like

INSERT INTO Roles(UserId)
OUTPUT INSERTED.ID
VALUES(@UserId)

edited Feb 20 '15 at 10:57

answered Feb 20 '15 at 10:51

Tushar Gupta

15,504
1
29
47

Isnt this just for triggers? – leppie Feb 20 '15 at 10:53
Perhaps you mean for the OP to use the OUTPUT clause? – Panagiotis Kanavos Feb 20 '15 at 10:54
any chance you've copied this from: http://stackoverflow.com/a/5228819/57475. If so, link to that answer and say where you've taken/adapted the code from. regardless of that fact, this still doesn't really answer the question in any direct manner. – Tanner Feb 20 '15 at 11:11
The OUTPUT clauses requires an INTO with a temporary (in memory) table! You should complete your otherwise valid example. – Thorsten Dittmar Feb 20 '15 at 11:15

score 1 · Answer 4 · answered Mar 25 '20 at 23:31

1

if you are using CommandText you shouldn't forget adding "SELECT SCOPE_IDENTITY()" end of CommandText line.

answered Mar 25 '20 at 23:31

Hakan Lekesiz

111
1
5

This is rather a comment than an answer. – Yan Sklyarenko Mar 26 '20 at 08:42
I spent 2 hours to learn this answer. maybe you understood wrongly me according to my bad english. – Hakan Lekesiz Mar 26 '20 at 13:22
I apologize for the misunderstanding. My point was solely about the shape of the information, not the content. When you answer the question, you might want to give a more detailed explanation so that a person asking the question and others can benefit. In this particular case, I would expect to see some more details about why one should not forget adding `SELECT SCOPE_IDENTITY()`. Hope you get my point. – Yan Sklyarenko Mar 26 '20 at 14:59
yes thanks. this was my first comment this platform. – Hakan Lekesiz Mar 28 '20 at 22:03

score 0 · Answer 5 · answered Feb 20 '15 at 10:53

0

You don't need an output variable to do it. You could just use a SELECT SCOPE_IDENTITY() to get the last id inserted on database and use the ExecuteScalar() method from DbCommand.

public int newid { get; set; }

public void CreateAlbum(string _titel, string _name, string _thumb, int _userid)
{
    objCMD = new SqlCommand(@"INSERT INTO tblFotoalbum 
                           (fldAlbumHead, fldAlbumName, fldAlbumThumb, fldUserID_FK)
                           VALUES 
                           (@titel, @name, @thumb, @userid);

                           SELECT SCOPE_IDENTITY()");

    objCMD.Parameters.AddWithValue("@titel", _titel);
    objCMD.Parameters.AddWithValue("@name", _name);
    objCMD.Parameters.AddWithValue("@thumb", _thumb);
    objCMD.Parameters.AddWithValue("@userid", _userid);
    objCMD.Parameters.AddWithValue("@newid", newid);

    objData.ModifyData(objCMD);     


    newid = (int)objCMD.ExecuteScalar();
}

I don't know objData object does, but take a look how you execute your command.

answered Feb 20 '15 at 10:53

Felipe Oriani

37,948
19
131
194

2

Did you mean `SELECT` instead of SET? Anyway, using the `OUTPUT inserted.ID` clause is preferable. You use only a single insert statement and don't have to worry about inserting in multiple tables with identity keys in the same batch. – Panagiotis Kanavos Feb 20 '15 at 10:54
We don't know what ModifyData does, but probably this is the best way to have a duplicate record in the db. – Steve Feb 20 '15 at 10:54
2

@FelipeOriani this cannot end well. The ExecuteScalar runs everything not only the SELECT SCOPE_IDENTITY. If ModifyData inserts a record, calling again ExecuteScalar inserts another copy of the same data – Steve Feb 20 '15 at 10:59
Yes Steve, the OP should provide for us what the `ModifyData` does. – Felipe Oriani Feb 20 '15 at 11:03

score 0 · Answer 6 · answered Feb 20 '15 at 11:17

The preferred solution (and AFAIK also the only really safe one) is to use the OUTPUT clause as in this example:

DECLARE @newIdTable(newid INT);
INSERT INTO table(...) OUTPUT INSERTED.ID INTO @newIdTable VALUES (...);
SELECT TOP 1 newid FROM @newIdTable;

I suggest you put this into a stored procedure and call it using ExecuteScalar, which would return the value of the last select.

score 0 · Answer 7 · answered Feb 20 '15 at 11:29

0

check with SCOPE_IDENTITY()..

https://msdn.microsoft.com/en-IN/library/ms190315.asp

answered Feb 20 '15 at 11:29

Viral Sarvaiya

781
2
9
29

Return id after insert C# using SQL Server

7 Answers7