?
Solved

Returning the last inserted ID...

Posted on 2005-05-12
5
Medium Priority
?
286 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:Ramesh Srinivas
  • 3
  • 2
5 Comments
 
LVL 23

Expert Comment

by:b1xml2
ID: 13984975
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)


0
 
LVL 23

Accepted Solution

by:
b1xml2 earned 1000 total points
ID: 13984988
The reason why you cannot get the new Id is because you are populating the parameter with the value
Select @theID = SCOPE_IDENTITY()

is the same as
SET @theID = SCOPE_IDENTITY()

You could of course still retain the
Select @theID = SCOPE_IDENTITY()

and then

do this:
SELECT @theID

and either using the ExecuteScalar() or using the ExecuteNonQuery and then getting the output parameter's value would work.

0
 
LVL 11

Author Comment

by:Ramesh Srinivas
ID: 13985187
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.

0
 
LVL 11

Author Comment

by:Ramesh Srinivas
ID: 13985305
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
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13985347
that;s good =)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question