asked on

Returning the last inserted ID...

Hi all,

I have in the past been able to retrieve the ID of the last inserted record - but by creating a separate command to retrieve it after the insert command. What i want to do is to have one command that inserts data and returns the ID.

I have tried the following.....

I used this SQL to insert and retrieve the ID:

CREATE PROCEDURE SP_StreamJob

@DOB DateTime,
@EndDate DateTime = Null,
@CompanyID int,
@EmailAddr nvarchar(500),
@Feature nvarchar(250) = Null,
@Prog nvarchar(250) = Null,
@Channel nvarchar(150) = Null,
@TOB nvarchar(5) = Null,
@FileName nvarchar(1000),
@FileType nvarchar(10),
@CreatedBy nvarchar(150),
@theID int = null OUTPUT

AS

BEGIN
      SET NOCOUNT ON
      Insert Into dbo.TBL_StreamClips
      (dbo.TBL_StreamClips.DOB,
      dbo.TBL_StreamClips.End_Date,
      dbo.TBL_StreamClips.CPY_ID,
      dbo.TBL_StreamClips.Email_Addr,
      dbo.TBL_StreamClips.Feature_Artist,
      dbo.TBL_StreamClips.Programme,
      dbo.TBL_StreamClips.Channel,
      dbo.TBL_StreamClips.TOB,
      dbo.TBL_StreamClips.Date_Added,
      dbo.TBL_StreamClips.Created_By,
      dbo.TBL_StreamClips.FileName,
      dbo.TBL_StreamClips.FileType)
      Values
      (@DOB,
      @EndDate,
      @CompanyID,
      @EmailAddr,
      @Feature,
      @Prog,
      @Channel,
      @TOB,
      GETDATE(),
      @CreatedBy,
      @FileName,
      @FileType)

      Select @theID = SCOPE_IDENTITY()

      SET NOCOUNT OFF
END
GO

Then with my command i do the following:

Dim theID = cmdInsert.ExecuteScalar()

The problem is that it keeps returning 0 (Zero).

When I run in query analyser and print out @theID I get the correct ID.

Probably something really small i am missing, any ideas???

thanks,

KS

b1xml2

2 ways to do it
==========
cmdInsert.ExecuteNonQuery()
Dim id As Integer = DirectCast(cmd.Parameters("@theID").Value,Integer)

or
change sql from
Select @theID = SCOPE_IDENTITY()
to

SELECT SCOPE_IDENTITY()
and this would work
Dim id As Integer = DirectCast(cmd.ExecuteScalar(),Integer)

ASKER CERTIFIED SOLUTION

b1xml2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ramesh Srinivas

ASKER

Hmmm, both methods did not work.

The first method error was:
System.IndexOutOfRangeException: An SqlParameter with ParameterName '@theID' is not contained by this SqlParameterCollection.

The second method error was:
System.NullReferenceException: Object reference not set to an instance of an object.

Ramesh Srinivas

ASKER

I got it!

I added the output parameter like so:

Dim idParam As SqlParameter = cmdInsert.Parameters.Add("@theID", SqlDbType.Int)
idParam.Direction = ParameterDirection.Output

and then executed the nonquery:

cmdInsert.ExecuteNonQuery()

finally, retreived the output parameters value like so:

Dim mID As Integer = idParam.Value

Thanks for your help,

KS

b1xml2

that;s good =)