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

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

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