Click here to Skip to main content
14,421,411 members
Rate this:
Please Sign up or sign in to vote.
See more:

I'm struggling to return the ID of the record I just inserted into a SQL database, using ASP.NET (C#) with Code Behind to handle the inserts and SqlDataSources as the connection

(SqlDataSource1.InsertParameter["xxxxxxxx"].DefaultValue = "xxxxxxx";) etc.

Ideally, I would like to insert the record, and then return the ID of that inserted record to a variable so I can use it on the next webpage (or current page if possible) or set as a querystring.

I have limited knowledge of how all this works in .NET (use to be simple in classic ASP lol).

I've tried to execute the SELECT = SCOPE_ID() after the Insert but not sure if this works, or how to return the value if it does work to a variable.

At the moment I'm having to use SELECT(MAX) on the next page to get the last record ID, but I know this is not a safe method, once multiple users start entering records.

Please be gentle with your replies as I might not understand them fully to start with.

Thanks in advance

codemagpie 3-Sep-10 6:45am
Do I need to use a stored procedure? as I currently don't have any of these setup in the SQL Database (and not sure how I would do it anyway).
Rate this:
Please Sign up or sign in to vote.

Solution 1

1. Add an output parameter to your stored procedure and set it using the SCOPE_IDENTITY() as follows (Assuming that you are using Identity field):

    , @Param2
    , @GeneratedID INT OUTPUT
     INSERT INTO TableName Values(@Param1,@Param2) --Insert statement
     SELECT @GeneratedID = SCOPE_IDENTITY(); --Populate the newly generated ID

2. Retrieve the newly generated ID in the Save method as follows:

public int Save(int Param1,int Param2)
        int GeneratedId = 0;
        string SP = "SAVE_DATA";
            using (DbCommand dbCommand = Database.GetStoredProcCommand(SP))
                Database.AddInParameter(dbCommand, "@Param1", DbType.Int32, Param1);
                Database.AddInParameter(dbCommand, "@Param1", DbType.Int32, Param2);
                //The Output Parameter
                Database.AddOutParameter(dbCommand, "@GeneratedId", DbType.Int64, 64);
                //Read the Output Parameter Value afte execution
                GeneratedId = Convert.ToInt64(Database.GetParameterValue(dbCommand, "@GeneratedId"));
        catch (Exception ex)
            //Handle exception here
        return GeneratedId;

Please note that, I am using the Enterprise Library application block for database access. You can do it in ADO.NET also. See[^]
Simon_Whale 3-Sep-10 5:31am
Reason for my vote of 5
nice clean answer - and that also how I've done it in the past too
Rate this:
Please Sign up or sign in to vote.

Solution 2

try only
scope_identity returns value in only within current session

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100