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
– Steve, Commented Feb 20, 2015 at 10:53
What is objData and what does .ModifyData() on that object do?? — marc_s
– marc_s, Commented Feb 20, 2015 at 11:00
Steve - yeah that was my idea, but i'm not hardcore to sql yet :) — Henrik S
– Henrik S, Commented Feb 20, 2015 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
– Henrik S, Commented Feb 20, 2015 at 11:04

marc_s · Accepted Answer · 2015-02-20 11:09:48Z

11

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

answered Feb 20, 2015 at 11:09

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Henrik S Over a year ago

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

marc_s Over a year ago

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

Jesús López · Accepted Answer · 2015-02-20 11:46:52Z

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, 2015 at 11:46

answered Feb 20, 2015 at 11:26

Jesús López

9,3489 gold badges47 silver badges83 bronze badges

5 Comments

Henrik S Over a year ago

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

Jesús López Over a year ago

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

Henrik S Over a year ago

Now i just get the value 0

Jesús López Over a year ago

Are you sure tblFotoalbum table has an identity column?

nuander Over a year ago

I like this answer because it works with parameters

Tushar Gupta · Accepted Answer · 2015-02-20 10:57:42Z

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, 2015 at 10:57

answered Feb 20, 2015 at 10:51

Tushar Gupta

16k1 gold badge33 silver badges53 bronze badges

4 Comments

leppie Over a year ago

Isnt this just for triggers?

Panagiotis Kanavos Over a year ago

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

Tanner Over a year ago

any chance you've copied this from: 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.

Thorsten Dittmar Over a year ago

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

Hakan Lekesiz · Accepted Answer · 2020-03-25 23:31:53Z

1

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

answered Mar 25, 2020 at 23:31

Hakan Lekesiz

1111 silver badge5 bronze badges

4 Comments

Yan Sklyarenko Over a year ago

This is rather a comment than an answer.

Hakan Lekesiz Over a year ago

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

Yan Sklyarenko Over a year ago

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.

Hakan Lekesiz Over a year ago

yes thanks. this was my first comment this platform.

Felipe Oriani · Accepted Answer · 2015-02-20 10:53:53Z

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, 2015 at 10:53

Felipe Oriani

38.7k20 gold badges141 silver badges203 bronze badges

4 Comments

Panagiotis Kanavos Over a year ago

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.

Steve Over a year ago

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

Steve Over a year ago

@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

Felipe Oriani Over a year ago

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

Thorsten Dittmar · Accepted Answer · 2015-02-20 11:17:43Z

0

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.

answered Feb 20, 2015 at 11:17

Thorsten Dittmar

56.9k10 gold badges95 silver badges146 bronze badges

Comments

Viral Sarvaiya · Accepted Answer · 2015-02-20 11:29:30Z

0

check with SCOPE_IDENTITY()..

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

answered Feb 20, 2015 at 11:29

Viral Sarvaiya

7812 gold badges10 silver badges29 bronze badges

Collectives™ on Stack Overflow

Return id after insert C# using SQL Server

7 Answers 7

2 Comments

5 Comments

4 Comments

4 Comments

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

5 Comments

4 Comments

4 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related