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

Unplumbed

Did you expect the property newid to be set with the value of SCOPE_IDENTITY? This is not the way in which parameters work. – Inseminate

SELECT SCOPE_IDENTITY() – Admit

What is objData and what does .ModifyData() on that object do?? – Osmium

Steve - yeah that was my idea, but i'm not hardcore to sql yet :) – Unplumbed

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(); } – Unplumbed

possible duplicate of How to get last inserted id? – Serafinaserafine

Answer 1

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

Osmium

Okay, i will try this, and how do i access the return value on my asp codebehinde? – Unplumbed

@HenrikS: the new ID value is returned from the CreateAlbum method as an int .... – Osmium

Answer 2

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

Fireboard

i'm getting an error: ExecuteNonQuery: Connection property has not been initialized – Unplumbed

Obviously you need to set the connection property of objCmd and make sure it is open before Executing it: objCmd.Connection = YouConnection; – Harmon

Now i just get the value 0 – Unplumbed

Are you sure tblFotoalbum table has an identity column? – Harmon

I like this answer because it works with parameters – Excurrent

Answer 3

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)

University

Isnt this just for triggers? – Admit

Perhaps you mean for the OP to use the OUTPUT clause? – Oresund

any chance you've copied this from: https://mcmap.net/q/134776/-how-to-get-last-inserted-id. 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. – Serafinaserafine

The OUTPUT clauses requires an INTO with a temporary (in memory) table! You should complete your otherwise valid example. – Kenley

Answer 4

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

Oscilloscope

This is rather a comment than an answer. – Aqualung

I spent 2 hours to learn this answer. maybe you understood wrongly me according to my bad english. – Oscilloscope

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

yes thanks. this was my first comment this platform. – Oscilloscope

Answer 5

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.

Morten

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

We don't know what ModifyData does, but probably this is the best way to have a duplicate record in the db. – Inseminate

@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 – Inseminate

Yes Steve, the OP should provide for us what the ModifyData does. – Morten

Answer 6

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.

Kenley

Answer 7

check with SCOPE_IDENTITY()..

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

Quigley