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

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.
Posted
Comments
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):

CREATE PROCEDURE [dbo].[SAVE_DATA]
(
      @Param1
    , @Param2
    , @GeneratedID INT OUTPUT
 )
AS
BEGIN
     INSERT INTO TableName Values(@Param1,@Param2) --Insert statement
     SELECT @GeneratedID = SCOPE_IDENTITY(); --Populate the newly generated ID
END


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";
        try
        {
            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);
                Database.ExecuteNonQuery(dbCommand);
                //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 http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx[^]
   
Comments
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
SELECT @@IDENTITY
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